The following bug has been logged on the website: Bug reference: 7539 Logged by: Yug Email address: yugandharh...@gmail.com PostgreSQL version: 9.2.0 Operating system: (Red Hat 4.1.2-52), 64-bit Description:
Hello, I am seeing a mismatch in the results returned by a select statement on Postgres 9.2.0. What I am seeing is a select statement with an additional restriction is returning results which are not part of the select statement without that additional restriction. Below, I am pasting the version, select statement, select statement with an additional restriction on the previous, the explanation of the first select statement, the explanation of the second select statement, and the schema of the tables in use. =# select version(); version --------------------------------------------------------------------------------------------------------------- PostgreSQL 9.2.0 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red Hat 4.1.2-52), 64-bit (1 row) First select statement and result =# SELECT pl.id, pl.site_id, pl.placement_id, pl.layout_id, pl.item_count, display_count, pl.column_count, pl.enabled, pl.campaign_id, ov.revision, ov.site_id, ov.canonical_id, ov.object_type, ov.object_id, ov.start_version, ov.end_version FROM html.placement_layouts pl, management.object_versions ov, (SELECT canonical_id, MIN(end_version) AS end_version FROM management.object_versions WHERE site_id=150 AND object_type=27 AND end_version IN (-1,2147483647) GROUP BY canonical_id) AS latest WHERE pl.site_id=150 AND pl.layout_id=ov.canonical_id AND ov.site_id=150 AND ov.object_type=27 AND ov.canonical_id=latest.canonical_id AND ov.end_version=latest.end_version AND pl.placement_id=16045 order by pl.placement_id desc; id | site_id | placement_id | layout_id | item_count | display_count | column_count | enabled | campaign_id | revision | site_id | canonical_id | object_type | object_id | start_version | end_version --------+---------+--------------+-----------+------------+---------------+--------------+---------+-------------+----------+---------+--------------+-------------+-----------+---------------+------------- 144829 | 150 | 16045 | 2468 | 3 | -1 | -1 | t | 2725 | 167933 | 150 | 2468 | 27 | 11590 | 1719 | 2147483647 144836 | 150 | 16045 | 2469 | 3 | -1 | -1 | t | 2729 | 167934 | 150 | 2469 | 27 | 11591 | 1720 | 2147483647 144835 | 150 | 16045 | 2470 | 3 | -1 | -1 | t | 2724 | 167935 | 150 | 2470 | 27 | 11592 | 1720 | 2147483647 144831 | 150 | 16045 | 2472 | 3 | -1 | -1 | t | 2726 | 167937 | 150 | 2472 | 27 | 11594 | 1720 | 2147483647 144832 | 150 | 16045 | 2473 | 3 | -1 | -1 | t | 2727 | 167938 | 150 | 2473 | 27 | 11595 | 1720 | 2147483647 144834 | 150 | 16045 | 2474 | 3 | -1 | -1 | t | 2728 | 167939 | 150 | 2474 | 27 | 11596 | 1720 | 2147483647 144833 | 150 | 16045 | 2491 | 3 | -1 | -1 | t | 3020 | 169233 | 150 | 2491 | 27 | 11721 | 1756 | 2147483647 144830 | 150 | 16045 | 2492 | 3 | -1 | -1 | t | 3021 | 169268 | 150 | 2492 | 27 | 11724 | 1761 | 2147483647 (8 rows) Second select statement and result =# SELECT pl.id, pl.site_id, pl.placement_id, pl.layout_id, pl.item_count, display_count, pl.column_count, pl.enabled, pl.campaign_id, ov.revision, ov.site_id, ov.canonical_id, ov.object_type, ov.object_id, ov.start_version, ov.end_version FROM html.placement_layouts pl, management.object_versions ov, (SELECT canonical_id, MIN(end_version) AS end_version FROM management.object_versions WHERE site_id=150 AND object_type=27 AND end_version IN (-1,2147483647) GROUP BY canonical_id) AS latest WHERE pl.site_id=150 AND pl.layout_id=ov.canonical_id AND ov.site_id=150 AND ov.object_type=27 AND ov.canonical_id=latest.canonical_id AND ov.end_version=latest.end_version AND pl.placement_id=16045 AND latest.canonical_id=2202 order by pl.placement_id desc; id | site_id | placement_id | layout_id | item_count | display_count | column_count | enabled | campaign_id | revision | site_id | canonical_id | object_type | object_id | start_version | end_version --------+---------+--------------+-----------+------------+---------------+--------------+---------+-------------+----------+---------+--------------+-------------+-----------+---------------+------------- 144841 | 150 | 16045 | 2202 | 1 | -1 | -1 | t | 3006 | 132831 | 150 | 2202 | 27 | 9990 | 1372 | 2147483647 144839 | 150 | 16045 | 2202 | 4 | -1 | -1 | t | 3008 | 132831 | 150 | 2202 | 27 | 9990 | 1372 | 2147483647 144840 | 150 | 16045 | 2202 | 4 | -1 | -1 | t | 3012 | 132831 | 150 | 2202 | 27 | 9990 | 1372 | 2147483647 Explanation of first select statement QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=43.12..528.46 rows=1 width=65) Join Filter: (pl.layout_id = ov.canonical_id) -> Merge Join (cost=43.12..522.24 rows=1 width=41) Merge Cond: (object_versions.canonical_id = pl.layout_id) -> GroupAggregate (cost=0.00..477.98 rows=82 width=8) -> Index Only Scan using object_versions_site_id_object_type_canonical_id_end_version on object_versions (cost=0.00..476.12 rows=208 width=8) Index Cond: ((site_id = 150) AND (object_type = 27) AND (end_version = ANY ('{-1,2147483647}'::integer[]))) -> Sort (cost=43.12..43.17 rows=21 width=33) Sort Key: pl.layout_id -> Index Scan using placement_layouts_placement_id_campaign_id_layout_id_idx on placement_layouts pl (cost=0.00..42.66 rows=21 width=33) Index Cond: (placement_id = 16045) Filter: (site_id = 150) -> Index Scan using object_versions_site_id_object_type_canonical_id_end_version on object_versions ov (cost=0.00..6.21 rows=1 width=32) Index Cond: ((site_id = 150) AND (object_type = 27) AND (canonical_id = object_versions.canonical_id)) Filter: ((min(object_versions.end_version)) = end_version) (15 rows) Explanation of second select statement QUERY PLAN ----------------------------------------------------------------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..22.92 rows=1 width=65) Join Filter: (ov.end_version = (min(object_versions.end_version))) -> Nested Loop (cost=0.00..13.16 rows=1 width=65) -> Index Scan using object_versions_site_id_object_type_canonical_id_end_version on object_versions ov (cost=0.00..6.46 rows=1 width=32) Index Cond: ((site_id = 150) AND (object_type = 27) AND (canonical_id = 2202)) -> Index Scan using placement_layouts_placement_id_campaign_id_layout_id_idx on placement_layouts pl (cost=0.00..6.69 rows=1 width=33) Index Cond: ((placement_id = 16045) AND (layout_id = 2202)) Filter: (site_id = 150) -> GroupAggregate (cost=0.00..9.74 rows=1 width=8) -> Index Only Scan using object_versions_site_id_object_type_canonical_id_end_version on object_versions (cost=0.00..9.73 rows=1 width=8) Index Cond: ((site_id = 150) AND (object_type = 27) AND (canonical_id = 2202) AND (end_version = ANY ('{-1,2147483647}'::integer[]))) (11 rows) Description of html.placement_layouts table =# \d html.placement_layouts; Table "html.placement_layouts" Column | Type | Modifiers ------------------+---------+--------------------------------------------------------------------- id | integer | not null default nextval('html.placement_layouts_id_seq'::regclass) site_id | integer | not null placement_id | integer | not null layout_id | integer | not null item_count | integer | not null display_count | integer | not null default (-1) column_count | integer | not null default (-1) enabled | boolean | default false campaign_id | integer | default (-1) creative_line_id | integer | not null default (-1) min_item_count | integer | default (-1) use_alt_message | boolean | not null default false Indexes: "placement_layouts_pkey" PRIMARY KEY, btree (id) "placement_layouts_placement_id_campaign_id_layout_id_idx" UNIQUE, btree (placement_id, campaign_id, layout_id) Description of management.object_versions =# \d management.object_versions Table "management.object_versions" Column | Type | Modifiers ------------------+-----------------------------+------------------------------------------------------------------------------- revision | bigint | not null default nextval('management.object_versions_revision_seq'::regclass) site_id | integer | not null canonical_id | integer | not null object_type | integer | not null object_id | integer | not null start_version | integer | not null default (-1) end_version | integer | not null default (-1) production_ready | boolean | not null default false created | timestamp without time zone | created_by | integer | delta_type | integer | not null Indexes: "object_versions_site_id_object_type_canonical_id_end_version" UNIQUE, btree (site_id, object_type, canonical_id, end_version) The second a select statement is just has an additional restriction from the first one, and it is returning a result set which is not a subset of the result set returned by the first select statement. Any help would be much appreciated. Thanks, Yug -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs