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@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. =


=3D# 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
=3D# 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=3D150 AND object_type=3D27 AND
end_version IN (-1,2147483647) GROUP BY canonical_id) AS latest =

WHERE pl.site_id=3D150 AND pl.layout_id=3Dov.canonical_id AND ov.site_id=3D=
150 AND
ov.object_type=3D27 AND ov.canonical_id=3Dlatest.canonical_id AND
ov.end_version=3Dlatest.end_version AND pl.placement_id=3D16045 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
=3D# 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=3D150 AND object_type=3D27 AND
end_version IN (-1,2147483647) GROUP BY canonical_id) AS latest =

WHERE pl.site_id=3D150 AND pl.layout_id=3Dov.canonical_id AND ov.site_id=3D=
150 AND
ov.object_type=3D27 AND ov.canonical_id=3Dlatest.canonical_id AND
ov.end_version=3Dlatest.end_version AND pl.placement_id=3D16045 AND
latest.canonical_id=3D2202 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=3D43.12..528.46 rows=3D1 width=3D65)
   Join Filter: (pl.layout_id =3D ov.canonical_id)
   ->  Merge Join  (cost=3D43.12..522.24 rows=3D1 width=3D41)
         Merge Cond: (object_versions.canonical_id =3D pl.layout_id)
         ->  GroupAggregate  (cost=3D0.00..477.98 rows=3D82 width=3D8)
               ->  Index Only Scan using
object_versions_site_id_object_type_canonical_id_end_version on
object_versions  (cost=3D0.00..476.12 rows=3D208 width=3D8)
                     Index Cond: ((site_id =3D 150) AND (object_type =3D 27=
) AND
(end_version =3D ANY ('{-1,2147483647}'::integer[])))
         ->  Sort  (cost=3D43.12..43.17 rows=3D21 width=3D33)
               Sort Key: pl.layout_id
               ->  Index Scan using
placement_layouts_placement_id_campaign_id_layout_id_idx on
placement_layouts pl  (cost=3D0.00..42.66 rows=3D21 width=3D33)
                     Index Cond: (placement_id =3D 16045)
                     Filter: (site_id =3D 150)
   ->  Index Scan using
object_versions_site_id_object_type_canonical_id_end_version on
object_versions ov  (cost=3D0.00..6.21 rows=3D1 width=3D32)
         Index Cond: ((site_id =3D 150) AND (object_type =3D 27) AND
(canonical_id =3D object_versions.canonical_id))
         Filter: ((min(object_versions.end_version)) =3D end_version)
(15 rows)

Explanation of second select statement
QUERY PLAN                                                                  =

   =

---------------------------------------------------------------------------=
--------------------------------------------------------------------------
 Nested Loop  (cost=3D0.00..22.92 rows=3D1 width=3D65)
   Join Filter: (ov.end_version =3D (min(object_versions.end_version)))
   ->  Nested Loop  (cost=3D0.00..13.16 rows=3D1 width=3D65)
         ->  Index Scan using
object_versions_site_id_object_type_canonical_id_end_version on
object_versions ov  (cost=3D0.00..6.46 rows=3D1 width=3D32)
               Index Cond: ((site_id =3D 150) AND (object_type =3D 27) AND
(canonical_id =3D 2202))
         ->  Index Scan using
placement_layouts_placement_id_campaign_id_layout_id_idx on
placement_layouts pl  (cost=3D0.00..6.69 rows=3D1 width=3D33)
               Index Cond: ((placement_id =3D 16045) AND (layout_id =3D 220=
2))
               Filter: (site_id =3D 150)
   ->  GroupAggregate  (cost=3D0.00..9.74 rows=3D1 width=3D8)
         ->  Index Only Scan using
object_versions_site_id_object_type_canonical_id_end_version on
object_versions  (cost=3D0.00..9.73 rows=3D1 width=3D8)
               Index Cond: ((site_id =3D 150) AND (object_type =3D 27) AND
(canonical_id =3D 2202) AND (end_version =3D ANY
('{-1,2147483647}'::integer[])))
(11 rows)

Description of html.placement_layouts table
=3D# \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
=3D# \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