View with virtual column to select count(*) will return NULL value
Philip Yeh found that selecting count(*) with NVL from a UNION ALL view with a constant virtual column returns NULL for one filter value on 14.10.FC12W12 and 15.0.1.7. Art Kagel reproduced it on 15.0.1.8, called it a long-standing bug and advised opening a PMR. Unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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.
------------------------------
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
------------------------------
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.sqlcreate 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.sqlcreate 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.
------------------------------
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 ------------------------------