non-deterministic error related to MIN/MAX optimization - Mailing list pgsql-bugs

From Jeff Davis
Subject non-deterministic error related to MIN/MAX optimization
Date
Msg-id 1219715424.6213.216.camel@dell.linuxdev.us.dell.com
Whole thread Raw
Responses Re: non-deterministic error related to MIN/MAX optimization
List 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

pgsql-bugs by date:

Previous
From: Tom Lane
Date:
Subject: Re: BUG #4375: pg_dump documentation, -Z option
Next
From: Tom Lane
Date:
Subject: Re: non-deterministic error related to MIN/MAX optimization