Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  View with virtual column to select count(*) will return NULL value

    Posted 06/10/26 04:54 AM
    The problem SQL is below:
    create table t1 (id int, name char(10));
    create table t2 (id int, name char(10));
     
    insert into t1 values('1','t1_01');
    insert into t2 values('1','t2_01');
     
    create view v1 (id, name, tn) as
    select id, name, 't1' as tn from t1
    union all
    select id, name, 't2' as tn from t2;
     
    select nvl(count(*),'NULL') from v1 where tn='t1';
    select nvl(count(*),'NULL') from v1 where tn='t2';

    The result:

    (expression)
     
    1.0000000000000000
     
     
    (expression)
     
    NULL



    ------------------------------
    Philip Yeh
    Cobrasonic Inc.
    ------------------------------


  • 2.  RE: View with virtual column to select count(*) will return NULL value

    Posted 06/10/26 06:05 AM

    Philip:

    What version of Informix are you using and in what platform?

    I have replicated the issue on v15.0.1.8. Also note:

    > select tn, count(*) from v1 group by tn;


    tn       (count(*)) 

    t1                1
    t2                1

    2 row(s) retrieved.

    that I can retrieve both values and:

    > select * from v1 where tn= 't2';


             id name       tn 

              1 t2_01      t2

    1 row(s) retrieved.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 3.  RE: View with virtual column to select count(*) will return NULL value

    Posted 06/10/26 09:37 PM

    Dear Art,

    I am run the Informix 14.10.FC12W12 on Linux:

    on14f12:/home/ids14fc12$ cat test_view.sql
    create database mydb with log;
     
    create table t1 (id int, name char(10));
    create table t2 (id int, name char(10));
     
    insert into t1 values('1','t1_01');
    insert into t2 values('1','t2_01');
     
    create view v1 (id, name, tn) as
    select id, name, 't1' from t1
    union all
    select id, name, 't2' from t2;
     
    select nvl(count(*),'NULL') from v1 where tn='t1';
    select nvl(count(*),'NULL') from v1 where tn='t2';

    on14f12:/home/ids14fc12$ onstat -
     
    IBM Informix Dynamic Server Version 14.10.FC12W12 -- On-Line -- Up 16 days 23:14:14 -- 366376 Kbytes
    2026-06-11 09:31:46
     
    on14f12:/home/ids14fc12$ dbaccess -e - test_view.sql
    create database mydb with log;
    Database created.
     
     
     
    create table t1 (id int, name char(10));
    Table created.
     
     
    create table t2 (id int, name char(10));
    Table created.
     
     
     
    insert into t1 values('1','t1_01');
    1 row(s) inserted.
     
     
    insert into t2 values('1','t2_01');
    1 row(s) inserted.
     
     
     
    create view v1 (id, name, tn) as
    select id, name, 't1' from t1
    union all
    select id, name, 't2' from t2;
    View created.
     
     
     
    select nvl(count(*),'NULL') from v1 where tn='t1';
     
    (expression)
     
    1.0000000000000000
     
    1 row(s) retrieved.
     
     
    select nvl(count(*),'NULL') from v1 where tn='t2';
     
    (expression)
     
    NULL
     
    1 row(s) retrieved.
     
     
     
    Database closed.
    Than I run the test_view.sql on the Informix 15.0.1.7, also return NULL:

    [informix@rhel95x64:/home/ids1501]$ onstat -
     
    IBM Informix Dynamic Server Version 15.0.1.7 -- On-Line -- Up 00:04:41 -- 365652 Kbytes
    2026-06-11 09:32:50 -- Infrastructure Version: 1
     
    [informix@rhel95x64:/home/ids1501]$ dbaccess -e - test_view.sql
    create database mydb with log;
    Database created.
     
     
     
    create table t1 (id int, name char(10));
    Table created.
     
     
    create table t2 (id int, name char(10));
    Table created.
     
     
     
    insert into t1 values('1','t1_01');
    1 row(s) inserted.
     
     
    insert into t2 values('1','t2_01');
    1 row(s) inserted.
     
     
     
    create view v1 (id, name, tn) as
    select id, name, 't1' from t1
    union all
    select id, name, 't2' from t2;
    View created.
     
     
     
    select nvl(count(*),'NULL') from v1 where tn='t1';
     
    (expression)
     
    1.0000000000000000
     
    1 row(s) retrieved.
     
     
    select nvl(count(*),'NULL') from v1 where tn='t2';
     
    (expression)
     
    NULL
     
    1 row(s) retrieved.
     
     
     
    Database closed.


    ------------------------------
    Philip Yeh
    Cobrasonic Inc.
    ------------------------------

    Original Message


  • 4.  RE: View with virtual column to select count(*) will return NULL value

    Posted 06/11/26 06:19 AM

    Philip:

    This is clearly a long standing bug! If you are licensed, you should open a PMR. I can back channel a report, but a customer report carries more weight.

    Art



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


Related Content