Thread: strange evaluation Window function and SRF functions?

strange evaluation Window function and SRF functions?

From
Pavel Stehule
Date:
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 /> 

Re: strange evaluation Window function and SRF functions?

From
Tom Lane
Date:
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


Re: strange evaluation Window function and SRF functions?

From
Thom Brown
Date:
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))

--
Thom

Re: strange evaluation Window function and SRF functions?

From
David Johnston
Date:
On Jul 30, 2012, at 12:33, Thom Brown <thom@linux.com> wrote:

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))


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

From
Pavel Stehule
Date:


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.

Regards

Pavel

 
--
Thom

Re: strange evaluation Window function and SRF functions?

From
Josh Berkus
Date:
> 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




Re: strange evaluation Window function and SRF functions?

From
Merlin Moncure
Date:
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


Re: strange evaluation Window function and SRF functions?

From
Tom Lane
Date:
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


Re: strange evaluation Window function and SRF functions?

From
Andrew Dunstan
Date:
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




Re: strange evaluation Window function and SRF functions?

From
Pavel Stehule
Date:


2012/7/30 Tom Lane <tgl@sss.pgh.pa.us>
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 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