Inconsistent query results after upgrading to Postgresql 8.4.0

Your name: Nicholas Jakobsen, Ryan Wallace
Your email address: nicholas(dot)jakobsen(at)telus(dot)net, rywall(at)gmail(dot)com

System Configuration:
---------------------
  Architecture (example: Intel Pentium): Intel Core 2 Duo

  Operating System (example: Linux 2.4.18): OS X 10.5 (Leopard)

  PostgreSQL version (example: PostgreSQL 8.3.4):  PostgreSQL 8.4.0

  Compiler 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.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------
We’ve provided a test database, screenshots of the queries, query
plans, and results. See files hosted here:
http://h4piaq.bay.livefilestore.com/y1pM63i57MAgIjkMWlm6yJOWguBlhNVSRAmvISpj2Z_btQYRJwhvj5JoeVMcT6_-iCd2IoO8k6rktB-UfldTOZd-szbYrYnjQmF/bug_report_files.zip?download.

Step 1. Overall query. This is the one that returns incorrect results.
See “Bugged Query.png”, “Bugged Query Plan.png”

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

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

SELECT descendant_id FROM item_type_descendants WHERE ancestor_id = 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 “Correct Result.png”,
“Correct Result Query Plan.png”

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