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
