Re: planner bug regarding lateral and subquery?

Hi Stephen,

On 2018/03/14 12:36, Stephen Frost wrote:
> Greetings,
>
> * Tatsuro Yamada (yamada(dot)tatsuro(at)lab(dot)ntt(dot)co(dot)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