Re: CTE inlining

På torsdag 11. mai 2017 kl. 23:37:27, skrev Yaroslav <ladayaroslav(at)yandex(dot)ru
<mailto:ladayaroslav(at)yandex(dot)ru>>:
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