delta.txt
text/plain
diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c
index 0ef70ad7f1..df6d706770 100644
--- a/src/backend/optimizer/path/indxpath.c
+++ b/src/backend/optimizer/path/indxpath.c
@@ -3498,8 +3498,24 @@ bool
relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
List *restrictlist,
List *exprlist, List *oprlist)
+{
+ return relation_has_unique_index_ext(root, rel, restrictlist,
+ exprlist, oprlist, NULL);
+}
+
+/*
+ * relation_has_unique_index_ext
+ * Used as the relation_has_unique_index_for, but if extra_clauses doesn't NULL,
+ * return baserestrictinfo clauses which were used to derive uniqueness.
+ */
+bool
+relation_has_unique_index_ext(PlannerInfo *root, RelOptInfo *rel,
+ List *restrictlist,
+ List *exprlist, List *oprlist,
+ List **extra_clauses)
{
ListCell *ic;
+ List *exprs;
Assert(list_length(exprlist) == list_length(oprlist));
@@ -3554,6 +3570,8 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
IndexOptInfo *ind = (IndexOptInfo *) lfirst(ic);
int c;
+ exprs = NIL;
+
/*
* If the index is not unique, or not immediately enforced, or if it's
* a partial index that doesn't match the query, it's useless here.
@@ -3600,7 +3618,16 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
if (match_index_to_operand(rexpr, c, ind))
{
+ MemoryContext oldContext = MemoryContextSwitchTo(root->planner_cxt);
+
matched = true; /* column is unique */
+
+ /* Store a filter for subsequent operations. */
+ if (bms_is_empty(rinfo->left_relids) ||
+ bms_is_empty(rinfo->right_relids))
+ exprs = lappend(exprs, rinfo);
+
+ MemoryContextSwitchTo(oldContext);
break;
}
}
@@ -3643,7 +3670,11 @@ relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
/* Matched all key columns of this index? */
if (c == ind->nkeycolumns)
+ {
+ if (extra_clauses)
+ *extra_clauses = exprs;
return true;
+ }
}
return false;
diff --git a/src/backend/optimizer/plan/analyzejoins.c b/src/backend/optimizer/plan/analyzejoins.c
index bdd9effd38..68edc9d2d7 100644
--- a/src/backend/optimizer/plan/analyzejoins.c
+++ b/src/backend/optimizer/plan/analyzejoins.c
@@ -33,6 +33,19 @@
#include "optimizer/tlist.h"
#include "utils/lsyscache.h"
+/*
+ * UniqueRelInfo caches a fact that a relation is unique when being joined
+ * to other relation(s) specified by outerrelids.
+ * extra_clauses contains additional clauses from a baserestrictinfo list that
+ * were used to prove uniqueness. We cache it for SJ checking procedure: SJ can
+ * be removed if outer relation contains strictly the same set of clauses.
+ */
+typedef struct UniqueRelInfo
+{
+ Relids outerrelids;
+ List *extra_clauses;
+} UniqueRelInfo;
+
bool enable_self_join_removal;
/* local functions */
@@ -42,14 +55,15 @@ static void remove_rel_from_query(PlannerInfo *root, int relid,
static List *remove_rel_from_joinlist(List *joinlist, int relid, int *nremoved);
static bool rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel);
static bool rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel,
- List *clause_list);
+ List *clause_list, List **extra_clauses);
static Oid distinct_col_search(int colno, List *colnos, List *opids);
static bool is_innerrel_unique_for(PlannerInfo *root,
Relids joinrelids,
Relids outerrelids,
RelOptInfo *innerrel,
JoinType jointype,
- List *restrictlist);
+ List *restrictlist,
+ List **extra_clauses);
static void change_rinfo(RestrictInfo* rinfo, Index from, Index to);
static Bitmapset* change_relid(Relids relids, Index oldId, Index newId);
static void change_varno(Expr *expr, Index oldRelid, Index newRelid);
@@ -293,7 +307,7 @@ join_is_removable(PlannerInfo *root, SpecialJoinInfo *sjinfo)
* Now that we have the relevant equality join clauses, try to prove the
* innerrel distinct.
*/
- if (rel_is_distinct_for(root, innerrel, clause_list))
+ if (rel_is_distinct_for(root, innerrel, clause_list, NULL))
return true;
/*
@@ -669,9 +683,14 @@ rel_supports_distinctness(PlannerInfo *root, RelOptInfo *rel)
* Note that the passed-in clause_list may be destructively modified! This
* is OK for current uses, because the clause_list is built by the caller for
* the sole purpose of passing to this function.
+ *
+ * outer_exprs contains right sides of baserestrictinfo clauses looks like
+ * x = const if distinctness is derived from such clauses, not joininfo clause.
+ * Pass NULL for the param value, if it is not interested.
*/
static bool
-rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list)
+rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list,
+ List **extra_clauses)
{
/*
* We could skip a couple of tests here if we assume all callers checked
@@ -684,10 +703,11 @@ rel_is_distinct_for(PlannerInfo *root, RelOptInfo *rel, List *clause_list)
{
/*
* Examine the indexes to see if we have a matching unique index.
- * relation_has_unique_index_for automatically adds any usable
+ * relation_has_unique_index_ext automatically adds any usable
* restriction clauses for the rel, so we needn't do that here.
*/
- if (relation_has_unique_index_for(root, rel, clause_list, NIL, NIL))
+ if (relation_has_unique_index_ext(root, rel, clause_list, NIL, NIL,
+ extra_clauses))
return true;
}
else if (rel->rtekind == RTE_SUBQUERY)
@@ -1001,9 +1021,21 @@ innerrel_is_unique(PlannerInfo *root,
JoinType jointype,
List *restrictlist,
bool force_cache)
+{
+ return innerrel_is_unique_ext(root, joinrelids, outerrelids, innerrel,
+ jointype, restrictlist, force_cache, NULL);
+}
+
+bool
+innerrel_is_unique_ext(PlannerInfo *root, Relids joinrelids, Relids outerrelids,
+ RelOptInfo *innerrel, JoinType jointype,
+ List *restrictlist, bool force_cache,
+ List **extra_clauses)
{
MemoryContext old_context;
ListCell *lc;
+ UniqueRelInfo *uniqueRelInfo;
+ List *outer_exprs = NIL;
/* Certainly can't prove uniqueness when there are no joinclauses */
if (restrictlist == NIL)
@@ -1025,10 +1057,14 @@ innerrel_is_unique(PlannerInfo *root,
*/
foreach(lc, innerrel->unique_for_rels)
{
- Relids unique_for_rels = (Relids) lfirst(lc);
+ uniqueRelInfo = (UniqueRelInfo *) lfirst(lc);
- if (bms_is_subset(unique_for_rels, outerrelids))
+ if (bms_is_subset(uniqueRelInfo->outerrelids, outerrelids))
+ {
+ if (extra_clauses)
+ *extra_clauses = uniqueRelInfo->extra_clauses;
return true; /* Success! */
+ }
}
/*
@@ -1045,7 +1081,7 @@ innerrel_is_unique(PlannerInfo *root,
/* No cached information, so try to make the proof. */
if (is_innerrel_unique_for(root, joinrelids, outerrelids, innerrel,
- jointype, restrictlist))
+ jointype, restrictlist, &outer_exprs))
{
/*
* Cache the positive result for future probes, being sure to keep it
@@ -1058,10 +1094,15 @@ innerrel_is_unique(PlannerInfo *root,
* supersets of them anyway.
*/
old_context = MemoryContextSwitchTo(root->planner_cxt);
+ uniqueRelInfo = palloc(sizeof(UniqueRelInfo));
+ uniqueRelInfo->extra_clauses = outer_exprs;
+ uniqueRelInfo->outerrelids = bms_copy(outerrelids);
innerrel->unique_for_rels = lappend(innerrel->unique_for_rels,
- bms_copy(outerrelids));
+ uniqueRelInfo);
MemoryContextSwitchTo(old_context);
+ if (extra_clauses)
+ *extra_clauses = outer_exprs;
return true; /* Success! */
}
else
@@ -1107,7 +1148,8 @@ is_innerrel_unique_for(PlannerInfo *root,
Relids outerrelids,
RelOptInfo *innerrel,
JoinType jointype,
- List *restrictlist)
+ List *restrictlist,
+ List **extra_clauses)
{
List *clause_list = NIL;
ListCell *lc;
@@ -1149,7 +1191,7 @@ is_innerrel_unique_for(PlannerInfo *root,
}
/* Let rel_is_distinct_for() do the hard work */
- return rel_is_distinct_for(root, innerrel, clause_list);
+ return rel_is_distinct_for(root, innerrel, clause_list, extra_clauses);
}
typedef struct ChangeVarnoContext
@@ -1395,6 +1437,7 @@ remove_self_join_rel(PlannerInfo *root, PlanRowMark *kmark, PlanRowMark *rmark,
if (leftOp != NULL && equal(leftOp, rightOp))
{
NullTest *nullTest = makeNode(NullTest);
+
nullTest->arg = leftOp;
nullTest->nulltesttype = IS_NOT_NULL;
nullTest->argisrow = false;
@@ -1638,6 +1681,70 @@ split_selfjoin_quals(PlannerInfo *root, List *joinquals, List **selfjoinquals,
*otherjoinquals = ojoinquals;
}
+/*
+ * Check for a case when uniqueness [partly] derived from a baserestrictinfo
+ * clause. In this case we have a chance to return the only one row (if such
+ * clauses on both sides of SJ is equal) or nothing - if they are different.
+ */
+static bool
+degenerate_case(RelOptInfo *outer, List *uclauses, Index relid)
+{
+ ListCell *lc;
+
+ foreach(lc, uclauses)
+ {
+ RestrictInfo *rinfo = lfirst_node(RestrictInfo, lc);
+ Expr *clause;
+ Const *c1;
+ bool matched = false;
+ ListCell *olc;
+
+ /* Only filters like x == const we should consider. */
+ Assert(bms_is_empty(rinfo->left_relids) ||
+ bms_is_empty(rinfo->right_relids));
+
+ clause = (Expr *) (rinfo->outer_is_left ?
+ get_rightop(rinfo->clause) : get_leftop(rinfo->clause));
+ c1 = (Const *) (rinfo->outer_is_left ?
+ get_leftop(rinfo->clause) : get_rightop(rinfo->clause));
+ Assert(IsA(c1, Const) && outer->relid > 0 && relid > 0);
+
+ clause = (Expr *) copyObject(clause);
+ change_varno(clause, relid, outer->relid);
+
+ /*
+ * Compare these left and right sides with corresponding sides of
+ * the outer's filters. If no one detected - return immediately.
+ */
+ foreach(olc, outer->baserestrictinfo)
+ {
+ RestrictInfo *orinfo = lfirst_node(RestrictInfo, olc);
+ Expr *oclause;
+ Expr *c2;
+
+ if (orinfo->mergeopfamilies == NIL)
+ /* Don't consider clauses which aren't similar to 'F(X)=G(Y)' */
+ continue;
+
+ oclause = (Expr *) (bms_is_empty(orinfo->left_relids) ?
+ get_rightop(orinfo->clause) : get_leftop(orinfo->clause));
+ c2 = (Expr *) (bms_is_empty(orinfo->left_relids) ?
+ get_leftop(orinfo->clause) : get_rightop(orinfo->clause));
+
+ if (equal(clause, oclause) && equal(c1,c2))
+ {
+ matched = true;
+ break;
+ }
+ }
+
+ if (!matched)
+ return false;
+ }
+
+ return true;
+}
+
/*
* Find and remove unique self joins in a group of base relations that have
* the same Oid.
@@ -1663,13 +1770,14 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
while ((k = bms_next_member(relids, k)) > 0)
{
RelOptInfo *inner = root->simple_rel_array[k];
- List *restrictlist;
- List *selfjoinquals;
- List *otherjoinquals;
+ List *restrictlist;
+ List *selfjoinquals;
+ List *otherjoinquals;
ListCell *lc;
bool jinfo_check = true;
PlanRowMark *omark = NULL;
PlanRowMark *imark = NULL;
+ List *uclauses = NIL;
/* A sanity check: the relations have the same Oid. */
Assert(root->simple_rte_array[k]->relid ==
@@ -1745,9 +1853,13 @@ remove_self_joins_one_group(PlannerInfo *root, Relids relids)
* join quals are selfjoin quals. Otherwise we could end up
* putting false negatives in the cache.
*/
- if (!innerrel_is_unique(root, joinrelids, outer->relids,
- inner, JOIN_INNER, selfjoinquals,
- list_length(otherjoinquals) == 0))
+ if (!innerrel_is_unique_ext(root, joinrelids, outer->relids,
+ inner, JOIN_INNER, selfjoinquals,
+ list_length(otherjoinquals) == 0,
+ &uclauses))
+ continue;
+
+ if (!degenerate_case(outer, uclauses, inner->relid))
continue;
/* See for row marks. */
diff --git a/src/include/optimizer/paths.h b/src/include/optimizer/paths.h
index 3d95e6bfc8..b93bc29f0f 100644
--- a/src/include/optimizer/paths.h
+++ b/src/include/optimizer/paths.h
@@ -76,6 +76,9 @@ extern void create_index_paths(PlannerInfo *root, RelOptInfo *rel);
extern bool relation_has_unique_index_for(PlannerInfo *root, RelOptInfo *rel,
List *restrictlist,
List *exprlist, List *oprlist);
+extern bool relation_has_unique_index_ext(PlannerInfo *root, RelOptInfo *rel,
+ List *restrictlist, List *exprlist,
+ List *oprlist, List **extra_clauses);
extern bool indexcol_is_bool_constant_for_query(PlannerInfo *root,
IndexOptInfo *index,
int indexcol);
diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h
index 86247cb9ce..ef963e4bcf 100644
--- a/src/include/optimizer/planmain.h
+++ b/src/include/optimizer/planmain.h
@@ -108,6 +108,10 @@ extern bool query_is_distinct_for(Query *query, List *colnos, List *opids);
extern bool innerrel_is_unique(PlannerInfo *root,
Relids joinrelids, Relids outerrelids, RelOptInfo *innerrel,
JoinType jointype, List *restrictlist, bool force_cache);
+extern bool innerrel_is_unique_ext(PlannerInfo *root, Relids joinrelids,
+ Relids outerrelids, RelOptInfo *innerrel,
+ JoinType jointype, List *restrictlist,
+ bool force_cache, List **uclauses);
extern List * remove_useless_self_joins(PlannerInfo *root, List *jointree);
/*
diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out
index 9d0674285c..ae0bec82ae 100644
--- a/src/test/regress/expected/join.out
+++ b/src/test/regress/expected/join.out
@@ -5099,11 +5099,165 @@ on true;
-> Seq Scan on int8_tbl y
(7 rows)
+--
+-- SJR corner case: uniqueness of an inner is [partially] derived from
+-- baserestrictinfo clauses.
+-- XXX: We really should allow SJR for these corner cases?
+--
+INSERT INTO sj VALUES (3, 1, 3);
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
+ QUERY PLAN
+------------------------------
+ Nested Loop
+ Join Filter: (j1.b = j2.b)
+ -> Seq Scan on sj j1
+ Filter: (a = 2)
+ -> Seq Scan on sj j2
+ Filter: (a = 3)
+(6 rows)
+
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; -- Return one column
+ a | b | c | a | b | c
+---+---+---+---+---+---
+ 2 | 1 | 1 | 3 | 1 | 3
+(1 row)
+
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on sj j2
+ Filter: ((b IS NOT NULL) AND (a = 2))
+(2 rows)
+
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; -- Return one column
+ a | b | c | a | b | c
+---+---+---+---+---+---
+ 2 | 1 | 1 | 2 | 1 | 1
+(1 row)
+
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on sj j2
+ Filter: ((b IS NOT NULL) AND (a = 1))
+(2 rows)
+
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; -- Return no columns
+ a | b | c | a | b | c
+---+---+---+---+---+---
+(0 rows)
+
+explain (costs off) -- Shuffle a clause. Remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
+ QUERY PLAN
+-----------------------------------------
+ Seq Scan on sj j2
+ Filter: ((b IS NOT NULL) AND (a = 1))
+(2 rows)
+
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; -- Return no columns
+ a | b | c | a | b | c
+---+---+---+---+---+---
+(0 rows)
+
+-- Functional index
+CREATE UNIQUE INDEX sj_fn_idx ON sj((a * a));
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 1;
+ QUERY PLAN
+-----------------------------------------------
+ Seq Scan on sj j2
+ Filter: ((b IS NOT NULL) AND ((a * a) = 1))
+(2 rows)
+
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 2;
+ QUERY PLAN
+-------------------------------
+ Nested Loop
+ Join Filter: (j1.b = j2.b)
+ -> Seq Scan on sj j1
+ Filter: ((a * a) = 1)
+ -> Seq Scan on sj j2
+ Filter: ((a * a) = 2)
+(6 rows)
+
+-- Multiple filters
+CREATE UNIQUE INDEX sj_temp_idx1 ON sj(a,b,c);
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND j1.a = 2 AND j1.c = 3 AND j2.a = 2 AND 3 = j2.c;
+ QUERY PLAN
+-----------------------------------------------------
+ Seq Scan on sj j2
+ Filter: ((b IS NOT NULL) AND (a = 2) AND (c = 3))
+(2 rows)
+
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND 2 = j1.a AND j1.c = 3 AND j2.a = 1 AND 3 = j2.c;
+ QUERY PLAN
+---------------------------------------
+ Nested Loop
+ Join Filter: (j1.b = j2.b)
+ -> Seq Scan on sj j1
+ Filter: ((2 = a) AND (c = 3))
+ -> Seq Scan on sj j2
+ Filter: ((c = 3) AND (a = 1))
+(6 rows)
+
+CREATE UNIQUE INDEX sj_temp_idx ON sj(a,b);
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2;
+ QUERY PLAN
+------------------------------
+ Nested Loop
+ Join Filter: (j1.b = j2.b)
+ -> Seq Scan on sj j1
+ Filter: (a = 2)
+ -> Seq Scan on sj j2
+(5 rows)
+
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a;
+ QUERY PLAN
+------------------------------
+ Nested Loop
+ Join Filter: (j1.b = j2.b)
+ -> Seq Scan on sj j2
+ Filter: (2 = a)
+ -> Seq Scan on sj j1
+(5 rows)
+
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1);
+ QUERY PLAN
+---------------------------------------------------------------
+ Nested Loop
+ Join Filter: ((j1.b = j2.b) AND ((j1.a = 1) OR (j2.a = 1)))
+ -> Seq Scan on sj j1
+ -> Materialize
+ -> Seq Scan on sj j2
+(5 rows)
+
+DROP INDEX sj_fn_idx, sj_temp_idx1, sj_temp_idx;
-- Test that OR predicated are updated correctly after join removal
CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT);
CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag);
explain (costs off)
-SELECT COUNT(*) FROM tab_with_flag WHERE (is_flag IS NULL OR is_flag = 0) AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
+SELECT COUNT(*) FROM tab_with_flag
+WHERE
+ (is_flag IS NULL OR is_flag = 0)
+ AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
QUERY PLAN
----------------------------------------------------------------------------------
Aggregate
@@ -5240,17 +5394,51 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
-- We can remove the join even if we find the join can't duplicate rows and
-- the base quals of each side are different. In the following case we end up
-- moving quals over to s1 to make it so it can't match any rows.
-create table sl(a int, b int);
+create table sl(a int, b int, c int);
create unique index on sl(a, b);
vacuum analyze sl;
-- Both sides are unique, but base quals are different
explain (costs off)
select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1 and t2.b = 2;
- QUERY PLAN
------------------------------------------------------
- Seq Scan on sl t2
- Filter: ((a IS NOT NULL) AND (b = 2) AND (b = 1))
-(2 rows)
+ QUERY PLAN
+------------------------------
+ Nested Loop
+ Join Filter: (t1.a = t2.a)
+ -> Seq Scan on sl t1
+ Filter: (b = 1)
+ -> Seq Scan on sl t2
+ Filter: (b = 2)
+(6 rows)
+
+-- Check NullTest in baserestrictinfo list
+explain (costs off)
+select * from sl t1, sl t2
+where t1.a = t2.a and t1.b = 1 and t2.b = 2
+ and t1.c IS NOT NULL and t2.c IS NOT NULL
+ and t2.b IS NOT NULL and t1.b IS NOT NULL
+ and t1.a IS NOT NULL and t2.a IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Nested Loop
+ Join Filter: (t1.a = t2.a)
+ -> Seq Scan on sl t1
+ Filter: ((c IS NOT NULL) AND (b IS NOT NULL) AND (a IS NOT NULL) AND (b = 1))
+ -> Seq Scan on sl t2
+ Filter: ((c IS NOT NULL) AND (b IS NOT NULL) AND (a IS NOT NULL) AND (b = 2))
+(6 rows)
+
+explain (verbose, costs off)
+select * from sl t1, sl t2
+where t1.b = t2.b and t2.a = 3 and t1.a = 3
+ and t1.c IS NOT NULL and t2.c IS NOT NULL
+ and t2.b IS NOT NULL and t1.b IS NOT NULL
+ and t1.a IS NOT NULL and t2.a IS NOT NULL;
+ QUERY PLAN
+---------------------------------------------------------------------------------------------
+ Seq Scan on public.sl t2
+ Output: t2.a, t2.b, t2.c, t2.a, t2.b, t2.c
+ Filter: ((t2.c IS NOT NULL) AND (t2.b IS NOT NULL) AND (t2.a IS NOT NULL) AND (t2.a = 3))
+(3 rows)
-- Join qual isn't mergejoinable, but inner is unique.
explain (COSTS OFF)
diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql
index 53d0aa98eb..d132eedf18 100644
--- a/src/test/regress/sql/join.sql
+++ b/src/test/regress/sql/join.sql
@@ -1829,11 +1829,69 @@ left join (select coalesce(y.q1, 1) from int8_tbl y
on true) z
on true;
+--
+-- SJR corner case: uniqueness of an inner is [partially] derived from
+-- baserestrictinfo clauses.
+-- XXX: We really should allow SJR for these corner cases?
+--
+
+INSERT INTO sj VALUES (3, 1, 3);
+
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3;
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 3; -- Return one column
+
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2;
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND j1.a = 2 AND j2.a = 2; -- Return one column
+
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1;
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND j1.a = 1 AND j2.a = 1; -- Return no columns
+
+explain (costs off) -- Shuffle a clause. Remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1;
+SELECT * FROM sj j1, sj j2
+WHERE j1.b = j2.b AND 1 = j1.a AND j2.a = 1; -- Return no columns
+
+-- Functional index
+CREATE UNIQUE INDEX sj_fn_idx ON sj((a * a));
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 1;
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND j1.a*j1.a = 1 AND j2.a*j2.a = 2;
+
+-- Multiple filters
+CREATE UNIQUE INDEX sj_temp_idx1 ON sj(a,b,c);
+explain (costs off) -- Remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND j1.a = 2 AND j1.c = 3 AND j2.a = 2 AND 3 = j2.c;
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2
+ WHERE j1.b = j2.b AND 2 = j1.a AND j1.c = 3 AND j2.a = 1 AND 3 = j2.c;
+
+CREATE UNIQUE INDEX sj_temp_idx ON sj(a,b);
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND j1.a = 2;
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND 2 = j2.a;
+explain (costs off) -- Don't remove SJ
+ SELECT * FROM sj j1, sj j2 WHERE j1.b = j2.b AND (j1.a = 1 OR j2.a = 1);
+DROP INDEX sj_fn_idx, sj_temp_idx1, sj_temp_idx;
+
-- Test that OR predicated are updated correctly after join removal
CREATE TABLE tab_with_flag ( id INT PRIMARY KEY, is_flag SMALLINT);
CREATE INDEX idx_test_is_flag ON tab_with_flag (is_flag);
explain (costs off)
-SELECT COUNT(*) FROM tab_with_flag WHERE (is_flag IS NULL OR is_flag = 0) AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
+SELECT COUNT(*) FROM tab_with_flag
+WHERE
+ (is_flag IS NULL OR is_flag = 0)
+ AND id IN (SELECT id FROM tab_with_flag WHERE id IN (2, 3));
DROP TABLE tab_with_flag;
-- HAVING clause
@@ -1886,7 +1944,7 @@ SELECT * FROM emp1 e1, emp1 e2 WHERE e1.id = e2.id AND e2.code <> e1.code;
-- We can remove the join even if we find the join can't duplicate rows and
-- the base quals of each side are different. In the following case we end up
-- moving quals over to s1 to make it so it can't match any rows.
-create table sl(a int, b int);
+create table sl(a int, b int, c int);
create unique index on sl(a, b);
vacuum analyze sl;
@@ -1894,6 +1952,20 @@ vacuum analyze sl;
explain (costs off)
select * from sl t1, sl t2 where t1.a = t2.a and t1.b = 1 and t2.b = 2;
+-- Check NullTest in baserestrictinfo list
+explain (costs off)
+select * from sl t1, sl t2
+where t1.a = t2.a and t1.b = 1 and t2.b = 2
+ and t1.c IS NOT NULL and t2.c IS NOT NULL
+ and t2.b IS NOT NULL and t1.b IS NOT NULL
+ and t1.a IS NOT NULL and t2.a IS NOT NULL;
+explain (verbose, costs off)
+select * from sl t1, sl t2
+where t1.b = t2.b and t2.a = 3 and t1.a = 3
+ and t1.c IS NOT NULL and t2.c IS NOT NULL
+ and t2.b IS NOT NULL and t1.b IS NOT NULL
+ and t1.a IS NOT NULL and t2.a IS NOT NULL;
+
-- Join qual isn't mergejoinable, but inner is unique.
explain (COSTS OFF)
SELECT n2.a FROM sj n1, sj n2 WHERE n1.a <> n2.a AND n2.a = 1;