diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 750586fceb..417758f705 100644 --- a/src/backend/optimizer/util/clauses.c +++ b/src/backend/optimizer/util/clauses.c @@ -22,6 +22,7 @@ #include "access/htup_details.h" #include "catalog/pg_aggregate.h" #include "catalog/pg_class.h" +#include "catalog/pg_constraint.h" #include "catalog/pg_language.h" #include "catalog/pg_operator.h" #include "catalog/pg_proc.h" @@ -42,6 +43,7 @@ #include "parser/parse_agg.h" #include "parser/parse_coerce.h" #include "parser/parse_func.h" +#include "parser/parsetree.h" #include "rewrite/rewriteManip.h" #include "tcop/tcopprot.h" #include "utils/acl.h" @@ -90,6 +92,14 @@ typedef struct char *prosrc; } inline_error_callback_arg; +typedef struct check_null_side_state +{ + Relids relids; /* base relids within this subtree */ + bool contains_outer; /* does subtree contain outer join(s)? */ + JoinType jointype; /* type of join */ + List *sub_states; /* List of states for subtree components */ +} check_null_side_state; + typedef struct { char max_hazard; /* worst proparallel hazard found so far */ @@ -156,6 +166,9 @@ static Query *substitute_actual_srf_parameters(Query *expr, int nargs, List *args); static Node *substitute_actual_srf_parameters_mutator(Node *node, substitute_actual_srf_parameters_context *context); +static bool check_null_side(PlannerInfo *root, int relid); +static check_null_side_state * collect_jointree_data(Node *jtnode); + /***************************************************************************** @@ -2245,7 +2258,6 @@ rowtype_field_matches(Oid rowtypeid, int fieldnum, return true; } - /*-------------------- * eval_const_expressions * @@ -2626,6 +2638,7 @@ eval_const_expressions_mutator(Node *node, { has_null_input |= ((Const *) lfirst(arg))->constisnull; all_null_input &= ((Const *) lfirst(arg))->constisnull; + } else has_nonconst_input = true; @@ -3382,7 +3395,47 @@ eval_const_expressions_mutator(Node *node, return makeBoolConst(result, false); } + if (IsA(arg, Var) && + ((Var *) arg)->varlevelsup == 0 && context->root) + { + /* + * Evaluate the test if it is on primary column and the + * relation is not mentioned on nullable side of outer + * join + */ + Var *var = (Var *) arg; + Query *parse = context->root->parse; + int relid; + Bitmapset *pkattnos; + Oid constraintOid; + RangeTblEntry *rte; + relid = var->varno; + rte = rt_fetch(relid, parse->rtable); + pkattnos = get_primary_key_attnos(rte->relid, false, &constraintOid); + + if (pkattnos != NULL && bms_is_member(var->varattno - FirstLowInvalidHeapAttributeNumber, pkattnos) + && !check_null_side(context->root, relid)) + { + bool result; + + switch (ntest->nulltesttype) + { + case IS_NULL: + result = false; + break; + case IS_NOT_NULL: + result = true; + break; + default: + elog(ERROR, "unrecognized nulltesttype: %d", + (int) ntest->nulltesttype); + result = false; /* keep compiler quiet */ + break; + } + return makeBoolConst(result, false); + } + } newntest = makeNode(NullTest); newntest->arg = (Expr *) arg; newntest->nulltesttype = ntest->nulltesttype; @@ -3572,6 +3625,118 @@ eval_const_expressions_mutator(Node *node, return ece_generic_processing(node); } +/* + * Check a relation attributes on nullable side of the outer join. + */ +static bool +check_null_side(PlannerInfo *root, int relid) +{ + check_null_side_state *state; + ListCell *l; + + state = collect_jointree_data((Node *) root->parse->jointree); + + /* if no outer joins the relation is not on nullable side */ + if (state == NULL || !state->contains_outer) + return false; + + /* scan the state and check relation on nullable outer join side */ + foreach(l, state->sub_states) + { + check_null_side_state *sub_state = (check_null_side_state *) lfirst(l); + + if (sub_state->contains_outer) + { + if (sub_state->jointype == JOIN_LEFT) + { + check_null_side_state *right_state = (check_null_side_state *) lsecond(sub_state->sub_states); + + if (bms_is_member(relid, right_state->relids)) + return true; + } + if (sub_state->jointype == JOIN_RIGHT) + { + check_null_side_state *left_state = (check_null_side_state *) linitial(sub_state->sub_states); + + if (bms_is_member(relid, left_state->relids)) + return true; + } + } + } + + return false; +} + + +/* + * Collecting data about join. this function is similar to reduce_outer_joins_pass1 + * + * Returns a state node describing the given jointree node. + */ +static check_null_side_state * +collect_jointree_data(Node *jtnode) +{ + check_null_side_state *result; + + result = (check_null_side_state *) + palloc(sizeof(check_null_side_state)); + result->relids = NULL; + result->contains_outer = false; + result->sub_states = NIL; + + if (jtnode == NULL) + return result; + if (IsA(jtnode, RangeTblRef)) + { + int varno = ((RangeTblRef *) jtnode)->rtindex; + + result->relids = bms_make_singleton(varno); + } + else if (IsA(jtnode, FromExpr)) + { + FromExpr *f = (FromExpr *) jtnode; + ListCell *l; + + foreach(l, f->fromlist) + { + check_null_side_state *sub_state; + + sub_state = collect_jointree_data(lfirst(l)); + result->relids = bms_add_members(result->relids, + sub_state->relids); + result->contains_outer |= sub_state->contains_outer; + result->sub_states = lappend(result->sub_states, sub_state); + } + } + else if (IsA(jtnode, JoinExpr)) + { + JoinExpr *j = (JoinExpr *) jtnode; + check_null_side_state *sub_state; + + /* join's own RT index is not wanted in result->relids */ + if (j->jointype == JOIN_RIGHT || j->jointype == JOIN_LEFT) + result->contains_outer = true; + + sub_state = collect_jointree_data(j->larg); + result->jointype = j->jointype; + result->relids = bms_add_members(result->relids, + sub_state->relids); + sub_state->contains_outer |= sub_state->contains_outer; + result->sub_states = lappend(result->sub_states, sub_state); + + sub_state = collect_jointree_data(j->rarg); + result->jointype = j->jointype; + result->relids = bms_add_members(result->relids, + sub_state->relids); + result->contains_outer |= sub_state->contains_outer; + result->sub_states = lappend(result->sub_states, sub_state); + } + else + elog(ERROR, "unrecognized node type: %d", + (int) nodeTag(jtnode)); + return result; +} + /* * Subroutine for eval_const_expressions: check for non-Const nodes. * diff --git a/src/test/regress/expected/join.out b/src/test/regress/expected/join.out index a46b1573bd..a42f58c9f8 100644 --- a/src/test/regress/expected/join.out +++ b/src/test/regress/expected/join.out @@ -4577,6 +4577,32 @@ SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0 1 | (1 row) +explain (costs off) +SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); + QUERY PLAN +------------------------------------------ + Hash Left Join + Hash Cond: (b.a_id = a.id) + Filter: ((a.id IS NULL) OR (a.id > 0)) + -> Seq Scan on b + -> Hash + -> Seq Scan on a +(6 rows) + +explain (costs off) +SELECT * FROM b RIGHT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); + QUERY PLAN +----------------------------------------------- + Hash Right Join + Hash Cond: (b.a_id = a.id) + -> Seq Scan on b + -> Hash + -> Bitmap Heap Scan on a + Recheck Cond: (id > 0) + -> Bitmap Index Scan on a_pkey + Index Cond: (id > 0) +(8 rows) + rollback; -- another join removal bug: this is not optimizable, either begin; diff --git a/src/test/regress/expected/select.out b/src/test/regress/expected/select.out index c441049f41..1514b5a05a 100644 --- a/src/test/regress/expected/select.out +++ b/src/test/regress/expected/select.out @@ -903,6 +903,22 @@ select unique1, unique2 from onek2 0 | 998 (2 rows) +CREATE TEMP TABLE a (id int PRIMARY KEY); +explain (costs off) +SELECT * FROM a WHERE id IS NULL; + QUERY PLAN +-------------------------- + Result + One-Time Filter: false +(2 rows) + +explain (costs off) +SELECT * FROM a WHERE id IS NOT NULL; + QUERY PLAN +--------------- + Seq Scan on a +(1 row) + -- -- Test some corner cases that have been known to confuse the planner -- diff --git a/src/test/regress/sql/join.sql b/src/test/regress/sql/join.sql index 1403e0ffe7..4146562c3b 100644 --- a/src/test/regress/sql/join.sql +++ b/src/test/regress/sql/join.sql @@ -1589,6 +1589,11 @@ INSERT INTO b VALUES (0, 0), (1, NULL); SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); SELECT b.* FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); +explain (costs off) +SELECT * FROM b LEFT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); + +explain (costs off) +SELECT * FROM b RIGHT JOIN a ON (b.a_id = a.id) WHERE (a.id IS NULL OR a.id > 0); rollback; -- another join removal bug: this is not optimizable, either diff --git a/src/test/regress/sql/select.sql b/src/test/regress/sql/select.sql index b5929b2eca..6c4f4f1af8 100644 --- a/src/test/regress/sql/select.sql +++ b/src/test/regress/sql/select.sql @@ -234,6 +234,13 @@ select unique1, unique2 from onek2 select unique1, unique2 from onek2 where (unique2 = 11 and stringu1 < 'B') or unique1 = 0; +CREATE TEMP TABLE a (id int PRIMARY KEY); +explain (costs off) +SELECT * FROM a WHERE id IS NULL; + +explain (costs off) +SELECT * FROM a WHERE id IS NOT NULL; + -- -- Test some corner cases that have been known to confuse the planner --