Thread: strange evaluation Window function and SRF functions?

Hello<br /><br />I seen nice trick based on window function <a
href="http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql">http://stackoverflow.com/questions/11700930/how-can-i-trim-a-text-array-in-postgresql</a><br
/><br/>but isn't it example of wrong evaluation? Result of row_number is not correct<br /><br />Regards<br /><br
/>Pavel<br /> 

Pavel Stehule <pavel.stehule@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

On 30 July 2012 17:19, Pavel Stehule <pavel.stehule@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.

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

On Mon, Jul 30, 2012 at 11:47 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
>
>
> 2012/7/30 Thom Brown <thom@linux.com>
>>
>> On 30 July 2012 17:19, Pavel Stehule <pavel.stehule@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

Josh Berkus <josh@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

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