Returning to null...
In a recent article I talked about the way to return a NULL from a query. That remembered me how "picky" Informix is with NULL. Did this sound like me saying Informix does something wrong?! That would be strange in an Informix dedicated blog...
Well... I said "picky" because that's the feeling I get from people who face the way Informix deals with NULL. Usually those persons have a background in other RDBMS which may times behave differently. Let me show some situations common in Informix:
- The query below returns NULL. Most of the times people expect it to return
"SomeString"
SELECT
"SomeString"||NULL
FROM
systables
WHERE
tabid = 1; - The query below return no rows. People would expect it to return some rows
SELECT
"SomeString"
FROM
systables
WHERE
"A" != NULL::CHAR - The query below also returns NULL. People usually expect it to return 10
SELECT
10 + NULL::INTEGER
FROM
systables
WHERE
tabid = 1
Also note that in Informix NULL cannot be represented by "" for example. "" is an empty string. But at least it has a defined value. NULL hasn't.
Many developers argue that the way Informix deals with NULL does not make sense and causes incoveniences. I argue that this makes a lot of sense (mathematically) and I concede that it can be inconvenient sometimes from a practical perspective. But in reality, what those developers or I think is pretty irrelevant. What is relevant is that it's the way the SQL standard defines NULL and Informix follows it.
In order to overcome the inconveniences we can use the NVL() function.
No comments:
Post a Comment