From 8a4fdce9f277fe1f02654091e89e96180af9c4cb Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Mon, 10 Feb 2003 17:08:50 +0000 Subject: [PATCH] Fix thinko in new logic about pushing down non-nullability constraints: constraints appearing in outer-join qualification clauses are restricted as to when and where they can be pushed down. Add regression test to catch future errors in this area. --- src/backend/optimizer/prep/prepjointree.c | 38 +++++++-- src/test/regress/expected/join.out | 123 ++++++++++++++++++++++++++++++ src/test/regress/sql/join.sql | 39 ++++++++++ 3 files changed, 192 insertions(+), 8 deletions(-) diff --git a/src/backend/optimizer/prep/prepjointree.c b/src/backend/optimizer/prep/prepjointree.c index 54fd393df5..207a813e8e 100644 --- a/src/backend/optimizer/prep/prepjointree.c +++ b/src/backend/optimizer/prep/prepjointree.c @@ -16,7 +16,7 @@ * * * IDENTIFICATION - * $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.5 2003/02/09 23:57:19 tgl Exp $ + * $Header: /cvsroot/pgsql/src/backend/optimizer/prep/prepjointree.c,v 1.6 2003/02/10 17:08:50 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -694,23 +694,45 @@ reduce_outer_joins_pass2(Node *jtnode, /* Only recurse if there's more to do below here */ if (left_state->contains_outer || right_state->contains_outer) { + Relids local_nonnullable; Relids pass_nonnullable; /* - * Scan join quals to see if we can add any nonnullability - * constraints. (Okay to do this even if join is still outer.) + * If this join is (now) inner, we can add any nonnullability + * constraints its quals provide to those we got from above. + * But if it is outer, we can only pass down the local constraints + * into the nullable side, because an outer join never eliminates + * any rows from its non-nullable side. If it's a FULL join then + * it doesn't eliminate anything from either side. */ - pass_nonnullable = find_nonnullable_rels(j->quals, true); - pass_nonnullable = bms_add_members(pass_nonnullable, - nonnullable_rels); - /* And recurse as needed */ + if (jointype != JOIN_FULL) + { + local_nonnullable = find_nonnullable_rels(j->quals, true); + local_nonnullable = bms_add_members(local_nonnullable, + nonnullable_rels); + } + else + local_nonnullable = NULL; /* no use in calculating it */ + if (left_state->contains_outer) + { + if (jointype == JOIN_INNER || jointype == JOIN_RIGHT) + pass_nonnullable = local_nonnullable; + else + pass_nonnullable = nonnullable_rels; reduce_outer_joins_pass2(j->larg, left_state, parse, pass_nonnullable); + } if (right_state->contains_outer) + { + if (jointype == JOIN_INNER || jointype == JOIN_LEFT) + pass_nonnullable = local_nonnullable; + else + pass_nonnullable = nonnullable_rels; reduce_outer_joins_pass2(j->rarg, right_state, parse, pass_nonnullable); - bms_free(pass_nonnullable); + } + bms_free(local_nonnullable); } } else diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index e04a90e072..ea2abf581d 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2004,6 +2004,129 @@ NATURAL FULL JOIN dd | | 42 | 2 | (4 rows) +-- Test for propagation of nullability constraints into sub-joins +create temp table x (x1 int, x2 int); +insert into x values (1,11); +insert into x values (2,22); +insert into x values (3,null); +insert into x values (4,44); +insert into x values (5,null); +create temp table y (y1 int, y2 int); +insert into y values (1,111); +insert into y values (2,222); +insert into y values (3,333); +insert into y values (4,null); +select * from x; + x1 | x2 +----+---- + 1 | 11 + 2 | 22 + 3 | + 4 | 44 + 5 | +(5 rows) + +select * from y; + y1 | y2 +----+----- + 1 | 111 + 2 | 222 + 3 | 333 + 4 | +(4 rows) + +select * from x left join y on (x1 = y1 and x2 is not null); + x1 | x2 | y1 | y2 +----+----+----+----- + 1 | 11 | 1 | 111 + 2 | 22 | 2 | 222 + 3 | | | + 4 | 44 | 4 | + 5 | | | +(5 rows) + +select * from x left join y on (x1 = y1 and y2 is not null); + x1 | x2 | y1 | y2 +----+----+----+----- + 1 | 11 | 1 | 111 + 2 | 22 | 2 | 222 + 3 | | 3 | 333 + 4 | 44 | | + 5 | | | +(5 rows) + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 3 | | 3 | 333 | 3 | + 4 | 44 | 4 | | 4 | 44 + 5 | | | | 5 | +(5 rows) + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1 and x2 is not null); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 3 | | 3 | 333 | | + 4 | 44 | 4 | | 4 | 44 + 5 | | | | | +(5 rows) + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1 and y2 is not null); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 3 | | 3 | 333 | 3 | + 4 | 44 | 4 | | | + 5 | | | | | +(5 rows) + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1 and xx2 is not null); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 3 | | 3 | 333 | | + 4 | 44 | 4 | | 4 | 44 + 5 | | | | | +(5 rows) + +-- these should NOT give the same answers as above +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1) where (x2 is not null); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 4 | 44 | 4 | | 4 | 44 +(3 rows) + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1) where (y2 is not null); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 3 | | 3 | 333 | 3 | +(3 rows) + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1) where (xx2 is not null); + x1 | x2 | y1 | y2 | xx1 | xx2 +----+----+----+-----+-----+----- + 1 | 11 | 1 | 111 | 1 | 11 + 2 | 22 | 2 | 222 | 2 | 22 + 4 | 44 | 4 | | 4 | 44 +(3 rows) + -- -- Clean up -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 98c94ac34e..e4ccfa4582 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -292,6 +292,45 @@ NATURAL FULL JOIN (SELECT name, n as s3_n FROM t3) as s3 ) ss2; + +-- Test for propagation of nullability constraints into sub-joins + +create temp table x (x1 int, x2 int); +insert into x values (1,11); +insert into x values (2,22); +insert into x values (3,null); +insert into x values (4,44); +insert into x values (5,null); + +create temp table y (y1 int, y2 int); +insert into y values (1,111); +insert into y values (2,222); +insert into y values (3,333); +insert into y values (4,null); + +select * from x; +select * from y; + +select * from x left join y on (x1 = y1 and x2 is not null); +select * from x left join y on (x1 = y1 and y2 is not null); + +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1); +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1 and x2 is not null); +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1 and y2 is not null); +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1 and xx2 is not null); +-- these should NOT give the same answers as above +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1) where (x2 is not null); +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1) where (y2 is not null); +select * from (x left join y on (x1 = y1)) left join x xx(xx1,xx2) +on (x1 = xx1) where (xx2 is not null); + + -- -- Clean up -- -- 2.11.0