From: | Tatsuro Yamada <yamada(dot)tatsuro(at)lab(dot)ntt(dot)co(dot)jp> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | planner bug regarding lateral and subquery? |
Date: | 2018-03-14 03:26:54 |
Message-ID: | 71442da7-fe14-2c6f-691e-0f4ed401bd1a@lab.ntt.co.jp |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Hongyuan Ma | 2018-03-14 03:31:28 | Re:Re: Re: [GSOC 18] Performance Farm Project——Initialization Project |
Previous Message | Michael Paquier | 2018-03-14 02:54:33 | Re: Fixes for missing schema qualifications |