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'
select count(*) from paf_trans where badge='X' and termination_date is null
select count(*) from paf_trans where badge = 'X' and termination_date is not null
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.
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.
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.