Your name: Nicholas Jakobsen, Ryan Wallace Your email address: nicholas.jakob...@telus.net, ryw...@gmail.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) -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
