Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2013
    Posts
    2

    Angry Unanswered: false negative on is null in where clause?

    I updated a column value to be null, but cannot select the row when comparing the recently updated column to null. If I compare the same column to not is null, the row is still not returned. Please see the example.

    update paf_trans set termination_date = null where badge = 'X'

    select count(*) from paf_trans where badge='X'

    Returns 1

    select count(*) from paf_trans where badge='X' and termination_date is null

    Returns nothing

    select count(*) from paf_trans where badge = 'X' and termination_date is not null

    Returns nothing

    How is that even possible given that there is 1 row with badge = 'X'?

    If I put any other value in termination_date I will get a result of 1 on the last query.

    Has anybody had this issue before?

    I believe my version of informix to be 7.2

  2. #2
    Join Date
    Sep 2011
    Location
    Pont l'Abbé, Brittany, France
    Posts
    381
    Provided Answers: 1
    Hi,

    handling the null value has been a long subject.
    First of all, which is a stupid but mandatory check: is your update statement successfully completed? Check this simply by issuing select * from paf_trans where badge = 'X'.

    If yes, I unfortunately do not have the release notes of IDS 7.2, which is by the way a really old version. It might be a problem, or, if my memory speaks well, kind of expected behaviour. I remember that the null value caused some problems.

    Do you run this test from dbacess or another client ?
    Can you also check the exact version of the engine by running onstat - , logged as informix ?

    I just tested your scenario on 12.1 and it works perfectly, as expected.

  3. #3
    Join Date
    Jul 2013
    Posts
    2
    Thanks begooden-it,

    The update appears to have completed as I get the 1 row updated response and the query "select * from paf_trans where badge='X'" returns 1 row. I believe our Informix hasn't been upgraded in many years perhaps going back to the 1990s. I run the test while running the VeraTerm terminal emulator. It is emulating a VT 100 and is using telnet to connect directly to the informix server running AIX Version 5.

Tags for this Thread

Posting Permissions

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