Re: generate_series

hi,

the following behaviour of multiple generate_series in a select seems
a little counter-intuitive to me. i expected that the former returns 4 rows.
where should i look to learn the exact semantics? (documentation and/or code)

YAMAMOTO Takashi

test=# select generate_series(1,2) as a,generate_series(1,2) as b;
a | b
---+---
1 | 1
2 | 2
(2 rows)

test=# select generate_series(1,2) as a,generate_series(1,3) as b;
a | b
---+---
1 | 1
2 | 2
1 | 3
2 | 1
1 | 2
2 | 3
(6 rows)

On 15 February 2011 02:06, YAMAMOTO Takashi <yamt(at)mwd(dot)biglobe(dot)ne(dot)jp> wrote:
> hi,
>
> the following behaviour of multiple generate_series in a select seems
> a little counter-intuitive to me.  i expected that the former returns 4 rows.
> where should i look to learn the exact semantics?  (documentation and/or code)
>
> YAMAMOTO Takashi
>
> test=# select generate_series(1,2) as a,generate_series(1,2) as b;
>  a | b
> ---+---
>  1 | 1
>  2 | 2
> (2 rows)
>
> test=# select generate_series(1,2) as a,generate_series(1,3) as b;
>  a | b
> ---+---
>  1 | 1
>  2 | 2
>  1 | 3
>  2 | 1
>  1 | 2
>  2 | 3
> (6 rows)

The output of such queries will keep producing output until all
generate_series functions are at their end simultaneously. I think
this may be due to none of the functions actually getting to decide
when the series ends unless it's unanimous... or something like that.

If you're looking for the first one to product 4 rows as its output,
you may actually want:

postgres=# select * from generate_series(1,2) as a,generate_series(1,2) as b;
a | b
---+---
1 | 1
1 | 2
2 | 1
2 | 2
(4 rows)

If you select FROM generate_series, they start getting treated like
tables, and you end up with a cartesian product.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Thom Brown <thom(at)linux(dot)com> writes:
> On 15 February 2011 02:06, YAMAMOTO Takashi <yamt(at)mwd(dot)biglobe(dot)ne(dot)jp> wrote:
>> the following behaviour of multiple generate_series in a select seems
>> a little counter-intuitive to me.

> The output of such queries will keep producing output until all
> generate_series functions are at their end simultaneously.

Right, so the actual number of result rows is the least common multiple
of their periods. This is undocumented because nobody is very happy
with it and we don't want users relying on it. Eventually we may
deprecate set-returning functions in SELECT's targetlist altogether,
but that won't happen until there's an adequate replacement (possibly
LATERAL). In the meantime it's best to avoid having more than one per
SELECT.

regards, tom lane