On 2018/07/05 8:44 AM, Simon Slavin wrote:
On 5 Jul 2018, at 7:30am, Clemens Ladisch <clem...@ladisch.de> wrote:
The expression "x = x" will fail for NULL, but succeed for everything
else. So you can use that to implement a "not-NULL ELSE"
Wow. That has to be the most counter-intuitive feature of SQLite. I
understand why it works, but I still don't like it. Thanks for posting it.
That's how it works everywhere, not just in SQLite. NULL has special
handling in that any expression or function that gets touched by a NULL
value immediately returns NULL (except for some aggregates that
sometimes have NULL values among their input populations, which they
What the OP essentially wants is to test for NULL values, which is
possible using "IS" but not in an equality test (since the expression [
a = x ] or [ a <> x ] both return NULL if either a is NULL or x is NULL,
as they should), so it cannot use equality testing in the usual way a
My typical way to do this is:
WHEN x IS NULL THEN ...
WHEN x < 1 THEN ...
WHEN x < 3 THEN ...
WHEN x < 5 THEN ...
But I feel like the equality check option can easily be enhanced in
SQLite to have this work:
WHEN IS NULL THEN ....
WHEN 1 THEN ...
WHEN 3 THEN ...
WHEN 5 THEN ...
but then it's so little difference from the example above it that I have
never yearned for it - in fact, I never use this latter version due to
its shortcomings in testing anything that is not an equality check (but
since my preference is no measure of its utility, perhaps it's worth
PS: Here is a version of the 1st example working:
WITH C(x) AS (
SELECT IFNULL(x + 1, 1) FROM C WHERE x < 10 OR x IS NULL
SELECT x, CASE
WHEN x IS NULL THEN 'None'
WHEN x < 1 THEN 'Zero'
WHEN x < 3 THEN 'Small'
WHEN x < 6 THEN 'Medium'
END AS size
-- x | size
-- ---- | ------
-- NULL | None
-- 1 | Small
-- 2 | Small
-- 3 | Medium
-- 4 | Medium
-- 5 | Medium
-- 6 | Large
-- 7 | Large
-- 8 | Large
-- 9 | Large
-- 10 | Large
sqlite-users mailing list