CASE WHEN False THEN MyFunction() END calls MyFunction
(1) By Paul van Helden (paulvh) on 2020-04-20 14:46:06 [link]
Hi, Is there something like SQLITE_DETERMINISTIC that would avoid MyFunction being called in this case? I'm using SQLite quite successfully as a scripting engine, but the above surprised me. Thanks, Paul.
(2.1) By Keith Medcalf (kmedcalf) on 2020-04-20 15:19:08 edited from 2.0 in reply to 1
Factoring of CASE and COALESCE expressions was changed from the initialization block to a once block resulting in short-circuit execution of COALESCE and CASE functions <https://www.sqlite.org/src/info/c5f96a085db9688a> and should appear in the next release of SQLite3 (3.32.0)
(3) By Richard Hipp (drh) on 2020-04-20 15:32:12 in reply to 1 [link]
This issue was addressed by check-in [c5f96a085db9688a]. Please try again using the "Prerelease Snapshot" on the [Download Page] and let us know whether or not this fixes your problem. : https://sqlite.org/src/timeline?c=c5f96a085db9688a : https://sqlite.org/download.html If you don't want to use unreleased code, then you can omit the SQLITE_DETERMINISTIC flag when you create MyFunction(), because it is that flag that is provoking the optimization that causes the function to be called even when it is not being used.
(4) By Paul van Helden (paulvh) on 2020-04-20 15:59:34 in reply to 3 [link]
Fantastic. Thank you!
(5) By Richard PArkins (rparkins) on 2021-02-18 14:42:18 in reply to 3 [link]
Does ths answer imply a guarantee of the order of evaluation of a CASE expression? For example if I write `CASE WHEN MyFunction(1) THEN 'one' WHEN MyFunction(2) THEN 'two' ELSE 'neither' END` is it guaranteed that if the SQLITE_DETERMINISTIC flag is not set and MyFunction (1) returns true, then MyFunction(2) will not be called? MyFunction() has side-effects, and I can't use two separate statements because the CASE expression is inside an UPDATE statement over a whole table.
(6) By Richard Hipp (drh) on 2021-02-18 15:29:24 in reply to 5 [link]
(7) By anonymous on 2021-02-19 09:54:47 in reply to 6 [link]
I've created a small experiment. Here is the sql-code: select 'Test and set flag 1'; select fs(1); -- is flag 1 set? O = clear 1 = set select case sf(1) when 0 then 'Failed to set flag 1!' when 1 then 'Success! Flag 1 is set' end; -- set flag 1 select case 1 when fsc(1) THEN "Flag 1 set and cleared" when fc(1) THEN "Flag 1 is clear" end as 'flag status?'; -- clear flag 1 select case fc(1); select case 1 when fsc(1) THEN "Flag 1 is set and cleared" when fc(1) THEN "Flag 1 is clear" end as 'flag status?'; And this is the result: Test and set flag 1 Success! Flag 1 is set Flag 1 set and cleared Flag 1 is clear It seems to me that this is deterministic.
(8) By anonymous on 2021-02-19 12:13:29 in reply to 5 [link]
Another experiment (the is code used in a trigger) UPDATE OR ROLLBACK DEVICE_COM_SETTINGS SET DCS_CONNECTION_TYPE = 2, -- Slaves always use a wired connection DCS_URI = (CASE 1 -- 🄰 → NODE > 0 AND it's alway an internal IP-address! WHEN ((SELECT VALUE FROM _Variables WHERE NAME == 'NEW_STATUS') == 4) -- ← Only state 4 is allows changeing the URI! THEN '255.255.255.255' -- ← Status change 2 → 4 ELSE CASE 1 -- 🄱 → Is an internal IP-adress defined? (Status change 4 → 8) WHEN EXISTS (SELECT 1 FROM _Variables WHERE NAME == 'INTERNAL_IP') AND ((SELECT VALUE FROM _Variables WHERE NAME == 'NEW_STATUS') == 8) THEN (SELECT VALUE FROM _Variables WHERE NAME == 'INTERNAL_IP') -- → externally defined as priority! ELSE NEW.URI -- → defined by THE parameter set by the USER END END) WHERE DCS_PK_SERIAL_NUMBER == NEW.SERIAL_NUMBER; No problem using multiple statements.