Re: Multiple SRF right after SELECT - Mailing list pgsql-hackers

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