Re: planner bug regarding lateral and subquery?

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!

Stephen