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

Commitdcc2334

Browse files
committed
Consider a clause to be outerjoin_delayed if it references the nullable side
of any lower outer join, even if it also references the non-nullable side andso could not get pushed below the outer join anyway. We need this in casethe clause is an OR clause: if it doesn't get marked outerjoin_delayed,create_or_index_quals() could pull an indexable restriction for the nullableside out of it, leading to wrong results as demonstrated by today's bugreport from toruvinn. (See added regression test case for an example.)In principle this has been wrong for quite a while. In practice I don'tthink any branch before 8.3 can really show the failure, becausecreate_or_index_quals() will only pull out indexable conditions, and before8.3 those were always strict. So though we might have improperly generatednull-extended rows in the outer join, they'd get discarded from the resultanyway. The gating factor that makes the failure visible is that 8.3considers "col IS NULL" to be indexable. Hence I'm not going to riskback-patching further than 8.3.
1 parentf6c1dec commitdcc2334

File tree

4 files changed

+38
-3
lines changed

4 files changed

+38
-3
lines changed

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

Lines changed: 4 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.139 2008/04/01 00:48:33 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/initsplan.c,v 1.140 2008/06/27 20:54:37 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -1092,14 +1092,15 @@ check_outerjoin_delay(PlannerInfo *root, Relids *relids_p,
10921092
(ojinfo->is_full_join&&
10931093
bms_overlap(relids,ojinfo->min_lefthand)))
10941094
{
1095-
/* yes; have we included all its rels in relids? */
1095+
/* yes, so set the result flag */
1096+
outerjoin_delayed= true;
1097+
/* have we included all its rels in relids? */
10961098
if (!bms_is_subset(ojinfo->min_lefthand,relids)||
10971099
!bms_is_subset(ojinfo->min_righthand,relids))
10981100
{
10991101
/* no, so add them in */
11001102
relids=bms_add_members(relids,ojinfo->min_lefthand);
11011103
relids=bms_add_members(relids,ojinfo->min_righthand);
1102-
outerjoin_delayed= true;
11031104
/* we'll need another iteration */
11041105
found_some= true;
11051106
}

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2321,3 +2321,15 @@ where f2 = 53;
23212321
53 | | |
23222322
(1 row)
23232323

2324+
--
2325+
-- regression test for improper extraction of OR indexqual conditions
2326+
-- (as seen in early 8.3.x releases)
2327+
--
2328+
select a.unique2, a.ten, b.tenthous, b.unique2, b.hundred
2329+
from tenk1 a left join tenk1 b on a.unique2 = b.tenthous
2330+
where a.unique1 = 42 and
2331+
((b.unique2 is null and a.ten = 2) or b.hundred = 3);
2332+
unique2 | ten | tenthous | unique2 | hundred
2333+
---------+-----+----------+---------+---------
2334+
(0 rows)
2335+

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

Lines changed: 12 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2321,3 +2321,15 @@ where f2 = 53;
23212321
53 | | |
23222322
(1 row)
23232323

2324+
--
2325+
-- regression test for improper extraction of OR indexqual conditions
2326+
-- (as seen in early 8.3.x releases)
2327+
--
2328+
select a.unique2, a.ten, b.tenthous, b.unique2, b.hundred
2329+
from tenk1 a left join tenk1 b on a.unique2 = b.tenthous
2330+
where a.unique1 = 42 and
2331+
((b.unique2 is null and a.ten = 2) or b.hundred = 3);
2332+
unique2 | ten | tenthous | unique2 | hundred
2333+
---------+-----+----------+---------+---------
2334+
(0 rows)
2335+

‎src/test/regress/sql/join.sql

Lines changed: 10 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -485,3 +485,13 @@ select * from
485485
zt2left join zt3on (f2= f3)
486486
left join zv1on (f3= f1)
487487
where f2=53;
488+
489+
--
490+
-- regression test for improper extraction of OR indexqual conditions
491+
-- (as seen in early 8.3.x releases)
492+
--
493+
494+
selecta.unique2,a.ten,b.tenthous,b.unique2,b.hundred
495+
from tenk1 aleft join tenk1 bona.unique2=b.tenthous
496+
wherea.unique1=42and
497+
((b.unique2 isnullanda.ten=2)orb.hundred=3);

0 commit comments

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