CASE and NULL

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

CASE and NULL

Andy Goth-3
I'd like to use CASE to compare an expression x against a number of
candidate values.  That's the typical use for "CASE x WHEN", which
avoids repeating x for each condition.

The trouble is that one of the possible values is NULL, yet the
comparison against each candidate value is done with the = operator.
The expression "x = NULL" is meaningless since it will always evaluate
to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will
never accomplish anything.

The workaround is to not use "CASE x WHEN" and instead use "CASE WHEN"
and repeat x every time, using = for all non-NULL values and IS for
NULL.  But this means repeating x for each condition, which is the whole
reason I'd prefer "CASE x WHEN".

A compromise is to do both, as follows:

CASE x
WHEN 1 THEN 11
WHEN 2 THEN 22
WHEN 3 THEN 33
WHEN 4 THEN 44
ELSE CASE
WHEN x IS NULL THEN 55
ELSE 66
END END

Or the other way around, so that both instances of x are near each other:

CASE WHEN x IS NULL THEN 55
ELSE CASE x
WHEN 1 THEN 11
WHEN 2 THEN 22
WHEN 3 THEN 33
WHEN 4 THEN 44
ELSE 66
END END

If the CASE statement enumerates all possible values of x (whose range
is perhaps guaranteed with a CHECK or FOREIGN KEY constraint), then the
ELSE case can stand in for the NULL comparison.  But otherwise, the ELSE
case will unavoidably collect not only NULL but any other unhandled values.

So I'm wondering: can we do better?  I wouldn't want to risk changing
the meaning of any existing queries, but it is generally possible to
extend from the error space: take something that's currently a syntax
error and give it meaning.  How about the following?

CASE x
WHEN 1 THEN 11
WHEN 2 THEN 22
WHEN 3 THEN 33
WHEN 4 THEN 44
WHEN IS NULL THEN 55
ELSE 66
END

Or equivalently, "ISNULL" instead of "IS NULL".

This treatment could also be applied to numerous other operators that
take an expression as their left-hand side and produce a truth result:

?NOT? LIKE|GLOB|REGEXP|MATCH expr ?ESCAPE expr?
NOTNULL
NOT NULL
< <= > >= != <>
= == (for completeness, even though it's implied)
?NOT? BETWEEN expr AND expr
?NOT? IN list-generation-expression

Giving us syntax such as:

CREATE TABLE fruits (name); [... veggies ... meats ...]
CASE food
WHEN IN fruits THEN 'fruit'
WHEN IN veggies THEN 'veggie'
WHEN IN meats THEN 'meat'
WHEN ISNULL THEN 'unspecified'
ELSE 'candy' END

CASE filename
WHEN GLOB '.*' THEN 'hidden'
WHEN GLOB '*.png' THEN 'image'
WHEN GLOB '*.html' THEN 'webpage'
WHEN REGEXP '\.docx?$' THEN 'MS-Word'
ELSE 'data' END

CASE age
WHEN < 1 THEN 'baby'
WHEN < 3 THEN 'toddler'
WHEN < 5 THEN 'preschooler'
WHEN < 12 THEN 'gradeschooler'
WHEN < 18 THEN 'teenager'
WHEN < 21 THEN 'young adult'
ELSE 'adult' END

CASE hour
WHEN BETWEEN  6   AND  6.5 THEN 'wake'
WHEN BETWEEN  7   AND  7.5 THEN 'breakfast'
WHEN BETWEEN  8   AND  8.5 THEN 'commute'
WHEN BETWEEN 11.5 AND 12.5 THEN 'lunch'
WHEN BETWEEN  9   AND 17   THEN 'work'
WHEN BETWEEN 17.5 AND 18   THEN 'commute'
WHEN BETWEEN 19   AND 19.5 THEN 'dinner'
WHEN BETWEEN 22   AND 24   THEN 'sleep'
WHEN BETWEEN  0   AND  6   THEN 'sleep'
END

To make the above examples more compelling, replace food, filename, age,
and hour with complex expressions such as nested queries.

The next evolution in bloat is to also support AND, OR, NOT, and
parentheses, allowing the LHS operand of any operator in a complex
expression to be omitted, defaulting to CASE's first argument.  In the
last example above this would allow the two "sleep" cases (or the
"commute" cases) to be combined with OR: "WHEN BETWEEN 22 AND 24 OR
BETWEEN 0 and 6 THEN 'sleep'".  But I imagine this would complicate the
parser far beyond any practical benefit.

--
Andy Goth | <andrew.m.goth/at/gmail/dot/com>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

Simon Slavin-3
On 5 Jul 2018, at 6:22am, Andy Goth <[hidden email]> wrote:

> Or equivalently, "ISNULL" instead of "IS NULL".

There is no ISNULL in sqlite3.  Also, the functions ifnull() and nullif() are not useful for actually testing for NULL.  Continuing to eliminate options, regular expressions do not match with NULL in a useful manner.

As your examples show, you are reduced to variations on

    CASE WHEN x IS NULL THEN ... ELSE ... END

> CASE age
> WHEN < 1 THEN 'baby'
> WHEN < 3 THEN 'toddler'
> WHEN < 5 THEN 'preschooler'
> WHEN < 12 THEN 'gradeschooler'
> WHEN < 18 THEN 'teenager'
> WHEN < 21 THEN 'young adult'
> ELSE 'adult' END

You can replace this with a table lookup.  Create a table like the following

CREATE TABLE AgeNames (age INTEGER UNIQUE, name TEXT COLLATE NOCASE);
INSERT INTO AgeNames (1, 'baby'),(3,'toddler'),(5 ...

then do a SELECT looking for

    SELECT name FROM AgeNames WHERE ?1 >= age ORDER BY rowid LIMIT 1

You will have to find a value for "age" which deals with the case where you're looking up NULL.  It might work just to set "age" to NULL.

Similar things can be done with the other CASE statements that match on numbers.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

Clemens Ladisch
In reply to this post by Andy Goth-3
Andy Goth wrote:
> The expression "x = NULL" is meaningless since it will always evaluate
> to NULL, which CASE interprets as untruth, hence "WHEN NULL THEN" will
> never accomplish anything.
> [...]
> So I'm wondering: can we do better?

The expression "x = x" will fail for NULL, but succeed for everything
else.  So you can use that to implement a "not-NULL ELSE":

CASE x
WHEN 1 THEN ...
WHEN x THEN 'not NULL'
ELSE        'NULL'
END


> [...]
> The next evolution in bloat is to also support AND, OR, NOT, and
> parentheses, allowing the LHS operand of any operator in a complex
> expression to be omitted

So you want to have your beloved COBOL features in SQL?  ;-)
http://www.3kranger.com/HP3000/mpeix/doc3k/B3150090013.11820/65.htm
http://www.csis.ul.ie/cobol/course/Selection.htm


Regards,
Clemens
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

Simon Slavin-3
On 5 Jul 2018, at 7:30am, Clemens Ladisch <[hidden email]> 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.

Hmm.  Yes, "x != x" works too.  I forgot to check "x IS NOT x".

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

Donald Shepherd
On Thu, 5 Jul 2018 at 16:45, Simon Slavin <[hidden email]> wrote:

> On 5 Jul 2018, at 7:30am, Clemens Ladisch <[hidden email]> 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.
>
> Hmm.  Yes, "x != x" works too.  I forgot to check "x IS NOT x".
>

I've always thought of that as an SQL thing rather than an SQLite thing,
because SQL Server and PostgreSQL and MySQL all do the same (or at least so
I'm lead to believe in the last two cases).
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

Keith Medcalf

On Thursday, 5 July, 2018 00:57, Donald Shepherd <[hidden email]>:
>On Thu, 5 Jul 2018 at 16:45, Simon Slavin <[hidden email]> >wrote:
>> On 5 Jul 2018, at 7:30am, Clemens Ladisch <[hidden email]> >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.

>> Hmm.  Yes, "x != x" works too.  I forgot to check "x IS NOT x".

> I've always thought of that as an SQL thing rather than an SQLite
> thing, because SQL Server and PostgreSQL and MySQL all do the same
> (or at least so I'm lead to believe in the last two cases).

It is a mathematics thing.  The NULL value works like that everywhere.  Unless the coder/programmer made a boo-boo.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.




_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

R Smith-2
In reply to this post by Simon Slavin-3
On 2018/07/05 8:44 AM, Simon Slavin wrote:
> On 5 Jul 2018, at 7:30am, Clemens Ladisch <[hidden email]> 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
simply ignore).

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
CASE executes.

My typical way to do this is:

CASE
     WHEN x IS NULL THEN ...
     WHEN x < 1 THEN ...
     WHEN x < 3 THEN ...
     WHEN x < 5 THEN ...
     ELSE ...
END;

But I feel like the equality check option can easily be enhanced in
SQLite to have this work:

CASE x
     WHEN IS NULL THEN ....
     WHEN  1 THEN ...
     WHEN  3 THEN ...
     WHEN  5 THEN ...
END;

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
considering).


Cheers,
Ryan

PS: Here is a version of the 1st example working:

WITH C(x) AS (
      SELECT NULL
      UNION ALL
      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'
     ELSE 'Large'
     END AS size
   FROM C
;


   -- 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: CASE and NULL

Peter Johnson
Can't you just use IFNULL to assign a default value?

CASE IFNULL( x, -999 )
  WHEN 1 THEN 11
  WHEN 2 THEN 22
  WHEN 3 THEN 33
  WHEN 4 THEN 44
  WHEN -999 THEN 55
  ELSE 66
END

On 5 July 2018 at 11:35, R Smith <[hidden email]> wrote:

> On 2018/07/05 8:44 AM, Simon Slavin wrote:
>
>> On 5 Jul 2018, at 7:30am, Clemens Ladisch <[hidden email]> 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 simply ignore).
>
> 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 CASE
> executes.
>
> My typical way to do this is:
>
> CASE
>     WHEN x IS NULL THEN ...
>     WHEN x < 1 THEN ...
>     WHEN x < 3 THEN ...
>     WHEN x < 5 THEN ...
>     ELSE ...
> END;
>
> But I feel like the equality check option can easily be enhanced in SQLite
> to have this work:
>
> CASE x
>     WHEN IS NULL THEN ....
>     WHEN  1 THEN ...
>     WHEN  3 THEN ...
>     WHEN  5 THEN ...
> END;
>
> 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 considering).
>
>
> Cheers,
> Ryan
>
> PS: Here is a version of the 1st example working:
>
> WITH C(x) AS (
>      SELECT NULL
>      UNION ALL
>      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'
>     ELSE 'Large'
>     END AS size
>   FROM C
> ;
>
>
>   -- 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users