Ilya Shkuratov wrote
> First of all, to such replacement to be valid, the CTE must be
> 1. non-writable (e.g. be of form: SELECT ...),
> 2. do not use VOLATILE or STABLE functions,
> 3. ... (maybe there must be more restrictions?)
What about simple things like this?
CREATE OR REPLACE FUNCTION z(numeric) RETURNS boolean AS $$
BEGIN
RETURN $1 <> 0;
END;
$$ LANGUAGE plpgSQL IMMUTABLE COST 1000;
-- This one works:
WITH T AS (
SELECT 1.0 AS v1, 0.0 AS v2
UNION ALL
SELECT 3.0, 1.0
UNION ALL
SELECT 2.0, 0.0
), a AS (
SELECT *
FROM t
WHERE z(v2)
)
SELECT *
FROM a
WHERE v1/v2 > 1.5;
-- This one gives 'division by zero':
WITH T AS (
SELECT 1.0 AS v1, 0.0 AS v2
UNION ALL
SELECT 3.0, 1.0
UNION ALL
SELECT 2.0, 0.0
)
SELECT *
FROM (
SELECT *
FROM t
WHERE z(v2)
) AS a
WHERE v1/v2 > 1.5;
From a non-hacker;
Just to se what other RDBMS are doing with CTEs; Look at slide 31 here: https://www.percona.com/live/17/sites/default/files/slides/Recursive%20Query%20Throwdown.pdf
PG is not on top wrt. CTE, but could have been if CTEs were not this "established" fence.
+1 for removing this fence and get all the possible optimization we can.
--
Andreas Joseph Krogh
