CASE WHEN False THEN MyFunction() END calls MyFunction
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.
(2.1) By Keith Medcalf (kmedcalf) on 2020-04-20 15:19:08 edited from 2.0 in reply to 1 [link] [source]
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)
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.
Fantastic. Thank you!
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.
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.
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.