Content-Type: multipart/alternative;
 boundary="------------YXoSzJEKmpuskJ3yCOsw4JG0"
Message-ID: <837b0dce-b2f3-4811-bccf-f42e8985d6e2@gmail.com>
Date: Sun, 22 Jun 2025 15:00:01 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: BUG #18953: Planner fails to build plan for complex query with
 LATERAL references
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-bugs@lists.postgresql.org, Richard Guo <guofenglinux@gmail.com>
References: <18953-1c9883a9d4afeb30@postgresql.org>
 <CAMbWs49XTfPD9OCw9xWsRtbiNYgQ-Nau_hHy9UbbGP-w2V=ANQ@mail.gmail.com>
 <1268645.1749765012@sss.pgh.pa.us> <1499514.1749848324@sss.pgh.pa.us>
 <CAMbWs4_E7wC3LX17jr+RnRunz9ROcwhn0eXt0riJ6FjbxB0g+g@mail.gmail.com>
 <527134.1750087495@sss.pgh.pa.us>
 <CAMbWs4_wYvQWHCwBfSXj9OQ-fWn+xnntR+EVzks2KB0eRWcRpw@mail.gmail.com>
 <835659.1750177751@sss.pgh.pa.us>
Content-Language: en-US
From: Alexander Lakhin <exclusion@gmail.com>
In-Reply-To: <835659.1750177751@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/837b0dce-b2f3-4811-bccf-f42e8985d6e2%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------YXoSzJEKmpuskJ3yCOsw4JG0
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

Hello Tom,

17.06.2025 19:29, Tom Lane wrote:
> So I'm inclined to leave that code as I had it.  It's notationally
> a bit simpler and it doesn't require assuming that we can ignore
> the path's required_outer marking at this stage.  If I'm wrong,
> someone will eventually find a counterexample and we can fix it
> then; the changes won't be large.

Please look at the following (simplified version of a query generated by
SQLsmith), which produces errors after a16ef313f2:
CREATE TABLE t(b bool); ANALYZE t;

SELECT 1 FROM t
INNER JOIN (SELECT (SELECT true) AS c FROM t, LATERAL (SELECT b LIMIT 1)) ON true
RIGHT JOIN (SELECT true) ON true,
LATERAL (SELECT b WHERE c LIMIT 1)
WHERE t.b;
ERROR:  XX000: unrecognized node type: 22
LOCATION:  ExecInitExprRec, execExpr.c:2665

Or a slight variation:
SELECT 1 FROM t
INNER JOIN (SELECT (SELECT true) AS c FROM t, LATERAL (SELECT 1 LIMIT 1)) ON true
RIGHT JOIN (SELECT true) ON true,
LATERAL (SELECT b WHERE c LIMIT 1)
WHERE t.b;
ERROR:  XX000: failed to assign all NestLoopParams to plan nodes
LOCATION:  create_plan, createplan.c:372

Best regards,
Alexander
--------------YXoSzJEKmpuskJ3yCOsw4JG0
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">Hello Tom,<br>
      <br>
      17.06.2025 19:29, Tom Lane wrote:<br>
    </div>
    <blockquote type="cite" cite="mid:835659.1750177751@sss.pgh.pa.us">
      <pre wrap="" class="moz-quote-pre">So I'm inclined to leave that code as I had it.  It's notationally
a bit simpler and it doesn't require assuming that we can ignore
the path's required_outer marking at this stage.  If I'm wrong,
someone will eventually find a counterexample and we can fix it
then; the changes won't be large.
</pre>
    </blockquote>
    <br>
    Please look at the following (simplified version of a query
    generated by<br>
    SQLsmith), which produces errors after a16ef313f2:<br>
    CREATE TABLE t(b bool); ANALYZE t;<br>
    <br>
    SELECT 1 FROM t<br>
    INNER JOIN (SELECT (SELECT true) AS c FROM t, LATERAL (SELECT b
    LIMIT 1)) ON true<br>
    RIGHT JOIN (SELECT true) ON true,<br>
    LATERAL (SELECT b WHERE c LIMIT 1)<br>
    WHERE t.b;<br>
    ERROR:  XX000: unrecognized node type: 22<br>
    LOCATION:  ExecInitExprRec, execExpr.c:2665<br>
    <br>
    Or a slight variation:<br>
    SELECT 1 FROM t<br>
    INNER JOIN (SELECT (SELECT true) AS c FROM t, LATERAL (SELECT 1
    LIMIT 1)) ON true<br>
    RIGHT JOIN (SELECT true) ON true,<br>
    LATERAL (SELECT b WHERE c LIMIT 1)<br>
    WHERE t.b;<br>
    ERROR:  XX000: failed to assign all NestLoopParams to plan nodes<br>
    LOCATION:  create_plan, createplan.c:372<br>
    <br>
    Best regards,<br>
    Alexander<br>
  </body>
</html>

--------------YXoSzJEKmpuskJ3yCOsw4JG0--