NULLS as part of composite key
For records where NULL is the first value of a composite key, the
selects fail.
I am running Informix SE 5.02.UC6 on a UNIVEL UnixWare 4.2 rel 1.1.2
system.
Given that a table "system1" contains a composite key made up of
two columns named "bus_id", and "sys_label", both are char columns, and
make up a unique key for the table. "bus_id" allows NULLs.
The index is created thus:
create unique index systm1 on system1(bus_id,sys_label);
When bus_id is NULL, the select statement:
select * from table where bus_id IS NULL and sys_label = "VALUE";
returns 0 rows.
The row identified by bus_id NULL and sys_label = "VALUE" is retrievable
with either
select * from system1 where bus_id is NULL;or
select * from system1 where sys_label = "VALUE";
but not the combined condition. Whenever the optimizer uses the index
to do the select, the select fails. Changing the select to something
like:
select * from system1 where bus_id is null and sys_label like "%VALUE";
works because the optimizer decides not to use the combined index.
Dropping all indexes from this table makes the select work.
The table contains about 150 rows, 20 or so of which have NULL as the
bus_id. Attempts to reproduce the problem with smaller tables doesn't
seem to exhibit the same problem.
All arguments about allowing NULLS as part of a key aside, (I didn't
design this database), is there a documented bug in Informix SE 5.x
that explains this?
I searched the FAQ, and the news archives, and there is a mention in
the 1994 news archives about a similar bug with the SCO port of 5.x.
Our Informix contact person is looking up information regarding our
maintenance agreement with Informix and hence whether we can access
the Informix Links Bug database.
If anyone has information about this situation and why it does this,
or how you worked around it, please email me and/or reply to this post.
-Tom Rudnick
----------------/----------------------------------------------
Tom Rudnick | USDA Natural Resources Conservation Service
Fort Collins,CO | tom@avatar.ftc.nrcs.usda.gov (970) 282-1941
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-