Inconsistent query results after upgrading to Postgresql 8.4.0

Your name: Nicholas Jakobsen, Ryan Wallace
Your email address: nicholas.jakobsen@telus.net, rywall@gmail.com

System Configuration:
---------------------
=A0=A0Architecture (example: Intel Pentium): Intel Core 2 Duo

=A0=A0Operating System (example: Linux 2.4.18): OS X 10.5 (Leopard)

=A0=A0PostgreSQL version (example: PostgreSQL 8.3.4): =A0PostgreSQL 8.4.0

=A0=A0Compiler used (example: gcc 3.3.5): Macports

Please enter a FULL description of your problem:
------------------------------------------------
Query returns incorrect results when executed on PostgreSQL 8.4.0. The
same query was executed successfully on PostgreSQL 8.3.7. The problem
seems to be caused by a specific execution plan. Our query consists of
an outer loop, with an inner sub query. When we replace the sub query
with its calculated result, the overall query results in a different
execution plan, and correct results.

Please describe a way to repeat the problem. =A0=A0Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
We=92ve provided a test database, screenshots of the queries, query
plans, and results. See files hosted here:
http://h4piaq.bay.livefilestore.com/y1pM63i57MAgIjkMWlm6yJOWguBlhNVSRAmvISp=
j2Z_btQYRJwhvj5JoeVMcT6_-iCd2IoO8k6rktB-UfldTOZd-szbYrYnjQmF/bug_report_fil=
es.zip?download.

Step 1. Overall query. This is the one that returns incorrect results.
See =93Bugged Query.png=94, =93Bugged Query Plan.png=94

SELECT items.id FROM items, item_item_types, item_types WHERE items.id
=3D item_item_types.item_id AND item_item_types.item_type_id =3D
item_types.id AND item_types.id IN (SELECT descendant_id FROM
item_type_descendants WHERE ancestor_id =3D 8)

Step 2. Determine the result of the IN (SELECT) sub query (shown
below). In case, t he result is the integer 8. See =93Interim
Query.png=94, =93Interim Query Plan.png=94

SELECT descendant_id FROM item_type_descendants WHERE ancestor_id =3D 8

Step 3. Perform the original query with the IN (SELECT) sub query
replaced by the result from Step 2. This will result in the correct
results being returned. This will also cause the execution planner to
use a different plan than in Step 1. See =93Correct Result.png=94,
=93Correct Result Query Plan.png=94

SELECT items.id FROM items, item_item_types, item_types WHERE items.id
=3D item_item_types.item_id AND item_item_types.item_type_id =3D
item_types.id AND item_types.id IN (8)