strange evaluation Window function and SRF functions?

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

--
Thom

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.

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

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

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