Improve LATERAL join case in test memoize.sql

From: Richard Guo <guofenglinux(at)gmail(dot)com>
To: PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org>
Subject: Improve LATERAL join case in test memoize.sql
Date: 2023-01-16 09:27:07
Message-ID: CAMbWs4_LHJaN4L-tXpKMiPFnsCJWU1P8Xh59o0W7AA6UN99=cQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Improve-LATERAL-join-case-in-test-memoize.sql.patch application/octet-stream 2.8 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Sandro Santilli 2023-01-16 09:35:43 Re: Ability to reference other extensions by schema in extension scripts
Previous Message Alexander Pyhalov 2023-01-16 08:18:08 Inconsistency in vacuum behavior