BUG #7539: Result mismatch on Postgres 9.2.0

The following bug has been logged on the website:

Bug reference: 7539
Logged by: Yug
Email address: yugandharhere(at)gmail(dot)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