planner bug regarding lateral and subquery? - Mailing list pgsql-hackers

Hi Hackers,

I found a bug, maybe.
If it is able to get an explain command result from below query successfully,
I think that it means the query is executable. However, I got an error by
executing the query without an explain command. I guess that planner makes a wrong plan.

I share a reproduction procedure and query results on 3b7ab4380440d7b14ee390fabf39f6d87d7491e2.

* Reproduction
====================================================
create table test (c1 integer, c2 integer, c3 text);
insert into test values (1, 3, 'a');
insert into test values (2, 4, 'b');

explain (costs off)
select
   subq_1.c0
from
   test as ref_0,
   lateral (select subq_0.c0 as c0
            from
                 (select ref_0.c2 as c0,
                 (select c1 from test) as c1 from test as ref_1
            where (select c3 from test) is NULL) as subq_0
            right join test as ref_2
            on (subq_0.c1 = ref_2.c1 )) as subq_1;

select
   subq_1.c0
from
   test as ref_0,
   lateral (select subq_0.c0 as c0
            from
                 (select ref_0.c2 as c0,
                 (select c1 from test) as c1 from test as ref_1
            where (select c3 from test) is NULL) as subq_0
            right join test as ref_2
            on (subq_0.c1 = ref_2.c1 )) as subq_1;


* Result of Explain: succeeded
====================================================
# explain (costs off)
select
   subq_1.c0
from
   test as ref_0,
   lateral (select subq_0.c0 as c0
            from
                 (select ref_0.c2 as c0,
                 (select c1 from test) as c1 from test as ref_1
            where (select c3 from test) is NULL) as subq_0
            right join test as ref_2
            on (subq_0.c1 = ref_2.c1 )) as subq_1;

                     QUERY PLAN
---------------------------------------------------
  Nested Loop
    InitPlan 1 (returns $0)
      ->  Seq Scan on test
    InitPlan 2 (returns $1)
      ->  Seq Scan on test test_1
    ->  Seq Scan on test ref_0
    ->  Nested Loop Left Join
          Join Filter: ($1 = ref_2.c1)
          ->  Seq Scan on test ref_2
          ->  Materialize
                ->  Result
                      One-Time Filter: ($0 IS NULL)
                      ->  Seq Scan on test ref_1

* Result of Select: failed
====================================================
# select
   subq_1.c0
from
   test as ref_0,
   lateral (select subq_0.c0 as c0
            from
                 (select ref_0.c2 as c0,
                 (select c1 from test) as c1 from test as ref_1
            where (select c3 from test) is NULL) as subq_0
            right join test as ref_2
            on (subq_0.c1 = ref_2.c1 )) as subq_1;

ERROR:  more than one row returned by a subquery used as an expression


* The error message came from here
====================================================
./src/backend/executor/nodeSubplan.c

        if (found &&
             (subLinkType == EXPR_SUBLINK ||
              subLinkType == MULTIEXPR_SUBLINK ||
              subLinkType == ROWCOMPARE_SUBLINK))
             ereport(ERROR,
                     (errcode(ERRCODE_CARDINALITY_VIOLATION),
                      errmsg("more than one row returned by a subquery used as an expression")));


Thanks,
Tatsuro Yamada