Re: planner bug regarding lateral and subquery?
Hi Stephen, On 2018/03/14 12:36, Stephen Frost wrote: Greetings, * Tatsuro Yamada (yamada.tats...@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
Re: planner bug regarding lateral and subquery?
On Tuesday, March 13, 2018, Tatsuro Yamadawrote: > 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.
Re: planner bug regarding lateral and subquery?
Greetings, * Tatsuro Yamada (yamada.tats...@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 signature.asc Description: PGP signature