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?

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

Carsten Haese wrote:
> 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?

IDS 7.31:

select sec_desc2 from sec_desc2;
SQL -217: Column (sec_desc2) not found in any table in the query (or SLV is
undefined).
SQLSTATE: IX000 at /dev/stdin:1

IDS 10.00:

select sec_desc2 from sec_desc2;

-- no output.

Art S. Kagel

> 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 wrote:
> 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?

IDS 7.31:

select sec_desc2 from sec_desc2;
SQL -217: Column (sec_desc2) not found in any table in the query (or SLV is
undefined).
SQLSTATE: IX000 at /dev/stdin:1

IDS 10.00:

select sec_desc2 from sec_desc2;

-- no output.

Art S. Kagel

> 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,
>