[HACKERS] Multiple SRF right after SELECT
Hi, I wonder, if the following is correct and provides expected result: test=# select generate_series(1, 2), generate_series(1, 4); generate_series | generate_series -+- 1 | 1 2 | 2 1 | 3 2 | 4 (4 rows) Actually I have two questions on this: 1. Is it correct at all to use SRF in select list, w/o explicit FROM? Why then we do not allow using subselects that return multiple rows? I'd rather expect that these two things work in similar manner. 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... -- Best regards, Nikolay -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multiple SRF right after SELECT
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; Can't tell you about the expected behaviour in the query you provided though. -- Albert Cervera i Areny http://www.NaN-tic.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 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 signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Multiple SRF right after SELECT
On Wed, 19 Mar 2008, Nikolay Samokhvalov [EMAIL PROTECTED] writes: I wonder, if the following is correct and provides expected result: test=# select generate_series(1, 2), generate_series(1, 4); generate_series | generate_series -+- 1 | 1 2 | 2 1 | 3 2 | 4 (4 rows) 1. Is it correct at all to use SRF in select list, w/o explicit FROM? Why then we do not allow using subselects that return multiple rows? I'd rather expect that these two things work in similar manner. 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... From PL/scheme sources: /* * There're 2 ways to return from an SRF: * * 1. Value-per-call Mode *You return each tuple one by one via SRF_RETURN_NEXT() macro. But *PG_RETURN_DATUM() calls in the macro, makes it quite *impracticble. OTOH, this method gives opportunity to call SRFs in *a fashion like SELECT mysrf(); * * 2. Materialize Mode *In this mode, you collect all tuples in a single set and return *that set. When compared to previos method, it's not possible to *use SRF of materialize mode like SELECT my_materialized_srf();, *instead, you need to access it as a simple table: SELECT * FROM *my_materialized_srf();. * * ... */ And I conclude to that generate_series() is written as a SRF function of value-per-call mode. (Also you may want to check Returning Sets[1] chapter at PostgreSQL manual.) [1] http://www.postgresql.org/docs/current/static/xfunc-c.html#XFUNC-C-RETURN-SET Regards. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Multiple SRF right after SELECT
Nikolay Samokhvalov [EMAIL PROTECTED] writes: 1. Is it correct at all to use SRF in select list, w/o explicit FROM? You can read about the current behavior in ExecTargetList, but basically the idea is to cycle all the SRFs until they all say done at the same time. So the number of result rows is the least common multiple of the sizes of the SRF outputs. This behavior is pretty random, I agree, but we inherited it from Berkeley ... and even if we didn't care about breaking existing applications that might rely on it, it's not exactly clear what it should be instead. The fact that there isn't any obviously right behavior is why I'd prefer to deprecate SRFs in targetlists ... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
