diff --git a/src/backend/optimizer/plan/subselect.c b/src/backend/optimizer/plan/subselect.c index 1b66906ad1..234a532574 100644 --- a/src/backend/optimizer/plan/subselect.c +++ b/src/backend/optimizer/plan/subselect.c @@ -93,6 +93,7 @@ static bool contain_outer_selfref_walker(Node *node, Index *depth); static void inline_cte(PlannerInfo *root, CommonTableExpr *cte); static bool inline_cte_walker(Node *node, inline_cte_walker_context *context); static bool sublink_testexpr_is_not_nullable(PlannerInfo *root, SubLink *sublink); +static bool sublink_inner_arg_is_param(Node *arg); static int add_antijoin_rte(Query *parse); static bool simplify_EXISTS_query(PlannerInfo *root, Query *query); static Query *convert_EXISTS_to_ANY(PlannerInfo *root, Query *subselect, @@ -1494,6 +1495,13 @@ convert_ANY_sublink_to_join(PlannerInfo *root, SubLink *sublink, * multi-column ordering checks. In all cases, we validate the operators and * the outer expressions. * + * We must also check the inner side of each comparison. The caller verifies + * that the sub-select's output columns cannot be NULL, but the parser may + * have wrapped the corresponding Param in a coercion to the operator's input + * type, and a cast function can return NULL for a non-null input. So we + * insist that each inner argument be the bare Param, possibly under a + * binary-compatible RelabelType. + * * It is acceptable for this check not to be exhaustive. We can err on the * side of conservatism: if we're not sure, it's okay to return FALSE. */ @@ -1524,6 +1532,10 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, SubLink *sublink) if (!op_is_safe_index_member(opexpr->opno)) return false; + /* the inner argument must not be able to go NULL; see above */ + if (!sublink_inner_arg_is_param(lsecond(opexpr->args))) + return false; + outer_exprs = lappend(outer_exprs, linitial(opexpr->args)); } else if (is_andclause(testexpr) || is_orclause(testexpr)) @@ -1544,6 +1556,10 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, SubLink *sublink) if (!op_is_safe_index_member(opexpr->opno)) return false; + /* the inner argument must not be able to go NULL */ + if (!sublink_inner_arg_is_param(lsecond(opexpr->args))) + return false; + outer_exprs = lappend(outer_exprs, linitial(opexpr->args)); } } @@ -1559,6 +1575,13 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, SubLink *sublink) return false; } + /* the inner arguments must not be able to go NULL */ + foreach_ptr(Node, rarg, rcexpr->rargs) + { + if (!sublink_inner_arg_is_param(rarg)) + return false; + } + outer_exprs = list_concat(outer_exprs, rcexpr->largs); } else @@ -1600,6 +1623,24 @@ sublink_testexpr_is_not_nullable(PlannerInfo *root, SubLink *sublink) return true; } +/* + * sublink_inner_arg_is_param: is this inner argument of an ANY_SUBLINK's + * comparison just the sublink's output Param, possibly binary-relabeled? + * + * Helper for sublink_testexpr_is_not_nullable. Anything else (such as an + * implicit coercion applied by the parser) could turn a non-null sub-select + * output into NULL. + */ +static bool +sublink_inner_arg_is_param(Node *arg) +{ + while (arg && IsA(arg, RelabelType)) + arg = (Node *) ((RelabelType *) arg)->arg; + + return arg && IsA(arg, Param) && + ((Param *) arg)->paramkind == PARAM_SUBLINK; +} + /* * convert_EXISTS_sublink_to_join: try to convert an EXISTS SubLink to a join * diff --git a/src/test/regress/expected/subselect.out b/src/test/regress/expected/subselect.out index 6379bd6ca3..8893298199 100644 --- a/src/test/regress/expected/subselect.out +++ b/src/test/regress/expected/subselect.out @@ -4074,4 +4074,86 @@ WHERE COALESCE(t1.id, -1) NOT IN ----+----- (0 rows) +-- No ANTI JOIN: the sub-select's output is coerced to the operator's input +-- type by a cast function, which can return NULL for a non-null input, so a +-- comparison can yield NULL even though both sides are non-nullable +CREATE FUNCTION int4_to_text_nullzero(int4) RETURNS text + AS $$ SELECT CASE WHEN $1 <> 0 THEN format('%s', $1) END $$ + LANGUAGE sql IMMUTABLE STRICT; +CREATE CAST (int4 AS text) WITH FUNCTION int4_to_text_nullzero(int4) AS IMPLICIT; +CREATE TEMP TABLE nn_text_tab (t text NOT NULL, k int NOT NULL); +CREATE TEMP TABLE nn_int_tab (i int NOT NULL, k int NOT NULL); +INSERT INTO nn_text_tab VALUES ('1', 1), ('5', 1); +INSERT INTO nn_int_tab VALUES (0, 1), (1, 1); +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT i FROM nn_int_tab); + QUERY PLAN +----------------------------------------------------------------- + Seq Scan on nn_text_tab + Filter: (NOT (ANY (t = ((hashed SubPlan any_1).col1)::text))) + SubPlan any_1 + -> Seq Scan on nn_int_tab +(4 rows) + +-- '1' matches 1, and '5' is compared with the NULL that 0 is cast to, so no +-- row qualifies +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT i FROM nn_int_tab); + t | k +---+--- +(0 rows) + +-- The cast cannot return NULL here, so '5' qualifies but '1' does not +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT i FROM nn_int_tab WHERE i <> 0); + t | k +---+--- + 5 | 1 +(1 row) + +-- Likewise for the multi-column form +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE (t, k) NOT IN (SELECT i, k FROM nn_int_tab); + QUERY PLAN +--------------------------------------------------------------------------------------------------------- + Seq Scan on nn_text_tab + Filter: (NOT (ANY ((t = ((hashed SubPlan any_1).col1)::text) AND (k = (hashed SubPlan any_1).col2)))) + SubPlan any_1 + -> Seq Scan on nn_int_tab +(4 rows) + +SELECT * FROM nn_text_tab WHERE (t, k) NOT IN (SELECT i, k FROM nn_int_tab); + t | k +---+--- +(0 rows) + +-- Likewise for a row comparison +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE NOT (t, k) > ANY (SELECT i, k FROM nn_int_tab); + QUERY PLAN +--------------------------------------------------------------------------------------------- + Seq Scan on nn_text_tab + Filter: (NOT (ANY (ROW(t, k) > ROW(((SubPlan any_1).col1)::text, (SubPlan any_1).col2)))) + SubPlan any_1 + -> Materialize + -> Seq Scan on nn_int_tab +(5 rows) + +SELECT * FROM nn_text_tab WHERE NOT (t, k) > ANY (SELECT i, k FROM nn_int_tab); + t | k +---+--- +(0 rows) + +-- ANTI JOIN: a binary-compatible relabeling of the sub-select's output cannot +-- produce NULLs +CREATE TEMP TABLE nn_varchar_tab (v varchar NOT NULL); +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT v FROM nn_varchar_tab); + QUERY PLAN +--------------------------------------------------------- + Hash Anti Join + Hash Cond: (nn_text_tab.t = (nn_varchar_tab.v)::text) + -> Seq Scan on nn_text_tab + -> Hash + -> Seq Scan on nn_varchar_tab +(5 rows) + ROLLBACK; diff --git a/src/test/regress/sql/subselect.sql b/src/test/regress/sql/subselect.sql index c6cd275367..38016884d9 100644 --- a/src/test/regress/sql/subselect.sql +++ b/src/test/regress/sql/subselect.sql @@ -1789,4 +1789,45 @@ SELECT * FROM null_tab t1 WHERE COALESCE(t1.id, -1) NOT IN (SELECT t1.val FROM not_null_tab t2 WHERE t2.val IS NOT NULL); +-- No ANTI JOIN: the sub-select's output is coerced to the operator's input +-- type by a cast function, which can return NULL for a non-null input, so a +-- comparison can yield NULL even though both sides are non-nullable +CREATE FUNCTION int4_to_text_nullzero(int4) RETURNS text + AS $$ SELECT CASE WHEN $1 <> 0 THEN format('%s', $1) END $$ + LANGUAGE sql IMMUTABLE STRICT; +CREATE CAST (int4 AS text) WITH FUNCTION int4_to_text_nullzero(int4) AS IMPLICIT; +CREATE TEMP TABLE nn_text_tab (t text NOT NULL, k int NOT NULL); +CREATE TEMP TABLE nn_int_tab (i int NOT NULL, k int NOT NULL); +INSERT INTO nn_text_tab VALUES ('1', 1), ('5', 1); +INSERT INTO nn_int_tab VALUES (0, 1), (1, 1); + +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT i FROM nn_int_tab); + +-- '1' matches 1, and '5' is compared with the NULL that 0 is cast to, so no +-- row qualifies +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT i FROM nn_int_tab); + +-- The cast cannot return NULL here, so '5' qualifies but '1' does not +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT i FROM nn_int_tab WHERE i <> 0); + +-- Likewise for the multi-column form +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE (t, k) NOT IN (SELECT i, k FROM nn_int_tab); + +SELECT * FROM nn_text_tab WHERE (t, k) NOT IN (SELECT i, k FROM nn_int_tab); + +-- Likewise for a row comparison +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE NOT (t, k) > ANY (SELECT i, k FROM nn_int_tab); + +SELECT * FROM nn_text_tab WHERE NOT (t, k) > ANY (SELECT i, k FROM nn_int_tab); + +-- ANTI JOIN: a binary-compatible relabeling of the sub-select's output cannot +-- produce NULLs +CREATE TEMP TABLE nn_varchar_tab (v varchar NOT NULL); + +EXPLAIN (COSTS OFF) +SELECT * FROM nn_text_tab WHERE t NOT IN (SELECT v FROM nn_varchar_tab); + ROLLBACK;