Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread Tom Lane
Pavel Stehule pavel.steh...@gmail.com writes:
 I seen nice trick based on window function
 http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql

 but isn't it example of wrong evaluation? Result of row_number is not
 correct

Sure it is ... or at least, you won't find anything in the SQL spec that
says it isn't.  The result of a window function is only dependent on the
state of the input, not on SRFs that might happen to be in sibling
SELECT expressions.  (This is one example of why SRFs in SELECT lists
aren't terribly well defined.)

A bigger problem with that query is that there's no guarantee it will
preserve ordering of the elements of the arrays.

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


Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread Thom Brown
On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote:

 Hello

 I seen nice trick based on window function
 http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql

 but isn't it example of wrong evaluation? Result of row_number is not
 correct


Looks right to me.  I guess the way to get the row_number they're after out
of the result set would involve changing OVER () to OVER (ORDER BY
unnest(myTextArrayColumn))

-- 
Thom


Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread David Johnston
On Jul 30, 2012, at 12:33, Thom Brown t...@linux.com wrote:

 On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote:
 Hello
 
 I seen nice trick based on window function 
 http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql
 
 but isn't it example of wrong evaluation? Result of row_number is not correct
 
 Looks right to me.  I guess the way to get the row_number they're after out 
 of the result set would involve changing OVER () to OVER (ORDER BY 
 unnest(myTextArrayColumn))
 

The better way would be to perform the unnest in a sub-select then attach the 
row number in the outer select.

David J.

Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread Pavel Stehule
2012/7/30 Thom Brown t...@linux.com

 On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote:

 Hello

 I seen nice trick based on window function
 http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql

 but isn't it example of wrong evaluation? Result of row_number is not
 correct


 Looks right to me.  I guess the way to get the row_number they're after
 out of the result set would involve changing OVER () to OVER (ORDER BY
 unnest(myTextArrayColumn))


it looks like row_number is evaluated before SRF - this behave is
absolutely undefined - for me - more native behave is different evaluation.

Regards

Pavel



 --
 Thom



Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread Josh Berkus

 it looks like row_number is evaluated before SRF - this behave is
 absolutely undefined - for me - more native behave is different evaluation.

SRFs which return multiple rows in the SELECT clause have ALWAYS behaved
oddly when it comes to row evaluation (LIMIT, COUNT(), etc.).   This
isn't necessarily desireable, but it is consistent with past releases,
and it's not in any way limited to Windowing functions.  In general, if
you care about rows when calling such an SRF, you need to subselect it.

It would be nice to clean that up, but you'd have to start with a
comprehensive definition of what the behavior *should* be in all common
cases.  And then you'd be in for a big code overhaul.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.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] strange evaluation Window function and SRF functions?

2012-07-30 Thread Merlin Moncure
On Mon, Jul 30, 2012 at 11:47 AM, Pavel Stehule pavel.steh...@gmail.com wrote:


 2012/7/30 Thom Brown t...@linux.com

 On 30 July 2012 17:19, Pavel Stehule pavel.steh...@gmail.com wrote:

 Hello

 I seen nice trick based on window function
 http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql

 but isn't it example of wrong evaluation? Result of row_number is not
 correct


 Looks right to me.  I guess the way to get the row_number they're after
 out of the result set would involve changing OVER () to OVER (ORDER BY
 unnest(myTextArrayColumn))


 it looks like row_number is evaluated before SRF - this behave is absolutely
 undefined - for me - more native behave is different evaluation.

If it was me, I'd have expanded the array with generate_series (as
with the undocumented information_schema._pg_expandarray) and stacked
the array with array() not array_agg().

merlin

-- 
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] strange evaluation Window function and SRF functions?

2012-07-30 Thread Tom Lane
Josh Berkus j...@agliodbs.com writes:
 it looks like row_number is evaluated before SRF - this behave is
 absolutely undefined - for me - more native behave is different evaluation.

 SRFs which return multiple rows in the SELECT clause have ALWAYS behaved
 oddly when it comes to row evaluation (LIMIT, COUNT(), etc.).   This
 isn't necessarily desireable, but it is consistent with past releases,
 and it's not in any way limited to Windowing functions.  In general, if
 you care about rows when calling such an SRF, you need to subselect it.

 It would be nice to clean that up, but you'd have to start with a
 comprehensive definition of what the behavior *should* be in all common
 cases.  And then you'd be in for a big code overhaul.

And a lot of application code breakage, if you change the semantics at all.

My feeling is that SRFs in targetlists are just fundamentally poorly
defined, and the answer is to avoid them not try to make them cleaner.
Most of the real use-cases for them could be handled in a
better-defined, more standard way with LATERAL ... so what we ought
to be spending time on is getting LATERAL done, not worrying about
putting lipstick on tlist SRFs.

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


Re: [HACKERS] strange evaluation Window function and SRF functions?

2012-07-30 Thread Andrew Dunstan


On 07/30/2012 01:18 PM, Tom Lane wrote:



My feeling is that SRFs in targetlists are just fundamentally poorly
defined, and the answer is to avoid them not try to make them cleaner.
Most of the real use-cases for them could be handled in a
better-defined, more standard way with LATERAL ... so what we ought
to be spending time on is getting LATERAL done, not worrying about
putting lipstick on tlist SRFs.





+1

LATERAL would be useful for all sorts of reasons anyway.

cheers

andrew



--
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] strange evaluation Window function and SRF functions?

2012-07-30 Thread Pavel Stehule
2012/7/30 Tom Lane t...@sss.pgh.pa.us

 Josh Berkus j...@agliodbs.com writes:
  it looks like row_number is evaluated before SRF - this behave is
  absolutely undefined - for me - more native behave is different
 evaluation.

  SRFs which return multiple rows in the SELECT clause have ALWAYS behaved
  oddly when it comes to row evaluation (LIMIT, COUNT(), etc.).   This
  isn't necessarily desireable, but it is consistent with past releases,
  and it's not in any way limited to Windowing functions.  In general, if
  you care about rows when calling such an SRF, you need to subselect it.

  It would be nice to clean that up, but you'd have to start with a
  comprehensive definition of what the behavior *should* be in all common
  cases.  And then you'd be in for a big code overhaul.

 And a lot of application code breakage, if you change the semantics at all.

 My feeling is that SRFs in targetlists are just fundamentally poorly
 defined, and the answer is to avoid them not try to make them cleaner.
 Most of the real use-cases for tihem could be handled in a
 better-defined, more standard way with LATERAL ... so what we ought
 to be spending time on is getting LATERAL done, not worrying about
 putting lipstick on tlist SRFs.


I don't propose any changes - I would to show interesting/strange usage of
SRF - this is a new use case of old issue - and I agree so we need LATERAL
more and early.

Regards

Pavel


 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