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