On 9/23/2010 5:33 PM, Tom Lane wrote:
"David Schmitt"<da...@dasz.at> writes:
Description: Optimizer creates strange execution plan leading to
wrong results
Please supply a self-contained example demonstrating the incorrect results.
The information you've provided is completely inadequate for
investigating this problem report. A SQL script that creates some test
tables and executes a query that gives wrong answers would be helpful.
Executing the attached example.sql on a fresh database demonstrates the
problem I'm seeing:
postg...@db:~$ dropdb test; createdb test; psql test < example.sql
[load dump]
[execute query]
ID | CreatedOn | ChangedOn |
ExportGuid | fk_CreatedBy | fk_ChangedBy | fk_Property | ID |
CreatedOn | ChangedOn | ExportGuid
| fk_CreatedBy | fk_ChangedBy | fk_Property | C1 | C2 | C3 | C4 |
fk_EnumVal
----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----+----+----+------------
60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | |
783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | |
783 | 60 | f | t | f | 3
60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | |
783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | |
783 | 60 | f | t | f | 3
(2 rows)
[remove pk]
[execute query again]
ALTER TABLE
ID | CreatedOn | ChangedOn |
ExportGuid | fk_CreatedBy | fk_ChangedBy | fk_Property | ID |
CreatedOn | ChangedOn | ExportGuid
| fk_CreatedBy | fk_ChangedBy | fk_Property | C1 | C2 | C3 | C4 |
fk_EnumVal
----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----------------------------+----------------------------+--------------------------------------+--------------+--------------+-------------+----+----+----+----+------------
60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | |
783 | 60 | 2010-09-20 13:22:17.773308 | 2010-09-20 13:22:17.773308 |
ecbd6901-13e8-4beb-a6a2-0548eba345a8 | | |
783 | 60 | f | t | f | 3
(1 row)
Hopefully, this helps you debugging it! Thanks for your time and work on
the otherwise really great PostgreSQL.
Best Regards, David
--
dasz.at OG Tel: +43 (0)664 2602670 Web: http://dasz.at
Klosterneuburg UID: ATU64260999
FB-Nr.: FN 309285 g FB-Gericht: LG Korneuburg
--
-- PostgreSQL database dump
--
SET statement_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
SET default_with_oids = false;
CREATE OR REPLACE FUNCTION uuid_generate_v4()
RETURNS uuid AS
'$libdir/uuid-ossp', 'uuid_generate_v4'
LANGUAGE 'c' VOLATILE STRICT
COST 1;
--
-- Name: CurrentDateTimeDefaultValues; Type: TABLE; Schema: dbo; Owner: zbox;
Tablespace:
--
CREATE TABLE "CurrentDateTimeDefaultValues" (
"ID" integer NOT NULL
);
--
-- Name: DefaultPropertyValues; Type: TABLE; Schema: dbo; Owner: zbox;
Tablespace:
--
CREATE TABLE "DefaultPropertyValues" (
"ID" integer NOT NULL,
"CreatedOn" timestamp without time zone,
"ChangedOn" timestamp without time zone,
"ExportGuid" uuid DEFAULT public.uuid_generate_v4() NOT NULL,
"fk_CreatedBy" integer,
"fk_ChangedBy" integer,
"fk_Property" integer NOT NULL
);
--
-- Name: DefaultPropertyValues_ID_seq; Type: SEQUENCE; Schema: dbo; Owner: zbox
--
CREATE SEQUENCE "DefaultPropertyValues_ID_seq"
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;
--
-- Name: DefaultPropertyValues_ID_seq; Type: SEQUENCE OWNED BY; Schema: dbo;
Owner: zbox
--
ALTER SEQUENCE "DefaultPropertyValues_ID_seq" OWNED BY
"DefaultPropertyValues"."ID";
--
-- Name: DefaultPropertyValues_ID_seq; Type: SEQUENCE SET; Schema: dbo; Owner:
zbox
--
SELECT pg_catalog.setval('"DefaultPropertyValues_ID_seq"', 60, true);
--
-- Name: EnumDefaultValues; Type: TABLE; Schema: dbo; Owner: zbox; Tablespace:
--
CREATE TABLE "EnumDefaultValues" (
"ID" integer NOT NULL,
"fk_EnumValue" integer NOT NULL
);
--
-- Name: NewGuidDefaultValues; Type: TABLE; Schema: dbo; Owner: zbox;
Tablespace:
--
CREATE TABLE "NewGuidDefaultValues" (
"ID" integer NOT NULL
);
--
-- Name: ID; Type: DEFAULT; Schema: dbo; Owner: zbox
--
ALTER TABLE "DefaultPropertyValues" ALTER COLUMN "ID" SET DEFAULT
nextval('"DefaultPropertyValues_ID_seq"'::regclass);
--
-- Data for Name: CurrentDateTimeDefaultValues; Type: TABLE DATA; Schema: dbo;
Owner: zbox
--
COPY "CurrentDateTimeDefaultValues" ("ID") FROM stdin;
58
57
56
55
59
\.
--
-- Data for Name: DefaultPropertyValues; Type: TABLE DATA; Schema: dbo; Owner:
zbox
--
COPY "DefaultPropertyValues" ("ID", "CreatedOn", "ChangedOn", "ExportGuid",
"fk_CreatedBy", "fk_ChangedBy", "fk_Property") FROM stdin;
1 2010-08-13 15:21:05.76775 2010-08-13 15:21:05.76775
c76e0b6a-54e5-4aa7-94eb-275a1bc9371c \N \N 107
2 2010-07-12 18:11:15.14978 2010-07-12 18:11:15.14978
fc046683-3e72-412c-a4c4-be6bfb47d636 \N \N 142
3 2010-01-29 13:11:51.28 2010-01-29 13:11:51.28
0978bee8-7802-4c7d-b463-16a4f37b82ef \N \N 108
4 2010-03-25 09:36:15.417 2010-03-25 09:36:15.417
a907f2c6-f999-4306-b1a9-6e3ce05690de \N \N 103
5 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
9d9ed07b-5922-4009-8d9e-19ace4ba5d77 \N \N 100
6 2009-12-14 17:08:05.387 2009-12-14 17:08:05.387
f570c30e-ddff-4af7-9870-0512858b0d99 \N \N 101
7 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
ff4229a6-78cb-4ac4-86a1-342fc9a6b63b \N \N 102
8 2010-03-08 15:47:06.183 2010-03-08 15:47:06.183
3e7c34d5-e1ab-4f06-87d7-d1f61b4f2e3b \N \N 99
9 2010-08-16 11:08:36.566158 2010-08-16 11:08:36.566158
5981b736-dfa6-4c95-b2ab-eb0969beb962 \N \N 106
10 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
01c2abbc-fa48-4a55-9ba5-bd2f9f12375e \N \N 104
11 2010-08-11 14:38:12.67653 2010-08-11 14:38:12.67653
54267d1f-0800-4a43-b659-08c1b16b5235 \N \N 105
12 2010-07-23 16:21:13.775739 2010-07-23 16:21:13.775739
79203001-7fd9-4cf0-b38f-ca5e83ababf1 \N \N 184
13 2010-07-16 14:25:11.112133 2010-07-16 14:25:11.112133
cba59ff5-8cc1-4d9c-9130-6c114724524e \N \N 144
14 2010-07-22 11:55:24.863555 2010-07-22 11:55:24.863555
cffe625f-a171-4018-8022-61d655af4de8 \N \N 145
15 2009-12-14 17:08:05.403 2009-12-14 17:08:05.403
14918fa9-45e3-4bae-9e08-25268515610c \N \N 183
16 2010-08-11 12:10:50.764801 2010-08-11 12:10:50.764801
4ee7b660-482c-4edb-b525-6b6b517de318 \N \N 146
17 2010-08-20 10:54:30.49596 2010-08-20 10:54:30.49596
bb12edce-955c-438a-8d08-fd1f6dabff10 \N \N 143
18 2010-08-26 17:23:58.6985 2010-08-26 17:23:58.6985
b4814559-ce95-4f7d-9afe-c0378be65d8f \N \N 139
19 2010-07-21 16:55:50.018353 2010-07-21 16:55:50.018353
95602787-4e40-4b42-a54f-0ae8ae6efc46 \N \N 138
20 2010-07-12 17:57:28.204481 2010-07-12 17:57:28.204481
78c6c4a9-8115-476e-b717-41e2c5a36a48 \N \N 140
21 2010-05-26 11:21:03.997 2010-05-26 11:21:03.997
de9052f6-3039-4f2a-8fa0-f4c8b7484d83 \N \N 185
22 2010-08-30 17:25:46.040982 2010-08-30 17:25:46.040982
4bfb802f-4ec4-4cde-9ac0-fa22e59d1bc7 \N \N 141
23 2010-07-19 13:40:55.269556 2010-07-19 13:40:55.269556
c3aeed08-6e44-4525-bf05-c5db98c99f3b \N \N 180
24 2009-12-14 17:08:05.403 2009-12-14 17:08:05.403
fe342efd-3267-4221-be27-e51c2c85a18d \N \N 176
25 2010-07-12 16:32:08.613657 2010-07-12 16:32:08.613657
c2f8dd32-ec41-4c09-bfd5-f317127c4476 \N \N 177
26 2010-07-12 14:16:38.519642 2010-07-12 14:16:38.519642
7d7617ce-3413-401a-9833-6f26be9e9fc7 \N \N 179
27 2010-07-01 17:03:50.947 2010-07-01 17:03:50.947
1aa97f2d-ef92-478e-aabf-0b4b1048c50c \N \N 178
28 2010-07-01 17:03:50.943 2010-07-01 17:03:50.943
75b38722-7e3e-4d24-8918-e58f7bb562b3 \N \N 175
29 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
8314cea3-d577-49da-9e33-7a11130d61c5 \N \N 182
30 2010-07-08 15:56:41.988113 2010-07-08 15:56:41.988113
c5112a8e-f08b-48d2-9771-a053f053f146 \N \N 147
31 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
7099a399-42f5-470b-859f-f16847d617b3 \N \N 148
32 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
4edb8fb2-f2f2-49c1-addd-2584ac8c77ae \N \N 181
33 2010-07-01 17:03:50.947 2010-07-01 17:03:50.947
6308c43c-ce15-495a-b7cd-12428170b7b4 \N \N 174
34 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
157a7cc8-3c14-4e0a-8cb5-66a3ef2749c0 \N \N 115
35 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
7e8187e3-2e6a-439a-9534-f91192c33897 \N \N 114
36 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
849c1c20-8aaf-4c1e-9722-6634bd46301a \N \N 116
37 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
d90c6237-cea9-497c-af56-8a8265a5cff6 \N \N 118
38 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
4f91070a-3f9e-4c77-8814-99aea8430f22 \N \N 117
39 2010-08-26 18:19:01.649418 2010-08-26 18:19:01.649418
17454c31-d3d4-4ac0-b570-28df7f19db54 \N \N 110
40 2010-08-16 11:19:40.713145 2010-08-16 11:19:40.713145
87c42abc-1d68-4939-b86a-461af591037b \N \N 109
41 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
19092ca2-45dc-47bb-bec9-87b06f087929 \N \N 111
42 2010-06-01 11:02:55.373 2010-06-01 11:02:55.373
05ee1498-1300-4d4a-a3cb-e4712bf19c3e \N \N 113
43 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
32ad4eaa-febc-4b69-b875-ed2e7c782f4c \N \N 112
44 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
12614e14-f045-469f-85fa-7fc1e00ef0b7 \N \N 119
45 2010-07-12 15:48:11.088799 2010-07-12 15:48:11.088799
917dbd99-7444-4cc6-9999-5d830bb14d28 \N \N 128
46 2010-08-26 14:22:12.194683 2010-08-26 14:22:12.194683
0ee36644-86eb-4e4b-a5dd-82d0fa9d730d \N \N 127
47 2010-07-12 15:45:39.270116 2010-07-12 15:45:39.270116
5f03f849-81f0-4fbe-8fa0-0a994228db14 \N \N 129
48 2010-07-21 17:04:48.306141 2010-07-21 17:04:48.306141
0ff13553-8e40-45ed-996b-129556e62268 \N \N 137
49 2010-08-25 13:49:28.928468 2010-08-25 13:49:28.928468
ba1c85f7-607e-4452-acba-5aac7865ffa7 \N \N 136
50 2010-03-10 13:14:05.063 2010-03-10 13:14:05.063
9908f0df-2f0b-48ea-89c8-41337ad723f3 \N \N 124
51 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
353415fa-4304-481c-af7e-3484afcecf0c \N \N 121
52 2009-12-14 17:08:05.39 2009-12-14 17:08:05.39
8cea0e1d-82b6-4344-8d60-e850491b6157 \N \N 120
53 2010-01-29 11:01:14.903 2010-01-29 11:01:14.903
0d75021d-5071-4766-b63f-5d704879da12 \N \N 122
54 2010-08-13 15:18:48.821917 2010-08-13 15:18:48.821917
0292a7d3-8c44-4dcc-91bc-7767fe3956f0 \N \N 123
55 2010-08-30 11:26:50.352211 2010-08-30 11:26:50.352211
ce24b705-89ba-488d-afc2-9c4a81636889 \N \N 597
56 2010-08-30 11:27:09.659315 2010-08-30 11:27:09.659315
a39b2a6a-190b-4335-8d41-a763f2692fad \N \N 627
57 2010-08-30 11:25:36.588992 2010-08-30 11:25:36.588992
e0bdabe0-9a18-42e0-b2d2-ab107219f2d3 \N \N 605
58 2010-08-30 11:26:14.438157 2010-08-30 11:26:14.438157
4b8f603a-7305-4086-8dfe-560abe616fa5 \N \N 604
59 2010-09-20 12:25:47.706407 2010-09-20 12:25:47.706407
2597fb09-4d50-47cb-ba2a-0167b5f88fd5 \N \N 807
60 2010-09-20 13:22:17.773308 2010-09-20 13:22:17.773308
ecbd6901-13e8-4beb-a6a2-0548eba345a8 \N \N 783
\.
--
-- Data for Name: EnumDefaultValues; Type: TABLE DATA; Schema: dbo; Owner: zbox
--
COPY "EnumDefaultValues" ("ID", "fk_EnumValue") FROM stdin;
60 3
\.
--
-- Data for Name: NewGuidDefaultValues; Type: TABLE DATA; Schema: dbo; Owner:
zbox
--
COPY "NewGuidDefaultValues" ("ID") FROM stdin;
12
17
11
10
48
47
46
15
13
14
16
9
1
51
50
52
33
54
53
2
7
49
8
6
3
4
5
25
24
36
26
23
39
37
38
30
29
34
31
32
35
28
27
22
43
42
44
45
18
40
41
21
20
19
\.
--
-- Name: PK_[zbox].[dbo].[CurrentDateTimeDefaultValues]; Type: CONSTRAINT;
Schema: dbo; Owner: zbox; Tablespace:
--
ALTER TABLE ONLY "CurrentDateTimeDefaultValues"
ADD CONSTRAINT "PK_[zbox].[dbo].[CurrentDateTimeDefaultValues]" PRIMARY KEY
("ID");
--
-- Name: PK_[zbox].[dbo].[DefaultPropertyValues]; Type: CONSTRAINT; Schema:
dbo; Owner: zbox; Tablespace:
--
ALTER TABLE ONLY "DefaultPropertyValues"
ADD CONSTRAINT "PK_[zbox].[dbo].[DefaultPropertyValues]" PRIMARY KEY ("ID");
--
-- Name: PK_[zbox].[dbo].[EnumDefaultValues]; Type: CONSTRAINT; Schema: dbo;
Owner: zbox; Tablespace:
--
ALTER TABLE ONLY "EnumDefaultValues"
ADD CONSTRAINT "PK_[zbox].[dbo].[EnumDefaultValues]" PRIMARY KEY ("ID");
--
-- Name: PK_[zbox].[dbo].[NewGuidDefaultValues]; Type: CONSTRAINT; Schema: dbo;
Owner: zbox; Tablespace:
--
ALTER TABLE ONLY "NewGuidDefaultValues"
ADD CONSTRAINT "PK_[zbox].[dbo].[NewGuidDefaultValues]" PRIMARY KEY ("ID");
--
-- Name: IDX_DefaultPropertyValues_fk_Property; Type: INDEX; Schema: dbo;
Owner: zbox; Tablespace:
--
CREATE UNIQUE INDEX "IDX_DefaultPropertyValues_fk_Property" ON
"DefaultPropertyValues" USING btree ("fk_Property");
--
-- Name: UIDX_DefaultPropertyValues_ExportGuid; Type: INDEX; Schema: dbo;
Owner: zbox; Tablespace:
--
CREATE UNIQUE INDEX "UIDX_DefaultPropertyValues_ExportGuid" ON
"DefaultPropertyValues" USING btree ("ExportGuid");
--
-- Name: FK_CurrentDateTimeDefaultValue_DefaultPropertyValue_ID; Type: FK
CONSTRAINT; Schema: dbo; Owner: zbox
--
ALTER TABLE ONLY "CurrentDateTimeDefaultValues"
ADD CONSTRAINT "FK_CurrentDateTimeDefaultValue_DefaultPropertyValue_ID"
FOREIGN KEY ("ID") REFERENCES "DefaultPropertyValues"("ID");
--
-- Name: FK_EnumDefaultValue_DefaultPropertyValue_ID; Type: FK CONSTRAINT;
Schema: dbo; Owner: zbox
--
ALTER TABLE ONLY "EnumDefaultValues"
ADD CONSTRAINT "FK_EnumDefaultValue_DefaultPropertyValue_ID" FOREIGN KEY
("ID") REFERENCES "DefaultPropertyValues"("ID");
--
-- Name: FK_NewGuidDefaultValue_DefaultPropertyValue_ID; Type: FK CONSTRAINT;
Schema: dbo; Owner: zbox
--
ALTER TABLE ONLY "NewGuidDefaultValues"
ADD CONSTRAINT "FK_NewGuidDefaultValue_DefaultPropertyValue_ID" FOREIGN KEY
("ID") REFERENCES "DefaultPropertyValues"("ID");
--
-- PostgreSQL database dump complete
--
select *
from "DefaultPropertyValues" AS "Extent1"
INNER JOIN "DefaultPropertyValues" AS "Extent2" ON
"Extent1"."ID"="Extent2"."ID"
LEFT JOIN (
SELECT "UnionAll1"."ID" AS "C1","UnionAll1"."C1" AS
"C2","UnionAll1"."C2" AS "C3","UnionAll1"."C3" AS "C4","UnionAll1"."C4" AS
"fk_EnumVal" FROM (
SELECT "Extent3"."ID" AS "ID",TRUE AS "C1",FALSE AS
"C2",FALSE AS "C3",CAST (NULL AS int4) AS "C4"
FROM "CurrentDateTimeDefaultValues" AS "Extent3"
UNION ALL
SELECT "Extent4"."ID" AS "ID", TRUE AS "C1",FALSE AS
"C2",TRUE AS "C3",CAST (NULL AS int4) AS "C4"
FROM "NewGuidDefaultValues" AS "Extent4") AS "UnionAll1"
UNION ALL
SELECT "Extent5"."ID" AS "ID",FALSE AS "C1",TRUE AS "C2",FALSE
AS "C3","Extent5"."fk_EnumValue" AS "fk_EnumValue"
FROM "EnumDefaultValues" AS "Extent5") AS "UnionAll2"
ON "Extent2"."ID"="UnionAll2"."C1"
WHERE ("Extent1"."fk_Property" IS NOT NULL ) AND ("Extent1"."fk_Property"=
(((783))));
ALTER TABLE ONLY "EnumDefaultValues" DROP CONSTRAINT
"PK_[zbox].[dbo].[EnumDefaultValues]";
select *
from "DefaultPropertyValues" AS "Extent1"
INNER JOIN "DefaultPropertyValues" AS "Extent2" ON
"Extent1"."ID"="Extent2"."ID"
LEFT JOIN (
SELECT "UnionAll1"."ID" AS "C1","UnionAll1"."C1" AS
"C2","UnionAll1"."C2" AS "C3","UnionAll1"."C3" AS "C4","UnionAll1"."C4" AS
"fk_EnumVal" FROM (
SELECT "Extent3"."ID" AS "ID",TRUE AS "C1",FALSE AS
"C2",FALSE AS "C3",CAST (NULL AS int4) AS "C4"
FROM "CurrentDateTimeDefaultValues" AS "Extent3"
UNION ALL
SELECT "Extent4"."ID" AS "ID", TRUE AS "C1",FALSE AS
"C2",TRUE AS "C3",CAST (NULL AS int4) AS "C4"
FROM "NewGuidDefaultValues" AS "Extent4") AS "UnionAll1"
UNION ALL
SELECT "Extent5"."ID" AS "ID",FALSE AS "C1",TRUE AS "C2",FALSE
AS "C3","Extent5"."fk_EnumValue" AS "fk_EnumValue"
FROM "EnumDefaultValues" AS "Extent5") AS "UnionAll2"
ON "Extent2"."ID"="UnionAll2"."C1"
WHERE ("Extent1"."fk_Property" IS NOT NULL ) AND ("Extent1"."fk_Property"=
(((783))));
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
