Thread: non-deterministic error related to MIN/MAX optimization
This problem exists in 8.3.3:
=> create table foo(a int);
CREATE TABLE
=> create index foo_a_idx on foo(a);
CREATE INDEX
=> select max(a), generate_series(1,2) as g from foo order by g desc;
max | g
-----+---
| 2
| 1
(2 rows)
=> explain select max(a), generate_series(1,2) as g from foo order by g
desc;
QUERY
PLAN
------------------------------------------------------------------------------------------------
Sort (cost=0.06..0.06 rows=1 width=0)
Sort Key: (generate_series(1, 2))
InitPlan
-> Limit (cost=0.00..0.03 rows=1 width=4)
-> Index Scan Backward using foo_a_idx on foo
(cost=0.00..80.25 rows=2400 width=4)
Filter: (a IS NOT NULL)
-> Result (cost=0.00..0.01 rows=1 width=0)
(7 rows)
=> set enable_indexscan=f;
SET
=> 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
=> explain select max(a), generate_series(1,2) as g from foo order by g
desc;
QUERY PLAN
-------------------------------------------------------------------
Sort (cost=40.02..40.03 rows=1 width=4)
Sort Key: (generate_series(1, 2))
-> Aggregate (cost=40.00..40.02 rows=1 width=4)
-> Seq Scan on foo (cost=0.00..34.00 rows=2400 width=4)
I believe this is related to this commit:
Date: Mon Mar 31 16:59:33 2008 +0000
Apply my original fix for Taiki Yamaguchi's bug report about
DISTINCT MAX().
Add some regression tests for plausible failures in this area.
However, that commit actually added a test case, which confuses me. I'm
not really sure what the behavior is supposed to be, but the output
shouldn't depend on the optimizer.
Regards,
Jeff Davis
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 > > regards, tom lane > > -- > Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-bugs >
[ 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