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
signature.asc
Description: This is a digitally signed message part.
