Skip to content

Commit 1697568

Browse files
tglsfdctglsfdc
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 everyplace I should've when replacing locParam markers by allParam. Add a regression test to catch related errors in future.
1 parent 608b85a commit 1697568

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
select count(distinct ss.ten) from
9494
(select ten from tenk1 a
9595
where unique1 IN (select distinct hundred from 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+
CREATE TABLE orderstest (
103+
approver_ref integer,
104+
po_ref integer,
105+
ordercancelled boolean
106+
);
107+
108+
INSERT INTO orderstest VALUES (1, 1, false);
109+
INSERT INTO orderstest VALUES (66, 5, false);
110+
INSERT INTO orderstest VALUES (66, 6, false);
111+
INSERT INTO orderstest VALUES (66, 7, false);
112+
INSERT INTO orderstest VALUES (66, 1, true);
113+
INSERT INTO orderstest VALUES (66, 8, false);
114+
INSERT INTO orderstest VALUES (66, 1, false);
115+
INSERT INTO orderstest VALUES (77, 1, false);
116+
INSERT INTO orderstest VALUES (1, 1, false);
117+
INSERT INTO orderstest VALUES (66, 1, false);
118+
INSERT INTO orderstest VALUES (1, 1, false);
119+
120+
CREATE VIEW orders_view AS
121+
SELECT *,
122+
(SELECT CASE
123+
WHEN ord.approver_ref=1 THEN '---' ELSE 'Approved'
124+
END) AS "Approved",
125+
(SELECT CASE
126+
WHEN ord.ordercancelled
127+
THEN 'Cancelled'
128+
ELSE
129+
(SELECT CASE
130+
WHEN ord.po_ref=1
131+
THEN
132+
(SELECT CASE
133+
WHEN ord.approver_ref=1
134+
THEN '---'
135+
ELSE 'Approved'
136+
END)
137+
ELSE 'PO'
138+
END)
139+
END) AS "Status",
140+
(CASE
141+
WHEN ord.ordercancelled
142+
THEN 'Cancelled'
143+
ELSE
144+
(CASE
145+
WHEN ord.po_ref=1
146+
THEN
147+
(CASE
148+
WHEN ord.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+
DROP TABLE orderstest cascade;

0 commit comments

Comments
 (0)