Re: [sqlite] CASE and NULL
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 wrote: > On 2018/07/05 8:44 AM, Simon Slavin wrote: > >> On 5 Jul 2018, at 7:30am, Clemens Ladisch 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On 2018/07/05 8:44 AM, Simon Slavin wrote: On 5 Jul 2018, at 7:30am, Clemens Ladisch 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On Thursday, 5 July, 2018 00:57, Donald Shepherd : >On Thu, 5 Jul 2018 at 16:45, Simon Slavin >wrote: >> On 5 Jul 2018, at 7:30am, Clemens Ladisch >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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On Thu, 5 Jul 2018 at 16:45, Simon Slavin wrote: > On 5 Jul 2018, at 7:30am, Clemens Ladisch 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On 5 Jul 2018, at 7:30am, Clemens Ladisch 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] CASE and NULL
On 5 Jul 2018, at 6:22am, Andy Goth 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] CASE and NULL
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 | ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
