Обсуждение: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF)
Folks, I'd like to see about getting an enhanced UNNEST, first the one according to the SQL standard, namely with an optional WITH ORDINALITY clause, and possibly some extra enhancements. In order to get WITH ORDINALITY, would it be better to change gram.y to account for both WITH ORDINALITY and without, or just for the WITH ORDINALITY case? Also, there's been some enthusiasm for a different kind of enhancement of UNNEST, which would go something like this: UNNEST(anyarray, number_of_dimensions_to_unnest) That'd probably be a separate patch, though. Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, Nov 19, 2010 at 08:33, David Fetter <david@fetter.org> wrote: > In order to get WITH ORDINALITY, would it be better to change gram.y > to account for both WITH ORDINALITY and without, or just for the WITH > ORDINALITY case? We probably need to change gram.y and make UNNEST to be COL_NAME_KEYWORD. UNNEST (without ORDINALITY) will call the existing unnest() function, and UNNEST() WITH ORDINALITY will call unnest_with_ordinality(). BTW, what will we return for arrays with 2 or more dimensions? There are no confusion in your two arguments version: > UNNEST(anyarray, number_of_dimensions_to_unnest) but we will also support one argument version. Array indexes will be composite numbers in the cases. The possible design would be just return sequential serial numbers of the values -- the following two queries return the same results: - SELECT i, v FROM UNNEST($1) WITH ORDINALITY AS t(v, i) - SELECT row_number() OVER () AS i, v FROM UNNEST($1) AS t(v) -- Itagaki Takahiro
On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote: > On Fri, Nov 19, 2010 at 08:33, David Fetter <david@fetter.org> wrote: > > In order to get WITH ORDINALITY, would it be better to change > > gram.y to account for both WITH ORDINALITY and without, or just > > for the WITH ORDINALITY case? > > We probably need to change gram.y and make UNNEST to be > COL_NAME_KEYWORD. UNNEST (without ORDINALITY) will call the > existing unnest() function, and UNNEST() WITH ORDINALITY will call > unnest_with_ordinality(). Thanks for sketching that out :) > BTW, what will we return for arrays with 2 or more dimensions? At the moment, per the SQL standard, UNNEST without the WITH ORDINALITY clause flattens all dimensions. SELECT * FROM UNNEST(ARRAY[[1,2],[3,4]]);unnest -------- 1 2 3 4 (4 rows) Unless we want to do something super wacky and contrary to the SQL standard, UNNEST(array) WITH ORDINALITY should do the same. > There are no confusion in your two arguments version: > > UNNEST(anyarray, number_of_dimensions_to_unnest) > but we will also support one argument version. Array indexes will > be composite numbers in the cases. The possible design would be just > return sequential serial numbers of the values -- the following two > queries return the same results: > > - SELECT i, v FROM UNNEST($1) WITH ORDINALITY AS t(v, i) > - SELECT row_number() OVER () AS i, v FROM UNNEST($1) AS t(v) Yes, that's what the standard says. Possible less-than-total unrolling schemes include: - Flatten specified number of initial dimensions into one list, e.g. turn UNNEST(array_3d, 2) into SETOF(array_1d) with onecolumn of ORDINALITY - Flatten similarly, but have an ORDINALITY column for each flattened dimension. - More exotic schemes, such as UNNEST(array_3d, [1,3]), with either of the two methods above. And of course the all-important: - Other possibilities I haven't thought of :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Note the standard also supports unnesting multiple arrays concurrently, the rule for handling arrays with different lengthsis to use null padding of the shorter array. SELECT * FROM UNNEST( ARRAY[5,2,3,4], ARRAY['hello', 'world'] ) WITH ORDINALITY AS t(a,b,i); a b i --- ---------- ------ 5 'hello' 1 2 'world' 2 3 3 4 4 (4 rows) To implement this it is not just substituting the existing unnest(anyarray) function in multiple times. Regards, Caleb On Nov 19, 2010, at 4:50 AM, <pgsql-hackers-owner@postgresql.org<mailto:pgsql-hackers-owner@postgresql.org>> <pgsql-hackers-owner@postgresql.org<mailto:pgsql-hackers-owner@postgresql.org>>wrote: From: David Fetter <david@fetter.org<mailto:david@fetter.org>> Date: November 18, 2010 11:48:16 PM PST To: Itagaki Takahiro <itagaki.takahiro@gmail.com<mailto:itagaki.takahiro@gmail.com>> Cc: PG Hackers <pgsql-hackers@postgresql.org<mailto:pgsql-hackers@postgresql.org>> Subject: Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF) On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote: On Fri, Nov 19, 2010 at 08:33, David Fetter <david@fetter.org<mailto:david@fetter.org>> wrote: In order to get WITH ORDINALITY, would it be better to change gram.y to account for both WITH ORDINALITY and without, or just for the WITH ORDINALITY case? We probably need to change gram.y and make UNNEST to be COL_NAME_KEYWORD. UNNEST (without ORDINALITY) will call the existing unnest() function, and UNNEST() WITH ORDINALITY will call unnest_with_ordinality(). Thanks for sketching that out :) BTW, what will we return for arrays with 2 or more dimensions? At the moment, per the SQL standard, UNNEST without the WITH ORDINALITY clause flattens all dimensions. SELECT * FROM UNNEST(ARRAY[[1,2],[3,4]]); unnest -------- 1 2 3 4 (4 rows) Unless we want to do something super wacky and contrary to the SQL standard, UNNEST(array) WITH ORDINALITY should do the same. There are no confusion in your two arguments version: UNNEST(anyarray, number_of_dimensions_to_unnest) but we will also support one argument version. Array indexes will be composite numbers in the cases. The possible design would be just return sequential serial numbers of the values -- the following two queries return the same results: - SELECT i, v FROM UNNEST($1) WITH ORDINALITY AS t(v, i) - SELECT row_number() OVER () AS i, v FROM UNNEST($1) AS t(v) Yes, that's what the standard says. Possible less-than-total unrolling schemes include: - Flatten specified number of initial dimensions into one list, e.g.turn UNNEST(array_3d, 2) into SETOF(array_1d) with onecolumn ofORDINALITY - Flatten similarly, but have an ORDINALITY column for each flatteneddimension. - More exotic schemes, such as UNNEST(array_3d, [1,3]), with either ofthe two methods above. And of course the all-important: - Other possibilities I haven't thought of :) Cheers, David. -- David Fetter <david@fetter.org<mailto:david@fetter.org>> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com<mailto:david.fetter@gmail.com> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, Nov 19, 2010 at 01:48:06PM -0500, Caleb.Welton@emc.com wrote: > Note the standard also supports unnesting multiple arrays > concurrently, the rule for handling arrays with different lengths is > to use null padding of the shorter array. Interesting. I notice that our version doesn't support multiple-array UNNEST just yet. SELECT * FROM UNNEST(ARRAY[1,2,3,4], ARRAY['hello','world']); ERROR: function unnest(integer[], text[]) does not exist LINE 1: SELECT * FROM UNNEST(ARRAY[1,2,3,4], ARRAY['hello','world'])... ^ HINT: No function matches the given name and argument types. You might need to add explicit type casts. > > SELECT * FROM > UNNEST( ARRAY[5,2,3,4], > ARRAY['hello', 'world'] ) > WITH ORDINALITY AS t(a,b,i); > > a b i > --- ---------- ------ > 5 'hello' 1 > 2 'world' 2 > 3 3 > 4 4 > (4 rows) This looks a lot like an OUTER JOIN on the ORDINALITY column of each of the individual UNNEST...WITH ORDINALITYs. Given that we know the ORDINALITY in advance just by building the arrays, we could optimize this away from FULL JOIN to LEFT (or RIGHT) JOINs. > To implement this it is not just substituting the existing unnest(anyarray) function in multiple times. Right. > > Regards, > Caleb > > On Nov 19, 2010, at 4:50 AM, <pgsql-hackers-owner@postgresql.org<mailto:pgsql-hackers-owner@postgresql.org>> <pgsql-hackers-owner@postgresql.org<mailto:pgsql-hackers-owner@postgresql.org>>wrote: > > From: David Fetter <david@fetter.org<mailto:david@fetter.org>> > Date: November 18, 2010 11:48:16 PM PST > To: Itagaki Takahiro <itagaki.takahiro@gmail.com<mailto:itagaki.takahiro@gmail.com>> > Cc: PG Hackers <pgsql-hackers@postgresql.org<mailto:pgsql-hackers@postgresql.org>> > Subject: Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF) > > > On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote: > On Fri, Nov 19, 2010 at 08:33, David Fetter <david@fetter.org<mailto:david@fetter.org>> wrote: > In order to get WITH ORDINALITY, would it be better to change > gram.y to account for both WITH ORDINALITY and without, or just > for the WITH ORDINALITY case? > > We probably need to change gram.y and make UNNEST to be > COL_NAME_KEYWORD. UNNEST (without ORDINALITY) will call the > existing unnest() function, and UNNEST() WITH ORDINALITY will call > unnest_with_ordinality(). > > Thanks for sketching that out :) > > BTW, what will we return for arrays with 2 or more dimensions? > > At the moment, per the SQL standard, UNNEST without the WITH > ORDINALITY clause flattens all dimensions. > > SELECT * FROM UNNEST(ARRAY[[1,2],[3,4]]); > unnest > -------- > 1 > 2 > 3 > 4 > (4 rows) > > Unless we want to do something super wacky and contrary to the SQL > standard, UNNEST(array) WITH ORDINALITY should do the same. > > There are no confusion in your two arguments version: > UNNEST(anyarray, number_of_dimensions_to_unnest) > but we will also support one argument version. Array indexes will > be composite numbers in the cases. The possible design would be just > return sequential serial numbers of the values -- the following two > queries return the same results: > > - SELECT i, v FROM UNNEST($1) WITH ORDINALITY AS t(v, i) > - SELECT row_number() OVER () AS i, v FROM UNNEST($1) AS t(v) > > Yes, that's what the standard says. Possible less-than-total > unrolling schemes include: > > - Flatten specified number of initial dimensions into one list, e.g. > turn UNNEST(array_3d, 2) into SETOF(array_1d) with one column of > ORDINALITY > > - Flatten similarly, but have an ORDINALITY column for each flattened > dimension. > > - More exotic schemes, such as UNNEST(array_3d, [1,3]), with either of > the two methods above. > > And of course the all-important: > > - Other possibilities I haven't thought of :) > > Cheers, > David. > -- > David Fetter <david@fetter.org<mailto:david@fetter.org>> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com<mailto:david.fetter@gmail.com> > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate > -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
Excerpts from Caleb.Welton's message of vie nov 19 15:48:06 -0300 2010: > Note the standard also supports unnesting multiple arrays concurrently, the rule for handling arrays with different lengthsis to use null padding of the shorter array. > > SELECT * FROM > UNNEST( ARRAY[5,2,3,4], > ARRAY['hello', 'world'] ) > WITH ORDINALITY AS t(a,b,i); > > a b i > --- ---------- ------ > 5 'hello' 1 > 2 'world' 2 > 3 3 > 4 4 > (4 rows) Hmm, this is pretty interesting and useful --- I had to deal with some XPath code not long ago and I had to turn to plpgsql; I think it could have been done with multi-array unnest. -- Álvaro Herrera <alvherre@commandprompt.com> The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support
The other aspect of the standard that the Postgres implementation does not currently support is the fact that unnest is supposedto be defined in terms of laterally derived subqueries, e.g. you should be able to unnest another element from afrom list entry laterally on the left. CREATE TABLE t1(id int, values int[]); SELECT id, a FROM t1 UNNEST(values) as u(a); If you consider it in terms of LATERAL, which Postgres also doesn't support, then you may find that it works out much morecleanly to consider the multi-array unnest in terms of that rather than in terms of an outer join. Specifically sincearrays are implicitly ordered on their ordinality a simple array lookup is much easier/more efficient than performinga full fledged join operator. E.g. the rewrite is: SELECT id, values[i] as a FROM t1 LATERAL(SELECT generate_series(array_lower(values, 1), array_upper(values,1) ) as lat(i); But then LATERAL support is something that has been discussed on and off for a while without seeing much progress. Regards, Caleb On Nov 19, 2010, at 11:06 AM, David Fetter wrote: > On Fri, Nov 19, 2010 at 01:48:06PM -0500, Caleb.Welton@emc.com wrote: >> Note the standard also supports unnesting multiple arrays >> concurrently, the rule for handling arrays with different lengths is >> to use null padding of the shorter array. > > Interesting. I notice that our version doesn't support multiple-array > UNNEST just yet. > > SELECT * FROM UNNEST(ARRAY[1,2,3,4], ARRAY['hello','world']); > ERROR: function unnest(integer[], text[]) does not exist > LINE 1: SELECT * FROM UNNEST(ARRAY[1,2,3,4], ARRAY['hello','world'])... > ^ > HINT: No function matches the given name and argument types. You might need to add explicit type casts. > >> >> SELECT * FROM >> UNNEST( ARRAY[5,2,3,4], >> ARRAY['hello', 'world'] ) >> WITH ORDINALITY AS t(a,b,i); >> >> a b i >> --- ---------- ------ >> 5 'hello' 1 >> 2 'world' 2 >> 3 3 >> 4 4 >> (4 rows) > > This looks a lot like an OUTER JOIN on the ORDINALITY column of each > of the individual UNNEST...WITH ORDINALITYs. Given that we know the > ORDINALITY in advance just by building the arrays, we could optimize > this away from FULL JOIN to LEFT (or RIGHT) JOINs. > >> To implement this it is not just substituting the existing unnest(anyarray) function in multiple times. > > Right. > >> >> Regards, >> Caleb >> >> On Nov 19, 2010, at 4:50 AM, <pgsql-hackers-owner@postgresql.org<mailto:pgsql-hackers-owner@postgresql.org>> <pgsql-hackers-owner@postgresql.org<mailto:pgsql-hackers-owner@postgresql.org>>wrote: >> >> From: David Fetter <david@fetter.org<mailto:david@fetter.org>> >> Date: November 18, 2010 11:48:16 PM PST >> To: Itagaki Takahiro <itagaki.takahiro@gmail.com<mailto:itagaki.takahiro@gmail.com>> >> Cc: PG Hackers <pgsql-hackers@postgresql.org<mailto:pgsql-hackers@postgresql.org>> >> Subject: Re: UNNEST ... WITH ORDINALITY (AND POSSIBLY OTHER STUFF) >> >> >> On Fri, Nov 19, 2010 at 11:40:05AM +0900, Itagaki Takahiro wrote: >> On Fri, Nov 19, 2010 at 08:33, David Fetter <david@fetter.org<mailto:david@fetter.org>> wrote: >> In order to get WITH ORDINALITY, would it be better to change >> gram.y to account for both WITH ORDINALITY and without, or just >> for the WITH ORDINALITY case? >> >> We probably need to change gram.y and make UNNEST to be >> COL_NAME_KEYWORD. UNNEST (without ORDINALITY) will call the >> existing unnest() function, and UNNEST() WITH ORDINALITY will call >> unnest_with_ordinality(). >> >> Thanks for sketching that out :) >> >> BTW, what will we return for arrays with 2 or more dimensions? >> >> At the moment, per the SQL standard, UNNEST without the WITH >> ORDINALITY clause flattens all dimensions. >> >> SELECT * FROM UNNEST(ARRAY[[1,2],[3,4]]); >> unnest >> -------- >> 1 >> 2 >> 3 >> 4 >> (4 rows) >> >> Unless we want to do something super wacky and contrary to the SQL >> standard, UNNEST(array) WITH ORDINALITY should do the same. >> >> There are no confusion in your two arguments version: >> UNNEST(anyarray, number_of_dimensions_to_unnest) >> but we will also support one argument version. Array indexes will >> be composite numbers in the cases. The possible design would be just >> return sequential serial numbers of the values -- the following two >> queries return the same results: >> >> - SELECT i, v FROM UNNEST($1) WITH ORDINALITY AS t(v, i) >> - SELECT row_number() OVER () AS i, v FROM UNNEST($1) AS t(v) >> >> Yes, that's what the standard says. Possible less-than-total >> unrolling schemes include: >> >> - Flatten specified number of initial dimensions into one list, e.g. >> turn UNNEST(array_3d, 2) into SETOF(array_1d) with one column of >> ORDINALITY >> >> - Flatten similarly, but have an ORDINALITY column for each flattened >> dimension. >> >> - More exotic schemes, such as UNNEST(array_3d, [1,3]), with either of >> the two methods above. >> >> And of course the all-important: >> >> - Other possibilities I haven't thought of :) >> >> Cheers, >> David. >> -- >> David Fetter <david@fetter.org<mailto:david@fetter.org>> http://fetter.org/ >> Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter >> Skype: davidfetter XMPP: david.fetter@gmail.com<mailto:david.fetter@gmail.com> >> iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics >> >> Remember to vote! >> Consider donating to Postgres: http://www.postgresql.org/about/donate >> > > -- > David Fetter <david@fetter.org> http://fetter.org/ > Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter > Skype: davidfetter XMPP: david.fetter@gmail.com > iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics > > Remember to vote! > Consider donating to Postgres: http://www.postgresql.org/about/donate >
On Fri, Nov 19, 2010 at 04:11:56PM -0500, Caleb.Welton@emc.com wrote: > The other aspect of the standard that the Postgres implementation > does not currently support is the fact that unnest is supposed to be > defined in terms of laterally derived subqueries, e.g. you should be > able to unnest another element from a from list entry laterally on > the left. > > CREATE TABLE t1(id int, values int[]); SELECT id, a FROM t1 > UNNEST(values) as u(a); > > If you consider it in terms of LATERAL, which Postgres also doesn't > support, then you may find that it works out much more cleanly to > consider the multi-array unnest in terms of that rather than in > terms of an outer join. Specifically since arrays are implicitly > ordered on their ordinality a simple array lookup is much > easier/more efficient than performing a full fledged join operator. > > E.g. the rewrite is: SELECT id, values[i] as a FROM t1 > LATERAL(SELECT generate_series(array_lower(values, 1), > array_upper(values, 1) ) as lat(i); > > But then LATERAL support is something that has been discussed on and > off for a while without seeing much progress. Is LATERAL something you'd like to put preliminary support in for? :) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate
On Sat, Nov 20, 2010 at 03:48, <Caleb.Welton@emc.com> wrote: > Note the standard also supports unnesting multiple arrays concurrently, the rule for handling arrays with different lengthsis to use null padding of the shorter array. > > UNNEST( ARRAY[5,2,3,4], > ARRAY['hello', 'world'] ) > WITH ORDINALITY AS t(a,b,i); Hmmm, that means we cannot support multi-array unnest() with our generic aggregate functions. The function prototype might be like below, but we don't support such definition. unnest(anyarray1, anyarray2, ..., OUT anyelement1, OUT anyelement2, ...) RETURNS SETOF record So, we would need a special representation for multi-array unnest(). -- Itagaki Takahiro
On Sat, Nov 20, 2010 at 01:54:32PM +0900, Itagaki Takahiro wrote: > On Sat, Nov 20, 2010 at 03:48, <Caleb.Welton@emc.com> wrote: > > Note the standard also supports unnesting multiple arrays concurrently, the rule for handling arrays with different lengthsis to use null padding of the shorter array. > > > > UNNEST( ARRAY[5,2,3,4], > > ARRAY['hello', 'world'] ) > > WITH ORDINALITY AS t(a,b,i); > > Hmmm, that means we cannot support multi-array unnest() with our > generic aggregate functions. The function prototype might be like > below, but we don't support such definition. > > unnest(anyarray1, anyarray2, ..., > OUT anyelement1, OUT anyelement2, ...) > RETURNS SETOF record > > So, we would need a special representation for multi-array unnest(). Using bits we already have, I came up with a way to do the things UNNEST(multiple, arrays, here) WITH ORDINALITY does. At least in theory, this is a matter of silently engaging the rewrite rule system: \set foo ARRAY[1,2,4,8] \set bar ARRAY['Here','is','some','odd','text','of','a','different','length'] \set baz ARRAY['Here','is','yet','more','text'] WITH x AS ( SELECT row_number() OVER () i, foo FROM UNNEST(:foo) foo ), y AS ( SELECT row_number() OVER () i, bar FROM UNNEST(:bar) bar ), z AS ( SELECT row_number() OVER () i, baz FROM UNNEST(:baz) baz ) SELECT * FROM x FULL JOIN y USING(i) FULL JOIN z USING(i); a i | foo | bar | baz ---+-----+-----------+------1 | 1 | Here | Here2 | 2 | is | is3 | 4 | some | yet4 | 8 | odd | more5 | | text | text6 | | of | 7 | | a | 8 | | different | 9 | | length | (9 rows) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fetter@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate