Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2003
    Posts
    23

    Unanswered: max(integer) returning null

    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

  2. #2
    Join Date
    Aug 2002
    Location
    Belgium
    Posts
    534
    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.
    rws

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •