non-deterministic error related to MIN/MAX optimization

Jeff Davis <pgsql@j-davis.com> writes:

=> select max(a), generate_series(1,2) as g from foo order by g desc;
ERROR: set-valued function called in context that cannot accept a set

This strikes me as a pretty useless query, so the fact that it doesn't
work doesn't bother me. It's mostly accidental that there are any
variants that do work, I think. Why would you want a SRF in a sort key?

regards, tom lane

On Mon, 2008-08-25 at 22:26 -0400, Tom Lane wrote:

Jeff Davis <pgsql@j-davis.com> writes:

=> select max(a), generate_series(1,2) as g from foo order by g desc;
ERROR: set-valued function called in context that cannot accept a set

This strikes me as a pretty useless query, so the fact that it doesn't
work doesn't bother me. It's mostly accidental that there are any
variants that do work, I think. Why would you want a SRF in a sort key?

The following line was added to the regression tests:

aggregates.sql:226:
select max(unique2), generate_series(1,3) as g from tenk1 order by g
desc;

I have no argument with what you say above. But one of my colleagues at
Truviso was doing some experiments, and it was causing a regression
failure here. I should have been more clear.

So if it truly is a useless query, shouldn't we at least remove the
regression test?

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

On Mon, 2008-08-25 at 22:26 -0400, Tom Lane wrote:

... It's mostly accidental that there are any
variants that do work, I think. Why would you want a SRF in a sort key?

The following line was added to the regression tests:
aggregates.sql:226:
select max(unique2), generate_series(1,3) as g from tenk1 order by g desc;

Hmm ... by me, even, according to the CVS logs :-)

I have no argument with what you say above. But one of my colleagues at
Truviso was doing some experiments, and it was causing a regression
failure here. I should have been more clear.

Please provide some more detail about those experiments. The test case
hasn't been seen to fail in the buildfarm, AFAIR.

regards, tom lane

On Tue, 2008-08-26 at 01:04 -0400, Tom Lane wrote:

Please provide some more detail about those experiments. The test case
hasn't been seen to fail in the buildfarm, AFAIR.

Dan Farina, my colleague at Truviso, was experimenting with some query
transformations that pushed the range table entries down into a
subquery.

You can see the effect here:

=> select max(a), generate_series(1,2) as g from foo;
max | g
-----+---
| 1
| 2
(2 rows)

=> -- make "foo" into a subquery and add a no-op
=> -- to prevent it from pulling up the subquery
=> select max(a), generate_series(1,2) as g from (select a as a from foo
offset 0) dummy;
ERROR: set-valued function called in context that cannot accept a set

So, although Dan's transformations were semantically correct, they ended
up causing this regression failure.

It doesn't have anything to do with the ORDER BY, so that part of my
example was unnecessary.

Regards,
Jeff Davis

Jeff Davis <pgsql@j-davis.com> writes:

=> -- make "foo" into a subquery and add a no-op
=> -- to prevent it from pulling up the subquery
=> select max(a), generate_series(1,2) as g from (select a as a from foo
offset 0) dummy;
ERROR: set-valued function called in context that cannot accept a set

So, although Dan's transformations were semantically correct, they ended
up causing this regression failure.

It doesn't have anything to do with the ORDER BY, so that part of my
example was unnecessary.

Hmm ... after a bit of poking at it, the reason it's failing is that Agg
plan nodes don't support SRFs in their targetlists. (Group nodes don't
either.) Kind of interesting that no one ever complained about that
before ... although given that plpgsql SRFs don't work in targetlists
anyway, maybe it's been masked for common uses.

I'm not entirely sure if we should add SRF support to Agg/Group or just
write it off as being a deprecated feature anyhow. Given the
definitional issues involved with multiple SRFs in the same targetlist,
putting more effort into the feature doesn't seem like a great
investment of time.

regards, tom lane

2008/8/26 Tom Lane <tgl@sss.pgh.pa.us>:

Jeff Davis <pgsql@j-davis.com> writes:

=> -- make "foo" into a subquery and add a no-op
=> -- to prevent it from pulling up the subquery
=> select max(a), generate_series(1,2) as g from (select a as a from foo
offset 0) dummy;
ERROR: set-valued function called in context that cannot accept a set

So, although Dan's transformations were semantically correct, they ended
up causing this regression failure.

It doesn't have anything to do with the ORDER BY, so that part of my
example was unnecessary.

Hmm ... after a bit of poking at it, the reason it's failing is that Agg
plan nodes don't support SRFs in their targetlists. (Group nodes don't
either.) Kind of interesting that no one ever complained about that
before ... although given that plpgsql SRFs don't work in targetlists
anyway, maybe it's been masked for common uses.

I'm not entirely sure if we should add SRF support to Agg/Group or just
write it off as being a deprecated feature anyhow. Given the
definitional issues involved with multiple SRFs in the same targetlist,
putting more effort into the feature doesn't seem like a great
investment of time.

I dislike this feature - sometime we can do nice hack with it, but
it's very dificult readable.

regards
Pavel Stehule

[ back to this issue ]

"Pavel Stehule" <pavel.stehule@gmail.com> writes:

2008/8/26 Tom Lane <tgl@sss.pgh.pa.us>:

Jeff Davis <pgsql@j-davis.com> writes:

ERROR: set-valued function called in context that cannot accept a set

Hmm ... after a bit of poking at it, the reason it's failing is that Agg
plan nodes don't support SRFs in their targetlists. (Group nodes don't
either.) Kind of interesting that no one ever complained about that
before ... although given that plpgsql SRFs don't work in targetlists
anyway, maybe it's been masked for common uses.

I'm not entirely sure if we should add SRF support to Agg/Group or just
write it off as being a deprecated feature anyhow. Given the
definitional issues involved with multiple SRFs in the same targetlist,
putting more effort into the feature doesn't seem like a great
investment of time.

I dislike this feature - sometime we can do nice hack with it, but
it's very dificult readable.

I think the plan has been to implement SQL's LATERAL feature and then
deprecate SRFs-in-targetlist. However, I don't see anyone working on
LATERAL for 8.4, and even if it did happen for 8.4, it would be a long
time after that before we could consider removing SRFs-in-targetlist
support altogether. (Besides, is LATERAL really so much more readable?)
In the meantime we have a bug or at least a functionality gap here.

So I'm thinking that we ought to fix nodeAgg and nodeGroup to support
this. It doesn't look like it will really take much extra code.

regards, tom lane