CASE TYPEOF(field) WHEN 'text' THEN .... doesn't seem to work

(1) By Jeffrey Friedl (jfriedl) on 2022-03-06 07:53:26 [source]

In some situation, doing a CASE against TYPEOF(field) does not match WHEN "text", even when the field contains text.

Given:

create table MyTable (
  `id`,
  `text`
);

insert into MyTable values(1,"this is some text");
insert into MyTable values(2,"this is more text");

SELECT TYPEOF(text),
  CASE       TYPEOF(text) WHEN  "text" THEN "expected" ELSE "unexpected" END,
  CASE  ">"||TYPEOF(text) WHEN ">text" THEN "expected" ELSE "unexpected" END
FROM MyTable;

I would expect:

text|expected|expected
text|expected|expected

But it (3.38.0) actually produces:

text|unexpected|expected
text|unexpected|expected

The bug, if in SQLITE and not your intrepid bug reporter, seems to depend on whether the field containing text is the first field, and on whether the name of the field is "text". That latter item points directly to user error of some kind, but I can't see it....

(2) By TripeHound on 2022-03-06 09:04:42 in reply to 1 [link] [source]

I've not tried your example, but double-quotes should be used to enclose identifiers (field/table names); single-quotes should be used for string-literals (although SQLite will treat something double-quoted as a string if it doesn't match a field name). Thus "text" should be referring to the field of that name. Possibly the "tolerance rules" have changed in 3.38, but at first glance, I'm more surprised that the first version worked as you expected it to.

(4) By Jeffrey Friedl (jfriedl) on 2022-03-06 10:31:00 in reply to 2 [link] [source]

Thank you (and John). Indeed, user error. I've been using sqlite for years, and am just learning the difference between quote types. 

Thanks much(!) and sorry for wasting your time.

(3) By John Dennis (jdennis) on 2022-03-06 10:16:30 in reply to 1 [link] [source]

It works perfectly in 3.38.0 (on Windows) when the doube quotes are replaced by single quotes, and the back-ticks around the column names in the create are removed. 

create table MyTable (
  id,
  text
);

insert into MyTable values(1,'this is some text');
insert into MyTable values(2,'this is more text');

SELECT TYPEOF(text),
  CASE       TYPEOF(text) WHEN  'text' THEN 'expected' ELSE 'unexpected' END,
  CASE  '>'||TYPEOF(text) WHEN '>text' THEN 'expected' ELSE 'unexpected' END
FROM MyTable;