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

Inconsistant SQL results - Suspected error with query planing or query optimisation.

From: adam terrey <a(dot)terrey(at)mackillop(dot)acu(dot)edu(dot)au>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Inconsistant SQL results - Suspected error with query planing or query optimisation.
Date: 2007-05-22 08:45:34
Message-ID: OF0C2A0BDA.4907776C-ONCA2572E3.002AD50A-CA2572E3.00301D71@mackillop.acu.edu.au (view raw or flat)
Thread:
Lists: pgsql-bugspgsql-hackers
Hay all :)

Firstly, just wanted to say how much I appreceate all the hard work that 
has gorn into postgres. It really is a fine system.

Anyway, mybug: I have a test SELECT statement (Listing A - see "sql 
listings.txt") wich produces different results under two simular setups 
(Listing B and Listing C). Each setup should product the same result for 
the given SELECT statement.

In the first setup (Listing B) the table "items" has 10,000 records id'ed 
from 1 to 10,000.  The field "number" for every record except two records 
(id:500 and 600) has the value NULL., the two exceptions (id: 500 and 600) 
have the value 1.
This first setup produces the correct results for the given SELECT 
statement.

The SELECT statement is essentlually a dubble negitive - using two LEFT 
JOINS.
 - The most inner nested query selects WHERE number = 1
 -  Then next most inner selects everything but, and
 -  The most outer selects every thing but everything but WHERE number = 1

The second setup (Listing C) is identicle to the first execpt that the 
table "items" has an extra field and a primary key index. The goal of this 
setup is to produce a cirtian query plan that I beleive is broken, where 
it seems that the "Nested Loop Left Join" has forced the filter for "WHERE 
number = 1" outside or (perhaps after) a join one of the more nested joins 
causeing that  more nested join to cancel it self out. 

Well.... that's what I make of it.   I really hope you are able to 
reproduce this, it took me ages to find a setup that would be reproduce 
what I was observing in my developement system.

See the attached output.txt for the results and the EXPLAIN statement for 
the SELECT statement under each setup.

Postgres:
        "PostgreSQL 8.2.3 on x86_64-unknown-linux-gnu, compiled by GCC gcc 
(GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)"

Hardware:
        AMD Athlon(tm) 64 X2 Dual Core Processor 4400+
        2GB Mem

OS:
        Kernel:  Linux version 2.6.18-4-amd64 (Debian 2.6.18.dfsg.1-12)
        OS: GNU/Linux Debian "Etch" - AMD64




Kind regards,
Adam Terrey

Data Systems - Marketing Strategy and Planning 
University Relations Directorate
Australian Catholic University Limited (ABN 15 050 192 660)

42 Edward Street, North Sydney NSW 2060
PO Box 968 North Sydney NSW 2059
Telephone 02 9739 2517
Facsimile 02 9739 2960
Mobile    0421 430 824 
Email: adam(dot)terrey(at)acu(dot)edu(dot)au
http://www.acu.edu.au

Australian Catholic University and the courses offered by the University 
are 
registered on the Commonwealth Register of Institutions and Courses for 
Overseas Students (CRICOS). Provider registration codes: 00004G, 0012C, 

Attachment: output.txt
Description: text/plain (1.8 KB)
Attachment: sql listings.txt
Description: text/plain (5.4 KB)

Responses

pgsql-hackers by date

Next:From: Shachar ShemeshDate: 2007-05-22 14:14:54
Subject: Re: Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server
Previous:From: Martijn van OosterhoutDate: 2007-05-22 06:55:46
Subject: Re: Re: [Oledb-dev] double precision error with pg linux server, but not with windows pg server

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-05-22 13:57:49
Subject: Re: BUG #3296: CASCADING FKs dont DEFER
Previous:From: Max KhonDate: 2007-05-22 08:29:50
Subject: Re: ON DELETE CASCADE with multiple paths

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