non-deterministic error related to MIN/MAX optimization

From: Jeff Davis <pgsql(at)j-davis(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: non-deterministic error related to MIN/MAX optimization
Date: 2008-08-26 01:50:24
Message-ID: 1219715424.6213.216.camel@dell.linuxdev.us.dell.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2008-08-26 02:26:51 Re: non-deterministic error related to MIN/MAX optimization
Previous Message Tom Lane 2008-08-26 00:04:45 Re: BUG #4375: pg_dump documentation, -Z option