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

Commit8a4fdce

Browse files
committed
Fix thinko in new logic about pushing down non-nullability constraints:
constraints appearing in outer-join qualification clauses are restrictedas to when and where they can be pushed down. Add regression testto catch future errors in this area.
1 parentec8f0e8 commit8a4fdce

File tree

3 files changed

+192
-8
lines changed

3 files changed

+192
-8
lines changed

‎src/backend/optimizer/prep/prepjointree.c

Lines changed: 30 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -16,7 +16,7 @@
1616
*
1717
*
1818
* IDENTIFICATION
19-
* $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.5 2003/02/09 23:57:19 tgl Exp $
19+
* $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.6 2003/02/10 17:08:50 tgl Exp $
2020
*
2121
*-------------------------------------------------------------------------
2222
*/
@@ -694,23 +694,45 @@ reduce_outer_joins_pass2(Node *jtnode,
694694
/* Only recurse if there's more to do below here */
695695
if (left_state->contains_outer||right_state->contains_outer)
696696
{
697+
Relidslocal_nonnullable;
697698
Relidspass_nonnullable;
698699

699700
/*
700-
* Scan join quals to see if we can add any nonnullability
701-
* constraints. (Okay to do this even if join is still outer.)
701+
* If this join is (now) inner, we can add any nonnullability
702+
* constraints its quals provide to those we got from above.
703+
* But if it is outer, we can only pass down the local constraints
704+
* into the nullable side, because an outer join never eliminates
705+
* any rows from its non-nullable side. If it's a FULL join then
706+
* it doesn't eliminate anything from either side.
702707
*/
703-
pass_nonnullable=find_nonnullable_rels(j->quals, true);
704-
pass_nonnullable=bms_add_members(pass_nonnullable,
705-
nonnullable_rels);
706-
/* And recurse as needed */
708+
if (jointype!=JOIN_FULL)
709+
{
710+
local_nonnullable=find_nonnullable_rels(j->quals, true);
711+
local_nonnullable=bms_add_members(local_nonnullable,
712+
nonnullable_rels);
713+
}
714+
else
715+
local_nonnullable=NULL;/* no use in calculating it */
716+
707717
if (left_state->contains_outer)
718+
{
719+
if (jointype==JOIN_INNER||jointype==JOIN_RIGHT)
720+
pass_nonnullable=local_nonnullable;
721+
else
722+
pass_nonnullable=nonnullable_rels;
708723
reduce_outer_joins_pass2(j->larg,left_state,parse,
709724
pass_nonnullable);
725+
}
710726
if (right_state->contains_outer)
727+
{
728+
if (jointype==JOIN_INNER||jointype==JOIN_LEFT)
729+
pass_nonnullable=local_nonnullable;
730+
else
731+
pass_nonnullable=nonnullable_rels;
711732
reduce_outer_joins_pass2(j->rarg,right_state,parse,
712733
pass_nonnullable);
713-
bms_free(pass_nonnullable);
734+
}
735+
bms_free(local_nonnullable);
714736
}
715737
}
716738
else

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

Lines changed: 123 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2004,6 +2004,129 @@ NATURAL FULL JOIN
20042004
dd | | 42 | 2 |
20052005
(4 rows)
20062006

2007+
-- Test for propagation of nullability constraints into sub-joins
2008+
create temp table x (x1 int, x2 int);
2009+
insert into x values (1,11);
2010+
insert into x values (2,22);
2011+
insert into x values (3,null);
2012+
insert into x values (4,44);
2013+
insert into x values (5,null);
2014+
create temp table y (y1 int, y2 int);
2015+
insert into y values (1,111);
2016+
insert into y values (2,222);
2017+
insert into y values (3,333);
2018+
insert into y values (4,null);
2019+
select * from x;
2020+
x1 | x2
2021+
----+----
2022+
1 | 11
2023+
2 | 22
2024+
3 |
2025+
4 | 44
2026+
5 |
2027+
(5 rows)
2028+
2029+
select * from y;
2030+
y1 | y2
2031+
----+-----
2032+
1 | 111
2033+
2 | 222
2034+
3 | 333
2035+
4 |
2036+
(4 rows)
2037+
2038+
select * from x left join y on (x1 = y1 and x2 is not null);
2039+
x1 | x2 | y1 | y2
2040+
----+----+----+-----
2041+
1 | 11 | 1 | 111
2042+
2 | 22 | 2 | 222
2043+
3 | | |
2044+
4 | 44 | 4 |
2045+
5 | | |
2046+
(5 rows)
2047+
2048+
select * from x left join y on (x1 = y1 and y2 is not null);
2049+
x1 | x2 | y1 | y2
2050+
----+----+----+-----
2051+
1 | 11 | 1 | 111
2052+
2 | 22 | 2 | 222
2053+
3 | | 3 | 333
2054+
4 | 44 | |
2055+
5 | | |
2056+
(5 rows)
2057+
2058+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2059+
on (x1 = xx1);
2060+
x1 | x2 | y1 | y2 | xx1 | xx2
2061+
----+----+----+-----+-----+-----
2062+
1 | 11 | 1 | 111 | 1 | 11
2063+
2 | 22 | 2 | 222 | 2 | 22
2064+
3 | | 3 | 333 | 3 |
2065+
4 | 44 | 4 | | 4 | 44
2066+
5 | | | | 5 |
2067+
(5 rows)
2068+
2069+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2070+
on (x1 = xx1 and x2 is not null);
2071+
x1 | x2 | y1 | y2 | xx1 | xx2
2072+
----+----+----+-----+-----+-----
2073+
1 | 11 | 1 | 111 | 1 | 11
2074+
2 | 22 | 2 | 222 | 2 | 22
2075+
3 | | 3 | 333 | |
2076+
4 | 44 | 4 | | 4 | 44
2077+
5 | | | | |
2078+
(5 rows)
2079+
2080+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2081+
on (x1 = xx1 and y2 is not null);
2082+
x1 | x2 | y1 | y2 | xx1 | xx2
2083+
----+----+----+-----+-----+-----
2084+
1 | 11 | 1 | 111 | 1 | 11
2085+
2 | 22 | 2 | 222 | 2 | 22
2086+
3 | | 3 | 333 | 3 |
2087+
4 | 44 | 4 | | |
2088+
5 | | | | |
2089+
(5 rows)
2090+
2091+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2092+
on (x1 = xx1 and xx2 is not null);
2093+
x1 | x2 | y1 | y2 | xx1 | xx2
2094+
----+----+----+-----+-----+-----
2095+
1 | 11 | 1 | 111 | 1 | 11
2096+
2 | 22 | 2 | 222 | 2 | 22
2097+
3 | | 3 | 333 | |
2098+
4 | 44 | 4 | | 4 | 44
2099+
5 | | | | |
2100+
(5 rows)
2101+
2102+
-- these should NOT give the same answers as above
2103+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2104+
on (x1 = xx1) where (x2 is not null);
2105+
x1 | x2 | y1 | y2 | xx1 | xx2
2106+
----+----+----+-----+-----+-----
2107+
1 | 11 | 1 | 111 | 1 | 11
2108+
2 | 22 | 2 | 222 | 2 | 22
2109+
4 | 44 | 4 | | 4 | 44
2110+
(3 rows)
2111+
2112+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2113+
on (x1 = xx1) where (y2 is not null);
2114+
x1 | x2 | y1 | y2 | xx1 | xx2
2115+
----+----+----+-----+-----+-----
2116+
1 | 11 | 1 | 111 | 1 | 11
2117+
2 | 22 | 2 | 222 | 2 | 22
2118+
3 | | 3 | 333 | 3 |
2119+
(3 rows)
2120+
2121+
select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2)
2122+
on (x1 = xx1) where (xx2 is not null);
2123+
x1 | x2 | y1 | y2 | xx1 | xx2
2124+
----+----+----+-----+-----+-----
2125+
1 | 11 | 1 | 111 | 1 | 11
2126+
2 | 22 | 2 | 222 | 2 | 22
2127+
4 | 44 | 4 | | 4 | 44
2128+
(3 rows)
2129+
20072130
--
20082131
-- Clean up
20092132
--

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

Lines changed: 39 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -292,6 +292,45 @@ NATURAL FULL JOIN
292292
(SELECT name, nas s3_nFROM t3)as s3
293293
) ss2;
294294

295+
296+
-- Test for propagation of nullability constraints into sub-joins
297+
298+
create temp table x (x1int, x2int);
299+
insert into xvalues (1,11);
300+
insert into xvalues (2,22);
301+
insert into xvalues (3,null);
302+
insert into xvalues (4,44);
303+
insert into xvalues (5,null);
304+
305+
create temp table y (y1int, y2int);
306+
insert into yvalues (1,111);
307+
insert into yvalues (2,222);
308+
insert into yvalues (3,333);
309+
insert into yvalues (4,null);
310+
311+
select*from x;
312+
select*from y;
313+
314+
select*from xleft join yon (x1= y1and x2is not null);
315+
select*from xleft join yon (x1= y1and y2is not null);
316+
317+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
318+
on (x1= xx1);
319+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
320+
on (x1= xx1and x2is not null);
321+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
322+
on (x1= xx1and y2is not null);
323+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
324+
on (x1= xx1and xx2is not null);
325+
-- these should NOT give the same answers as above
326+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
327+
on (x1= xx1)where (x2is not null);
328+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
329+
on (x1= xx1)where (y2is not null);
330+
select*from (xleft join yon (x1= y1))left join x xx(xx1,xx2)
331+
on (x1= xx1)where (xx2is not null);
332+
333+
295334
--
296335
-- Clean up
297336
--

0 commit comments

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