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.