From: | David BOURIAUD <david(dot)bouriaud(at)ac-rouen(dot)fr> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | Re: Multiple SRF right after SELECT |
Date: | 2008-03-19 12:47:03 |
Message-ID: | 200803191347.06443.david.bouriaud@ac-rouen.fr |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Alvaro Herrera | 2008-03-19 13:08:14 | stored procedure stats in collector |
Previous Message | Zdeněk Kotala | 2008-03-19 12:39:41 | Re: [PATCHES] Fix for large file support (nonsegment mode support) |