Is this an Informix bug?
I had a typo in an SQL query, and I stumbled upon an interesting
feature/bug. Try this code:
create table tab(a int);
create table tab1(a int);
insert into tab values(1);
insert into tab values(null);
select count(*) from tab where tab is not null;
select count(*) from tab where tab is null;
select count(*) from tab where tab1 is null;
The answers are:
2
0
SQL Error -217 (column not found)
which behaves as if Informix evaluates tab is null to false, tab is
not null to true and a different table name is considered an error. Is
there a documented (or even undocumented) meaning for use of the table
name in a WHERE clause using IS NULL and IS NOT NULL or is this a bug?
Syntax of a where clause in this case is COLUMN IS NULL (or operator
<value>)
Table is not allowed in the position where you typed it. I would be
interested to see what database does support this.
j.
-----Original Message-----
From: informix-list-bounces@iiug.org
[mailto:informix-list-bounces@iiug.org]On Behalf Of Zachi
Sent: Tuesday, May 08, 2007 2:27 PM
To: informix-list@iiug.org
Subject: Is this an Informix bug?
I had a typo in an SQL query, and I stumbled upon an interesting
feature/bug. Try this code:
create table tab(a int);
create table tab1(a int);
insert into tab values(1);
insert into tab values(null);
select count(*) from tab where tab is not null;
select count(*) from tab where tab is null;
select count(*) from tab where tab1 is null;
The answers are:
2
0
SQL Error -217 (column not found)
which behaves as if Informix evaluates tab is null to false, tab is
not null to true and a different table name is considered an error. Is
there a documented (or even undocumented) meaning for use of the table
name in a WHERE clause using IS NULL and IS NOT NULL or is this a bug?
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
On Tue, 2007-05-08 at 11:26 -0700, Zachi wrote:
> I had a typo in an SQL query, and I stumbled upon an interesting
> feature/bug. Try this code:
>
> create table tab(a int);
> create table tab1(a int);
> insert into tab values(1);
> insert into tab values(null);
> select count(*) from tab where tab is not null;
> select count(*) from tab where tab is null;
> select count(*) from tab where tab1 is null;>
> The answers are:
> 2
> 0
> SQL Error -217 (column not found)
>
> which behaves as if Informix evaluates tab is null to false, tab is
> not null to true and a different table name is considered an error. Is
> there a documented (or even undocumented) meaning for use of the table
> name in a WHERE clause using IS NULL and IS NOT NULL or is this a bug?
It's a feature. IDS accepts the name of a table as an expression. This
expression constructs ROW objects on the fly, containing all columns of
the table. Try "select tab from tab" to see what that does. What that's
good for, well, your guess is probably just as good as mine.
HTH,
--
Carsten Haese
http://informixdb.sourceforge.net