planner bug regarding lateral and subquery?

Started by Tatsuro Yamadaalmost 8 years ago5 messages

yamada.tatsuro@lab.ntt.co.jp

almost 8 years ago

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

sfrost@snowman.net

almost 8 years ago

In reply to: Tatsuro Yamada (#1)

Re: planner bug regarding lateral and subquery?

Greetings,

* Tatsuro Yamada (yamada.tatsuro@lab.ntt.co.jp) wrote:

I found a bug, maybe.

I don't think so...

* 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

You don't need LATERAL or anything complicated to reach that error,
simply do:

=*> select * from test where (select c1 from test) is null;
ERROR: more than one row returned by a subquery used as an expression

The problem there is that the WHERE clause is trying to evaluate an
expression, which is "(select c1 from test) is null" and you aren't
allowed to have multiple rows returned from that subquery (otherwise,
how would we know which row to compare in the expression..?).

If you're actually intending to refer to the 'c3' column from the test
through the lateral join, you would just refer to it as 'ref_0.c3', as
you do in another part of that query.

Thanks!

Stephen

David G. Johnston

david.g.johnston@gmail.com

almost 8 years ago

In reply to: Tatsuro Yamada (#1)

Re: planner bug regarding lateral and subquery?

On Tuesday, March 13, 2018, Tatsuro Yamada <yamada.tatsuro@lab.ntt.co.jp>
wrote:

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.

There is a difference between executable, compilable, and able to execute
to completion, runtime, on specific data. You've proven the former but as
the error indicates specific data causes the complete execution of the
query to fail.

I can write "select cola / colb from tbl" and as long as there are no zeros
in colb the query will complete, but if there is you get a divide by zero
runtime error. This is similar.

David J.

yamada.tatsuro@lab.ntt.co.jp

almost 8 years ago

In reply to: Stephen Frost (#2)

Re: planner bug regarding lateral and subquery?

Hi Stephen,

On 2018/03/14 12:36, Stephen Frost wrote:

Greetings,

* Tatsuro Yamada (yamada.tatsuro@lab.ntt.co.jp) wrote:

I found a bug, maybe.

I don't think so...

* 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

You don't need LATERAL or anything complicated to reach that error,
simply do:

=*> select * from test where (select c1 from test) is null;
ERROR: more than one row returned by a subquery used as an expression

The problem there is that the WHERE clause is trying to evaluate an
expression, which is "(select c1 from test) is null" and you aren't
allowed to have multiple rows returned from that subquery (otherwise,
how would we know which row to compare in the expression..?).

If you're actually intending to refer to the 'c3' column from the test
through the lateral join, you would just refer to it as 'ref_0.c3', as
you do in another part of that query.

Thanks for your reply.

The query is not useful for me and it's just a test query for planner
because it is made by sqlsmith. :)
My question is that was it possible to handle the error only in
executer phase? I expected that it is checked in parsing or planning phase.

Thanks,
Tatsuro Yamada

yamada.tatsuro@lab.ntt.co.jp

almost 8 years ago

In reply to: David G. Johnston (#3)

Re: planner bug regarding lateral and subquery?

Hi David and Stephen,

On 2018/03/14 12:59, David G. Johnston wrote:

On Tuesday, March 13, 2018, Tatsuro Yamada <yamada.tatsuro@lab.ntt.co.jp <mailto:yamada.tatsuro@lab.ntt.co.jp>> wrote:

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.

There is a difference between executable, compilable, and able to execute to completion, runtime, on specific data. You've proven the former but as the error indicates specific data causes the complete execution of the query to fail.

I can write "select cola / colb from tbl" and as long as there are no zeros in colb the query will complete, but if there is you get a divide by zero runtime error. This is similar.

David J.

Thank you for the explanation.
I understand that it's a runtime error and not able to avoid in parser and planner. :)

Thanks,
Tatsuro Yamada