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

adam terrey wrote:

> 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.

It's easy to confirm that the nested loop is the culprit here: if you
SET enable_nestloop to off, the query returns different results (the
expected two tuples).

8.1 seems to work OK, but both 8.2 and HEAD don't.

alvherre=# set enable_nestloop to off;
SET
alvherre=# SELECT items.id
FROM items
LEFT JOIN (
-- Query i.
SELECT items.id
FROM items
LEFT JOIN (
-- Query ii.
SELECT id FROM items WHERE number = 1
) AS moded_items USING (id)
WHERE moded_items.id IS NULL
) AS sub_items USING (id)
WHERE sub_items.id IS NULL;
id
-----
500
600
(2 rows)

alvherre=# set enable_nestloop to on;
SET
alvherre=# SELECT items.id
FROM items
LEFT JOIN (
-- Query i.
SELECT items.id
FROM items
LEFT JOIN (
-- Query ii.
SELECT id FROM items WHERE number = 1
) AS moded_items USING (id)
WHERE moded_items.id IS NULL
) AS sub_items USING (id)
WHERE sub_items.id IS NULL;
id
----
(0 rows)

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.