[threshar]

I've got a VERY interesting problem here. I've been working on fragmenting our "main" table and so I setup a frag strategy and loaded it up with a few million rows of data... I created my indexes, updated statistics and whatnot and now a select max(number) on this table returns null - (not no rows found, but null)

System info:

4 cpu sun e450, 4G mem, solaris 2.6
Informix Dynamic Server Version 7.30.UC3 -- On-Line -- Up 00:24:06 -- 529376 Kbytes

(Yes I know it is a crappy version)

So I have a table with a schema like
create table posts (
board_name varchar(12),
number integer,
prev integer,
next integer,
replies integer,
reply_to integer,
member_handle varchar(35),
timestamp datetime year to minute,
body text )
fragment by expression
timestamp <= datetime(1999-12-31) year to day in dbs1,
timestamp > datetime(1999-12-31) year to day and
timestamp <= datetime(2000-12-31) year to day in posts_2000,
timestamp > datetime(2000-12-31) year to day and
timestamp <= datetime(2001-12-31) year to day in posts_2001,
timestamp > datetime(2001-12-31) year to day and
timestamp <= datetime(2002-12-31) year to day in posts_2002,
timestamp > datetime(2002-12-31) year to day and
timestamp <= datetime(2003-12-31) year to day in posts_2003
extent size 2000000 next size 500000

and an index

create index posts2_idx on posts(board_name, number, reply_to)

ok. now what is odd I loaded up a few million records into it and discovered something interesting -

select number from posts where board_name = 'BLAH'
and number = 999;

number

999

select number from posts where board_name = 'BLAH'
and number = 1000;

No rows found.


select max(number) from posts where board_name = 'BLAH'

(max)

1 row(s) retrieved.


select count(*) from posts where board_name = 'BLAH'
and number is null

(count(*))

0

select count(*) from posts where board_name = 'BLAH'

(count(*))

999

there was nothing in the logs.
So after a while of banging my head I dropped the index and it worked fine. Then I recreated the index, but I made it not fragmented (added in foodbs) and it worked fine.

So anybody know anything about this, or is it yet another bug in the version of informix I run? (I do have a very reliable way to make the engine crash too

[Roelwe]

I would test it with a version that is not so old. Lots of bugs were corrected since that version.
Version 7 always acted very weird with NULL values.

It follows simple rules, set by Berkeley University:

NULL is not equal to NULL
NULL is not equal to 0

so if you do a select max() and there is a NULL value it is possible that that NULL value is the max.

same with your where clause.

'where number is NULL' will work in version 9, but is nor reliable in version 7.