Re: [HACKERS] strange evaluation Window function and SRF functions?
2012/7/30 Tom Lane > Josh Berkus 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 >
Re: [HACKERS] strange evaluation Window function and SRF functions?
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?
Josh Berkus 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?
On Mon, Jul 30, 2012 at 11:47 AM, Pavel Stehule wrote: > > > 2012/7/30 Thom Brown >> >> On 30 July 2012 17:19, Pavel Stehule 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?
> 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/7/30 Thom Brown > On 30 July 2012 17:19, Pavel Stehule 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?
On Jul 30, 2012, at 12:33, Thom Brown wrote: > On 30 July 2012 17:19, Pavel Stehule 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?
On 30 July 2012 17:19, Pavel Stehule 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?
Pavel Stehule 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
[HACKERS] strange evaluation Window function and SRF functions?
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 Regards Pavel
