Обсуждение: Relation ordering in FROM clause causing error related to missing entry... Or not.

Hi all,

Today while playing with some queries I bumped into the following thing:
=# with count_query as (select generate_series(0,1) as a) select b
from count_query, generate_series(1, count_query.a) as b;b
---1
(1 row)
=# with count_query as (select generate_series(0,1) as a) select b
from generate_series(1, count_query.a) as b, count_query;
ERROR:  42P01: missing FROM-clause entry for table "count_query"
LINE 1: ...eries(0,1) as a) select b from generate_series(1, count_quer...
             ^
 
LOCATION:  errorMissingRTE, parse_relation.c:2850

I have been a little bit surprised by the fact that different entry
ordering in the FROM clause of the main query had different effects.
Perhaps there is something I am missing? I haven't looked at the code
but if this happens to be a bug I am fine to submit a patch.
Regards,
-- 
Michael

Michael,

* Michael Paquier (michael.paquier@gmail.com) wrote:
> Today while playing with some queries I bumped into the following thing:
> =# with count_query as (select generate_series(0,1) as a) select b
> from count_query, generate_series(1, count_query.a) as b;
>  b
> ---
>  1
> (1 row)

The above results in an implicit LATERAL being done.

> =# with count_query as (select generate_series(0,1) as a) select b
> from generate_series(1, count_query.a) as b, count_query;
> ERROR:  42P01: missing FROM-clause entry for table "count_query"
> LINE 1: ...eries(0,1) as a) select b from generate_series(1, count_quer...
>                                                              ^
> LOCATION:  errorMissingRTE, parse_relation.c:2850

This doesn't because the generate_series() is first- where would it get
the count_query relation?

> I have been a little bit surprised by the fact that different entry
> ordering in the FROM clause of the main query had different effects.
> Perhaps there is something I am missing? I haven't looked at the code
> but if this happens to be a bug I am fine to submit a patch.

Yeah, it's simply because we can turn one into an implicit LATERAL, but
we can't do that for the other.
Thanks,
    Stephen

Michael Paquier wrote:
> Hi all,
> 
> Today while playing with some queries I bumped into the following thing:
> =# with count_query as (select generate_series(0,1) as a) select b
> from count_query, generate_series(1, count_query.a) as b;
>  b
> ---
>  1
> (1 row)
> =# with count_query as (select generate_series(0,1) as a) select b
> from generate_series(1, count_query.a) as b, count_query;
> ERROR:  42P01: missing FROM-clause entry for table "count_query"
> LINE 1: ...eries(0,1) as a) select b from generate_series(1, count_quer...
>                                                              ^
> LOCATION:  errorMissingRTE, parse_relation.c:2850
> 
> I have been a little bit surprised by the fact that different entry
> ordering in the FROM clause of the main query had different effects.
> Perhaps there is something I am missing?

This seems natural to me -- in your second example, by the time you
reference count_query it hasn't yet been declared and thus it's not
available in the namespace.  This is how I expect a LATERAL reference to
work: a RTE can reference previous entries, but not ones that come
later.

(SRFs in FROM become lateral references automatically, as I recall.
Without LATERAL, you wouldn't have been able to refer to count_query at
all.)

-- 
Álvaro Herrera                http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Tue, Mar 10, 2015 at 10:30 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Yeah, it's simply because we can turn one into an implicit LATERAL, but
> we can't do that for the other.

Ah, yes, thanks. I forgot that it was changed to an implicit LATERAL.
Just wondering where my mind was yesterday night...
-- 
Michael