Re: Multiple SRF right after SELECT

Le mercredi 19 mars 2008, Albert Cervera i Areny a écrit :
Hi !

> A Dimecres 19 Març 2008, Nikolay Samokhvalov va escriure:
> > 2. Why the query above provides 4 rows, not 2*4=8? Actually, that's
> > interesting -- I can use this query to find l.c.m. But it's defenetely
> > not that I'd expect before my try...
>
> 2*4 = 8:
>
> select * from generate_series(1, 2) a, generate_series(1, 4) b;

If you launch the above query, you just get what you would get if you would do
a select from two tables without joining them at all...
So, you get the cartesian product of the two ensembles.

>
> Can't tell you about the expected behaviour in the query you provided
> though.

I've made few tests with the primary query, and indeed it is strange
behavoiour. Consider the following :

select generate_series(1, 3), generate_series(1, 4);
generate_series | generate_series
-----------------+-----------------
1 | 1
2 | 2
3 | 3
1 | 4
2 | 1
3 | 2
1 | 3
2 | 4
3 | 1
1 | 2
2 | 3
3 | 4
which is not fully readeable but if you sort things, you get exactly the same
as what you mentionned before :

select generate_series(1, 3), generate_series(1, 4) order by 1,2;
generate_series | generate_series
-----------------+-----------------
1 | 1
1 | 2
1 | 3
1 | 4
2 | 1
2 | 2
2 | 3
2 | 4
3 | 1
3 | 2
3 | 3
3 | 4

So far it is clear, but if you just make things so that the the number of rows
returned by one call to generate_series is a multiple of the other, the
result is truncated :

select generate_series(1, 3), generate_series(1, 6) order by 1,2;
generate_series | generate_series
-----------------+-----------------
1 | 1
1 | 4
2 | 2
2 | 5
3 | 3
3 | 6

provides the same strange result as initialy discovered, and
select generate_series(1, 6), generate_series(1, 3) order by 2,1;
generate_series | generate_series
-----------------+-----------------
1 | 1
4 | 1
2 | 2
5 | 2
3 | 3
6 | 3

provides the same, mirrored. So, it could be a bug somewhere.
Hoping that it will be of any help...
Regards.

>
> --
> Albert Cervera i Areny
> http://www.NaN-tic.com