På torsdag 11. mai 2017 kl. 23:37:27, skrev Yaroslav <ladayaros...@yandex.ru <mailto:ladayaros...@yandex.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
