Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1697568

Browse files
committed
Repair recalculation failure for nested sub-SELECTs, per bug report from
Didier Moens. Bug is new in 7.4, and was caused by not updating everyplaceI should've when replacing locParam markers by allParam.Add a regression test to catch related errors in future.
1 parent608b85a commit1697568

File tree

3 files changed

+143
-1
lines changed

3 files changed

+143
-1
lines changed

‎src/backend/optimizer/plan/planner.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.168 2004/04/07 18:17:24 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planner.c,v 1.169 2004/05/11 02:21:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -343,6 +343,9 @@ subquery_planner(Query *parse, double tuple_fraction)
343343

344344
plan->extParam=bms_add_members(plan->extParam,
345345
initplan->plan->extParam);
346+
/* allParam must include all members of extParam */
347+
plan->allParam=bms_add_members(plan->allParam,
348+
plan->extParam);
346349
initplan_cost+=initplan->plan->total_cost;
347350
}
348351

‎src/test/regress/expected/subselect.out

Lines changed: 75 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -201,3 +201,78 @@ select count(distinct ss.ten) from
201201
10
202202
(1 row)
203203

204+
--
205+
-- Test case to catch problems with multiply nested sub-SELECTs not getting
206+
-- recalculated properly. Per bug report from Didier Moens.
207+
--
208+
CREATE TABLE orderstest (
209+
approver_ref integer,
210+
po_ref integer,
211+
ordercancelled boolean
212+
);
213+
INSERT INTO orderstest VALUES (1, 1, false);
214+
INSERT INTO orderstest VALUES (66, 5, false);
215+
INSERT INTO orderstest VALUES (66, 6, false);
216+
INSERT INTO orderstest VALUES (66, 7, false);
217+
INSERT INTO orderstest VALUES (66, 1, true);
218+
INSERT INTO orderstest VALUES (66, 8, false);
219+
INSERT INTO orderstest VALUES (66, 1, false);
220+
INSERT INTO orderstest VALUES (77, 1, false);
221+
INSERT INTO orderstest VALUES (1, 1, false);
222+
INSERT INTO orderstest VALUES (66, 1, false);
223+
INSERT INTO orderstest VALUES (1, 1, false);
224+
CREATE VIEW orders_view AS
225+
SELECT *,
226+
(SELECT CASE
227+
WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
228+
END) AS "Approved",
229+
(SELECT CASE
230+
WHEN ord.ordercancelled
231+
THEN 'Cancelled'
232+
ELSE
233+
(SELECT CASE
234+
WHEN ord.po_ref=1
235+
THEN
236+
(SELECT CASE
237+
WHEN ord.approver_ref=1
238+
THEN '---'
239+
ELSE 'Approved'
240+
END)
241+
ELSE 'PO'
242+
END)
243+
END) AS "Status",
244+
(CASE
245+
WHEN ord.ordercancelled
246+
THEN 'Cancelled'
247+
ELSE
248+
(CASE
249+
WHEN ord.po_ref=1
250+
THEN
251+
(CASE
252+
WHEN ord.approver_ref=1
253+
THEN '---'
254+
ELSE 'Approved'
255+
END)
256+
ELSE 'PO'
257+
END)
258+
END) AS "Status_OK"
259+
FROM orderstest ord;
260+
SELECT * FROM orders_view;
261+
approver_ref | po_ref | ordercancelled | Approved | Status | Status_OK
262+
--------------+--------+----------------+----------+-----------+-----------
263+
1 | 1 | f | --- | --- | ---
264+
66 | 5 | f | Approved | PO | PO
265+
66 | 6 | f | Approved | PO | PO
266+
66 | 7 | f | Approved | PO | PO
267+
66 | 1 | t | Approved | Cancelled | Cancelled
268+
66 | 8 | f | Approved | PO | PO
269+
66 | 1 | f | Approved | Approved | Approved
270+
77 | 1 | f | Approved | Approved | Approved
271+
1 | 1 | f | --- | --- | ---
272+
66 | 1 | f | Approved | Approved | Approved
273+
1 | 1 | f | --- | --- | ---
274+
(11 rows)
275+
276+
DROP TABLE orderstest cascade;
277+
NOTICE: drop cascades to rule _RETURN on view orders_view
278+
NOTICE: drop cascades to view orders_view

‎src/test/regress/sql/subselect.sql

Lines changed: 64 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -93,3 +93,67 @@ select count(*) from
9393
selectcount(distinctss.ten)from
9494
(select tenfrom tenk1 a
9595
where unique1IN (select distinct hundredfrom tenk1 b)) ss;
96+
97+
--
98+
-- Test case to catch problems with multiply nested sub-SELECTs not getting
99+
-- recalculated properly. Per bug report from Didier Moens.
100+
--
101+
102+
CREATETABLEorderstest (
103+
approver_refinteger,
104+
po_refinteger,
105+
ordercancelledboolean
106+
);
107+
108+
INSERT INTO orderstestVALUES (1,1, false);
109+
INSERT INTO orderstestVALUES (66,5, false);
110+
INSERT INTO orderstestVALUES (66,6, false);
111+
INSERT INTO orderstestVALUES (66,7, false);
112+
INSERT INTO orderstestVALUES (66,1, true);
113+
INSERT INTO orderstestVALUES (66,8, false);
114+
INSERT INTO orderstestVALUES (66,1, false);
115+
INSERT INTO orderstestVALUES (77,1, false);
116+
INSERT INTO orderstestVALUES (1,1, false);
117+
INSERT INTO orderstestVALUES (66,1, false);
118+
INSERT INTO orderstestVALUES (1,1, false);
119+
120+
CREATEVIEWorders_viewAS
121+
SELECT*,
122+
(SELECT CASE
123+
WHENord.approver_ref=1 THEN'---' ELSE'Approved'
124+
END)AS"Approved",
125+
(SELECT CASE
126+
WHENord.ordercancelled
127+
THEN'Cancelled'
128+
ELSE
129+
(SELECT CASE
130+
WHENord.po_ref=1
131+
THEN
132+
(SELECT CASE
133+
WHENord.approver_ref=1
134+
THEN'---'
135+
ELSE'Approved'
136+
END)
137+
ELSE'PO'
138+
END)
139+
END)AS"Status",
140+
(CASE
141+
WHENord.ordercancelled
142+
THEN'Cancelled'
143+
ELSE
144+
(CASE
145+
WHENord.po_ref=1
146+
THEN
147+
(CASE
148+
WHENord.approver_ref=1
149+
THEN'---'
150+
ELSE'Approved'
151+
END)
152+
ELSE'PO'
153+
END)
154+
END)AS"Status_OK"
155+
FROM orderstest ord;
156+
157+
SELECT*FROM orders_view;
158+
159+
DROPTABLE orderstest cascade;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp