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
