diff options
Diffstat (limited to 'src')
| -rw-r--r-- | src/backend/optimizer/path/pathkeys.c | 65 | ||||
| -rw-r--r-- | src/test/regress/expected/join.out | 31 | ||||
| -rw-r--r-- | src/test/regress/expected/join_1.out | 31 | ||||
| -rw-r--r-- | src/test/regress/sql/join.sql | 23 |
4 files changed, 117 insertions, 33 deletions
diff --git a/src/backend/optimizer/path/pathkeys.c b/src/backend/optimizer/path/pathkeys.c index b254598fcf3..abc50ef14d0 100644 --- a/src/backend/optimizer/path/pathkeys.c +++ b/src/backend/optimizer/path/pathkeys.c @@ -11,7 +11,7 @@ * Portions Copyright (c) 1994, Regents of the University of California * * IDENTIFICATION - * $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.79 2006/10/04 00:29:54 momjian Exp $ + * $PostgreSQL: pgsql/src/backend/optimizer/path/pathkeys.c,v 1.79.2.1 2008/01/09 20:50:11 tgl Exp $ * *------------------------------------------------------------------------- */ @@ -423,15 +423,16 @@ sub_generate_join_implications(PlannerInfo *root, false); /* - * We can remove explicit tests of this outer-join qual, too, - * since we now have tests forcing each of its sides to the same - * value. + * We used to think we could remove explicit tests of this + * outer-join qual, too, since we now have tests forcing each of + * its sides to the same value. However, that fails in some + * corner cases where lower outer joins could cause one of the + * variables to go to NULL. (BUG in 8.2 through 8.2.6.) + * So now we just leave it in place, but mark it with selectivity + * 1.0 so that we don't underestimate the join size output --- + * it's mostly redundant with the constant constraints. */ - process_implied_equality(root, - leftop, rightop, - rinfo->left_sortop, rinfo->right_sortop, - rinfo->left_relids, rinfo->right_relids, - true); + rinfo->this_selec = 1.0; /* * And recurse to see if we can deduce anything from INNERVAR = @@ -465,15 +466,16 @@ sub_generate_join_implications(PlannerInfo *root, false); /* - * We can remove explicit tests of this outer-join qual, too, - * since we now have tests forcing each of its sides to the same - * value. + * We used to think we could remove explicit tests of this + * outer-join qual, too, since we now have tests forcing each of + * its sides to the same value. However, that fails in some + * corner cases where lower outer joins could cause one of the + * variables to go to NULL. (BUG in 8.2 through 8.2.6.) + * So now we just leave it in place, but mark it with selectivity + * 1.0 so that we don't underestimate the join size output --- + * it's mostly redundant with the constant constraints. */ - process_implied_equality(root, - leftop, rightop, - rinfo->left_sortop, rinfo->right_sortop, - rinfo->left_relids, rinfo->right_relids, - true); + rinfo->this_selec = 1.0; /* * And recurse to see if we can deduce anything from INNERVAR = @@ -542,25 +544,22 @@ sub_generate_join_implications(PlannerInfo *root, rinfo->right_sortop, rinfo->right_relids, false); - /* ... and remove COALESCE() = CONSTANT */ - process_implied_const_eq(root, equi_key_set, relids, - item1, - sortop1, - item1_relids, - true); /* - * We can remove explicit tests of this outer-join qual, too, - * since we now have tests forcing each of its sides to the - * same value. + * We used to think we could remove explicit tests of this + * outer-join qual, too, since we now have tests forcing each + * of its sides to the same value. However, that fails in + * some corner cases where lower outer joins could cause one + * of the variables to go to NULL. (BUG in 8.2 through + * 8.2.6.) So now we just leave it in place, but mark it with + * selectivity 1.0 so that we don't underestimate the join + * size output --- it's mostly redundant with the constant + * constraints. + * + * Ideally we'd do that for the COALESCE() = CONSTANT rinfo, + * too, but we don't have easy access to that here. */ - process_implied_equality(root, - leftop, rightop, - rinfo->left_sortop, - rinfo->right_sortop, - rinfo->left_relids, - rinfo->right_relids, - true); + rinfo->this_selec = 1.0; /* * And recurse to see if we can deduce anything from LEFTVAR = diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index 3dd4f5ed7d0..bd33cb5c126 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -2290,3 +2290,34 @@ from yy 301 | | | | 1 (3 rows) +-- +-- regression test for improper pushing of constants across outer-join clauses +-- (as seen in early 8.2.x releases) +-- +create temp table zt1 (f1 int primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt1_pkey" for table "zt1" +create temp table zt2 (f2 int primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt2_pkey" for table "zt2" +create temp table zt3 (f3 int primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt3_pkey" for table "zt3" +insert into zt1 values(53); +insert into zt2 values(53); +select * from + zt2 left join zt3 on (f2 = f3) + left join zt1 on (f3 = f1) +where f2 = 53; + f2 | f3 | f1 +----+----+---- + 53 | | +(1 row) + +create temp view zv1 as select *,'dummy'::text AS junk from zt1; +select * from + zt2 left join zt3 on (f2 = f3) + left join zv1 on (f3 = f1) +where f2 = 53; + f2 | f3 | f1 | junk +----+----+----+------ + 53 | | | +(1 row) + diff --git a/src/test/regress/expected/join_1.out b/src/test/regress/expected/join_1.out index 9e10c73acb7..4690b711402 100644 --- a/src/test/regress/expected/join_1.out +++ b/src/test/regress/expected/join_1.out @@ -2290,3 +2290,34 @@ from yy 301 | | | | 1 (3 rows) +-- +-- regression test for improper pushing of constants across outer-join clauses +-- (as seen in early 8.2.x releases) +-- +create temp table zt1 (f1 int primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt1_pkey" for table "zt1" +create temp table zt2 (f2 int primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt2_pkey" for table "zt2" +create temp table zt3 (f3 int primary key); +NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "zt3_pkey" for table "zt3" +insert into zt1 values(53); +insert into zt2 values(53); +select * from + zt2 left join zt3 on (f2 = f3) + left join zt1 on (f3 = f1) +where f2 = 53; + f2 | f3 | f1 +----+----+---- + 53 | | +(1 row) + +create temp view zv1 as select *,'dummy'::text AS junk from zt1; +select * from + zt2 left join zt3 on (f2 = f3) + left join zv1 on (f3 = f1) +where f2 = 53; + f2 | f3 | f1 | junk +----+----+----+------ + 53 | | | +(1 row) + diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 4efb3c72836..96e15b526c2 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -462,3 +462,26 @@ from yy left join (SELECT * FROM yy where pkyy = 101) as yya ON yy.pkyy = yya.pkyy left join xx xxa on yya.pkxx = xxa.pkxx left join xx xxb on coalesce (xxa.pkxx, 1) = xxb.pkxx; + +-- +-- regression test for improper pushing of constants across outer-join clauses +-- (as seen in early 8.2.x releases) +-- + +create temp table zt1 (f1 int primary key); +create temp table zt2 (f2 int primary key); +create temp table zt3 (f3 int primary key); +insert into zt1 values(53); +insert into zt2 values(53); + +select * from + zt2 left join zt3 on (f2 = f3) + left join zt1 on (f3 = f1) +where f2 = 53; + +create temp view zv1 as select *,'dummy'::text AS junk from zt1; + +select * from + zt2 left join zt3 on (f2 = f3) + left join zv1 on (f3 = f1) +where f2 = 53; |