Skip site navigation (1) Skip section navigation (2)

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

From: David Schmitt <david(at)dasz(dot)at>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #5673: Optimizer creates strange execution plan leading to wrong results
Date: 2010-09-23 16:22:11
Message-ID: 4C9B7EB3.5000501@dasz.at (view raw or flat)
Thread:
Lists: pgsql-bugs
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

Attachment: example.sql
Description: text/plain (12.9 KB)

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2010-09-23 16:31:33
Subject: Re: libpq: system-wide root.crt
Previous:From: Carl HollidayDate: 2010-09-23 15:50:29
Subject: Feature request - pg_dump - -W specify pwd in command line

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group