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)"
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.terrey@acu.edu.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,