I have two queries is postgresql:
1. SELECT CASE WHEN (1=1) THEN NULL ELSE cast(1/0 as text) END; 2. SELECT CASE WHEN (EXISTS (SELECT 10)) THEN NULL ELSE cast(1/0 as text) END;
As you might have noticed result of the first condition in both queries is true, but result of first query is null and result of the second one is ERROR: division by zero
What is happening in here?
Is there any optimization in order of evaluation occurring? If yes, is there any why to turn it off?
Is there any why to have a complex query in the condition block that doesn’t trigger runtime error in else block when result of condition is true?
Postgresql version: 13.1
Advertisement
Answer
The error happens when the query is being planned, not when it is run.
In the first case, 1=1 is known to be true at planning time, causing the entire CASE to collapse to just NULL::text. So the constant folding of 1=1 and of CASE WHEN true saves you from the constant-folding exception of 1/0. You can see this by doing EXPLAIN VERBOSE.
Is there any why to have a complex query in the condition block that doesn’t trigger runtime error in else block when result of condition is true?
Yes, except it isn’t really at run time where the problem manifests.
Just like a dummy SELECT defeats the constant folding that prevents the problem, another dummy select can defeat the constant folding that causes the problem.
SELECT CASE WHEN (EXISTS (SELECT 10)) THEN NULL::text ELSE cast (1/(select 0) as text) END;