Re: BUG #5673: Optimizer creates strange execution plan leading to wrong results

On 9/23/2010 5:33 PM, Tom Lane wrote:
> "David Schmitt"<david@dasz.at>  writes:
>> Description:        Optimizer creates strange execution plan leading to
>> wrong results
>
> Please supply a self-contained example demonstrating the incorrect results.
> The information you've provided is completely inadequate for
> investigating this problem report.  A SQL script that creates some test
> tables and executes a query that gives wrong answers would be helpful.


Executing the attached example.sql on a fresh database demonstrates the
problem I'm seeing:

> postgres@db:~$ dropdb test; createdb test; psql test < example.sql
[load dump]
[execute query]
>  ID |         CreatedOn          |         ChangedOn          |              ExportGuid              | fk_CreatedBy |
fk_ChangedBy| fk_Property | ID |         CreatedOn          |         ChangedOn          |              ExportGuid
       | fk_CreatedBy | fk_ChangedBy | fk_Property | C1 | C2 | C3 | C4 | fk_EnumVal 
>
----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----+----+----+------------
>  60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 |              |
            |         783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8|              |              |         783 | 60 | f  | t  | f  |          3 
>  60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 |              |
            |         783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8|              |              |         783 | 60 | f  | t  | f  |          3 
> (2 rows)
>
[remove pk]
[execute query again]
> ALTER TABLE
>  ID |         CreatedOn          |         ChangedOn          |              ExportGuid              | fk_CreatedBy |
fk_ChangedBy| fk_Property | ID |         CreatedOn          |         ChangedOn          |              ExportGuid
       | fk_CreatedBy | fk_ChangedBy | fk_Property | C1 | C2 | C3 | C4 | fk_EnumVal 
>
----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----+----+----+------------
>  60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 | ecbd6901-13e8-4beb-a6a2-0548eba345a8 |              |
            |         783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8|              |              |         783 | 60 | f  | t  | f  |          3 
> (1 row)


Hopefully, this helps you debugging it! Thanks for your time and work on
the otherwise really great PostgreSQL.


Best Regards, David
--
dasz.at OG              Tel: +43 (0)664 2602670     Web: http://dasz.at
Klosterneuburg                                         UID: ATU64260999

        FB-Nr.: FN 309285 g          FB-Gericht: LG Korneuburg