I happened to notice we have the case in memoize.sql that tests for
memoize node with LATERAL joins, which is
-- Try with LATERAL joins
SELECT explain_memoize('
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1) t2
WHERE t1.unique1 < 1000;', false);
ISTM this is not the right query for the test. After the subquery being
pulled up into the parent query, there will be no lateral references any
more. I'm thinking maybe we can add an ORDER BY clause in the subquery
to prevent it from being pulled up.
-- Try with LATERAL joins
SELECT explain_memoize('
SELECT COUNT(*),AVG(t2.unique1) FROM tenk1 t1,
LATERAL (SELECT t2.unique1 FROM tenk1 t2 WHERE t1.twenty = t2.unique1 ORDER BY 1) t2
WHERE t1.unique1 < 1000;', false);
Attach a trivial patch for the change.
Thanks
Richard
