BUG #7539: Result mismatch on Postgres 9.2.0
От | yugandharhere@gmail.com |
---|---|
Тема | BUG #7539: Result mismatch on Postgres 9.2.0 |
Дата | |
Msg-id | E1TCKwU-0004YR-GX@wrigleys.postgresql.org обсуждение исходный текст |
Ответы |
Re: BUG #7539: Result mismatch on Postgres 9.2.0
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-bugs |
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
В списке pgsql-bugs по дате отправления: