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(at)dasz(dot)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(at)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