diff --git a/src/backend/executor/execQual.c b/src/backend/executor/execQual.c index 80f08d8..c5df8ba 100644 *** a/src/backend/executor/execQual.c --- b/src/backend/executor/execQual.c *************** ExecEvalScalarArrayOp(ScalarArrayOpExprS *** 2385,2391 **** char *s; bits8 *bitmap; int bitmask; ! /* Set default values for result flags: non-null, not a set result */ *isNull = false; if (isDone) --- 2385,2391 ---- char *s; bits8 *bitmap; int bitmask; ! /* Set default values for result flags: non-null, not a set result */ *isNull = false; if (isDone) *************** ExecEvalScalarArrayOp(ScalarArrayOpExprS *** 2416,2428 **** * If the array is NULL then we return NULL --- it's not very meaningful * to do anything else, even if the operator isn't strict. */ ! if (fcinfo->argnull[1]) { *isNull = true; return (Datum) 0; } /* Else okay to fetch and detoast the array */ ! arr = DatumGetArrayTypeP(fcinfo->arg[1]); /* * If the array is empty, we return either FALSE or TRUE per the useOr --- 2416,2428 ---- * If the array is NULL then we return NULL --- it's not very meaningful * to do anything else, even if the operator isn't strict. */ ! if (fcinfo->argnull[opexpr->aryArgIdx]) { *isNull = true; return (Datum) 0; } /* Else okay to fetch and detoast the array */ ! arr = DatumGetArrayTypeP(fcinfo->arg[opexpr->aryArgIdx]); /* * If the array is empty, we return either FALSE or TRUE per the useOr *************** ExecEvalScalarArrayOp(ScalarArrayOpExprS *** 2438,2444 **** * If the scalar is NULL, and the function is strict, return NULL; no * point in iterating the loop. */ ! if (fcinfo->argnull[0] && sstate->fxprstate.func.fn_strict) { *isNull = true; return (Datum) 0; --- 2438,2445 ---- * If the scalar is NULL, and the function is strict, return NULL; no * point in iterating the loop. */ ! if (fcinfo->argnull[1-opexpr->aryArgIdx] && ! sstate->fxprstate.func.fn_strict) { *isNull = true; return (Datum) 0; *************** ExecEvalScalarArrayOp(ScalarArrayOpExprS *** 2476,2495 **** /* Get array element, checking for NULL */ if (bitmap && (*bitmap & bitmask) == 0) { ! fcinfo->arg[1] = (Datum) 0; ! fcinfo->argnull[1] = true; } else { elt = fetch_att(s, typbyval, typlen); s = att_addlength_pointer(s, typlen, s); s = (char *) att_align_nominal(s, typalign); ! fcinfo->arg[1] = elt; ! fcinfo->argnull[1] = false; } /* Call comparison function */ ! if (fcinfo->argnull[1] && sstate->fxprstate.func.fn_strict) { fcinfo->isnull = true; thisresult = (Datum) 0; --- 2477,2497 ---- /* Get array element, checking for NULL */ if (bitmap && (*bitmap & bitmask) == 0) { ! fcinfo->arg[opexpr->aryArgIdx] = (Datum) 0; ! fcinfo->argnull[opexpr->aryArgIdx] = true; } else { elt = fetch_att(s, typbyval, typlen); s = att_addlength_pointer(s, typlen, s); s = (char *) att_align_nominal(s, typalign); ! fcinfo->arg[opexpr->aryArgIdx] = elt; ! fcinfo->argnull[opexpr->aryArgIdx] = false; } /* Call comparison function */ ! if (fcinfo->argnull[opexpr->aryArgIdx] && ! sstate->fxprstate.func.fn_strict) { fcinfo->isnull = true; thisresult = (Datum) 0; diff --git a/src/backend/executor/nodeIndexscan.c b/src/backend/executor/nodeIndexscan.c index dbc1467..4ac8363 100644 *** a/src/backend/executor/nodeIndexscan.c --- b/src/backend/executor/nodeIndexscan.c *************** ExecIndexBuildScanKeys(PlanState *planst *** 982,987 **** --- 982,989 ---- ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause; Assert(!isorderby); + + Assert(saop->aryArgIdx == 1); Assert(saop->useOr); opno = saop->opno; diff --git a/src/backend/executor/nodeTidscan.c b/src/backend/executor/nodeTidscan.c index aae41bf..a4dd583 100644 *** a/src/backend/executor/nodeTidscan.c --- b/src/backend/executor/nodeTidscan.c *************** TidListCreate(TidScanState *tidstate) *** 124,129 **** --- 124,130 ---- } else if (expr && IsA(expr, ScalarArrayOpExpr)) { + ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) expr; ScalarArrayOpExprState *saexstate = (ScalarArrayOpExprState *) exstate; Datum arraydatum; ArrayType *itemarray; *************** TidListCreate(TidScanState *tidstate) *** 131,138 **** bool *ipnulls; int ndatums; int i; ! ! exstate = (ExprState *) lsecond(saexstate->fxprstate.args); arraydatum = ExecEvalExprSwitchContext(exstate, econtext, &isNull, --- 132,140 ---- bool *ipnulls; int ndatums; int i; ! ! exstate = (ExprState *) list_nth(saexstate->fxprstate.args, ! saop->aryArgIdx); arraydatum = ExecEvalExprSwitchContext(exstate, econtext, &isNull, diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c index c9133dd..cb5590b 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyScalarArrayOpExpr(ScalarArrayOpExpr *** 1280,1285 **** --- 1280,1286 ---- COPY_SCALAR_FIELD(opfuncid); COPY_SCALAR_FIELD(useOr); COPY_SCALAR_FIELD(inputcollid); + COPY_SCALAR_FIELD(aryArgIdx); COPY_NODE_FIELD(args); COPY_LOCATION_FIELD(location); diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c index 3a0267c..9bdb879 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** _equalScalarArrayOpExpr(ScalarArrayOpExp *** 359,364 **** --- 359,365 ---- COMPARE_SCALAR_FIELD(useOr); COMPARE_SCALAR_FIELD(inputcollid); + COMPARE_SCALAR_FIELD(aryArgIdx); COMPARE_NODE_FIELD(args); COMPARE_LOCATION_FIELD(location); diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c index 681f5f8..92a4f03 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** _outScalarArrayOpExpr(StringInfo str, Sc *** 1071,1076 **** --- 1071,1077 ---- WRITE_OID_FIELD(opfuncid); WRITE_BOOL_FIELD(useOr); WRITE_OID_FIELD(inputcollid); + WRITE_INT_FIELD(aryArgIdx); WRITE_NODE_FIELD(args); WRITE_LOCATION_FIELD(location); } *************** _outAExpr(StringInfo str, A_Expr *node) *** 2372,2382 **** --- 2373,2393 ---- WRITE_NODE_FIELD(name); appendStringInfo(str, " ANY "); break; + case AEXPR_ANY_OP: + appendStringInfo(str, " "); + appendStringInfo(str, " ANY "); + WRITE_NODE_FIELD(name); + break; case AEXPR_OP_ALL: appendStringInfo(str, " "); WRITE_NODE_FIELD(name); appendStringInfo(str, " ALL "); break; + case AEXPR_ALL_OP: + appendStringInfo(str, " "); + appendStringInfo(str, " ALL "); + WRITE_NODE_FIELD(name); + break; case AEXPR_DISTINCT: appendStringInfo(str, " DISTINCT "); WRITE_NODE_FIELD(name); diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c index 2288514..cb96f72 100644 *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** _readScalarArrayOpExpr(void) *** 676,681 **** --- 676,682 ---- READ_BOOL_FIELD(useOr); READ_OID_FIELD(inputcollid); + READ_INT_FIELD(aryArgIdx); READ_NODE_FIELD(args); READ_LOCATION_FIELD(location); diff --git a/src/backend/optimizer/path/costsize.c b/src/backend/optimizer/path/costsize.c index bb38768..13273d1 100644 *** a/src/backend/optimizer/path/costsize.c --- b/src/backend/optimizer/path/costsize.c *************** cost_tidscan(Path *path, PlannerInfo *ro *** 829,835 **** { /* Each element of the array yields 1 tuple */ ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) lfirst(l); ! Node *arraynode = (Node *) lsecond(saop->args); ntuples += estimate_array_length(arraynode); } --- 829,836 ---- { /* Each element of the array yields 1 tuple */ ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) lfirst(l); ! Node *arraynode = (Node *) list_nth(saop->args, ! saop->aryArgIdx); ntuples += estimate_array_length(arraynode); } *************** cost_qual_eval_walker(Node *node, cost_q *** 2711,2717 **** * array elements before the answer is determined. */ ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) node; ! Node *arraynode = (Node *) lsecond(saop->args); set_sa_opfuncid(saop); context->total.per_tuple += get_func_cost(saop->opfuncid) * --- 2712,2718 ---- * array elements before the answer is determined. */ ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) node; ! Node *arraynode = (Node *) list_nth(saop->args, saop->aryArgIdx); set_sa_opfuncid(saop); context->total.per_tuple += get_func_cost(saop->opfuncid) * diff --git a/src/backend/optimizer/path/indxpath.c b/src/backend/optimizer/path/indxpath.c index 1cace6d..e466627 100644 *** a/src/backend/optimizer/path/indxpath.c --- b/src/backend/optimizer/path/indxpath.c *************** match_clause_to_indexcol(IndexOptInfo *i *** 1225,1230 **** --- 1225,1232 ---- Oid expr_op; Oid expr_coll; bool plain_op; + bool match_left_op; + bool match_right_op; /* * Never match pseudoconstants to indexes. (Normally this could not *************** match_clause_to_indexcol(IndexOptInfo *i *** 1259,1264 **** --- 1261,1267 ---- expr_op = ((OpExpr *) clause)->opno; expr_coll = ((OpExpr *) clause)->inputcollid; plain_op = true; + match_left_op = match_right_op = true; } else if (saop_control != SAOP_FORBID && clause && IsA(clause, ScalarArrayOpExpr)) *************** match_clause_to_indexcol(IndexOptInfo *i *** 1268,1277 **** /* We only accept ANY clauses, not ALL */ if (!saop->useOr) return false; leftop = (Node *) linitial(saop->args); rightop = (Node *) lsecond(saop->args); ! left_relids = NULL; /* not actually needed */ ! right_relids = pull_varnos(rightop); expr_op = saop->opno; expr_coll = saop->inputcollid; plain_op = false; --- 1271,1293 ---- /* We only accept ANY clauses, not ALL */ if (!saop->useOr) return false; + leftop = (Node *) linitial(saop->args); rightop = (Node *) lsecond(saop->args); ! if (saop->aryArgIdx == 1) ! { ! left_relids = NULL; ! right_relids = pull_varnos(rightop); ! match_left_op = true; ! match_right_op = false; ! } ! else ! { ! left_relids = pull_varnos(leftop); ! right_relids = NULL; ! match_left_op = false; ! match_right_op = true; ! } expr_op = saop->opno; expr_coll = saop->inputcollid; plain_op = false; *************** match_clause_to_indexcol(IndexOptInfo *i *** 1299,1305 **** * Check for clauses of the form: (indexkey operator constant) or * (constant operator indexkey). See above notes about const-ness. */ ! if (match_index_to_operand(leftop, indexcol, index) && bms_is_subset(right_relids, outer_relids) && !contain_volatile_functions(rightop)) { --- 1315,1322 ---- * Check for clauses of the form: (indexkey operator constant) or * (constant operator indexkey). See above notes about const-ness. */ ! if (match_left_op && ! match_index_to_operand(leftop, indexcol, index) && bms_is_subset(right_relids, outer_relids) && !contain_volatile_functions(rightop)) { *************** match_clause_to_indexcol(IndexOptInfo *i *** 1317,1323 **** return false; } ! if (plain_op && match_index_to_operand(rightop, indexcol, index) && bms_is_subset(left_relids, outer_relids) && !contain_volatile_functions(leftop)) --- 1334,1340 ---- return false; } ! if (match_right_op && match_index_to_operand(rightop, indexcol, index) && bms_is_subset(left_relids, outer_relids) && !contain_volatile_functions(leftop)) *************** match_clause_to_indexcol(IndexOptInfo *i *** 1330,1336 **** * If we didn't find a member of the index's opfamily, see whether it * is a "special" indexable operator. */ ! if (match_special_index_operator(clause, opfamily, idxcollation, false)) return true; return false; } --- 1347,1354 ---- * If we didn't find a member of the index's opfamily, see whether it * is a "special" indexable operator. */ ! if (plain_op && ! match_special_index_operator(clause, opfamily, idxcollation, false)) return true; return false; } diff --git a/src/backend/optimizer/path/tidpath.c b/src/backend/optimizer/path/tidpath.c index 05c18b5..50721d3 100644 *** a/src/backend/optimizer/path/tidpath.c --- b/src/backend/optimizer/path/tidpath.c *************** IsTidEqualClause(OpExpr *node, int varno *** 111,123 **** /* * Check to see if a clause is of the form ! * CTID = ANY (pseudoconstant_array) */ static bool IsTidEqualAnyClause(ScalarArrayOpExpr *node, int varno) { ! Node *arg1, ! *arg2; /* Operator must be tideq */ if (node->opno != TIDEqualOperator) --- 111,124 ---- /* * Check to see if a clause is of the form ! * CTID = ANY (pseudoconstant_array) or ! * ANY(pseudoconstant_array) = CTID */ static bool IsTidEqualAnyClause(ScalarArrayOpExpr *node, int varno) { ! Node *sarg /* Scalar argument */, ! *aarg /* Array argument */; /* Operator must be tideq */ if (node->opno != TIDEqualOperator) *************** IsTidEqualAnyClause(ScalarArrayOpExpr *n *** 125,145 **** if (!node->useOr) return false; Assert(list_length(node->args) == 2); ! arg1 = linitial(node->args); ! arg2 = lsecond(node->args); ! /* CTID must be first argument */ ! if (arg1 && IsA(arg1, Var)) { ! Var *var = (Var *) arg1; if (var->varattno == SelfItemPointerAttributeNumber && var->vartype == TIDOID && var->varno == varno && var->varlevelsup == 0) { ! /* The other argument must be a pseudoconstant */ ! if (is_pseudo_constant_clause(arg2)) return true; /* success */ } } --- 126,146 ---- if (!node->useOr) return false; Assert(list_length(node->args) == 2); ! sarg = list_nth(node->args, 1 - node->aryArgIdx); ! aarg = list_nth(node->args, node->aryArgIdx); ! /* CTID must be scalar argument */ ! if (sarg && IsA(sarg, Var)) { ! Var *var = (Var *) sarg; if (var->varattno == SelfItemPointerAttributeNumber && var->vartype == TIDOID && var->varno == varno && var->varlevelsup == 0) { ! /* The array argument must be a pseudoconstant */ ! if (is_pseudo_constant_clause(aarg)) return true; /* success */ } } diff --git a/src/backend/optimizer/plan/createplan.c b/src/backend/optimizer/plan/createplan.c index e4ccf5c..0e8a2ad 100644 *** a/src/backend/optimizer/plan/createplan.c --- b/src/backend/optimizer/plan/createplan.c *************** fix_indexqual_references(PlannerInfo *ro *** 2463,2475 **** else if (IsA(clause, ScalarArrayOpExpr)) { ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause; ! ! /* Never need to commute... */ ! /* * Determine which index attribute this is and change the indexkey * operand as needed. */ linitial(saop->args) = fix_indexqual_operand(linitial(saop->args), index); } --- 2463,2480 ---- else if (IsA(clause, ScalarArrayOpExpr)) { ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause; ! ! /* To be indexable, the scalar needs to be the left ! * operator argument and the array the right ! */ ! if (saop->aryArgIdx != 1) ! CommuteScalarArrayOpExpr(saop); ! /* * Determine which index attribute this is and change the indexkey * operand as needed. */ + Assert(saop->aryArgIdx == 1); linitial(saop->args) = fix_indexqual_operand(linitial(saop->args), index); } diff --git a/src/backend/optimizer/prep/prepqual.c b/src/backend/optimizer/prep/prepqual.c index f6f00c4..6cf7867 100644 *** a/src/backend/optimizer/prep/prepqual.c --- b/src/backend/optimizer/prep/prepqual.c *************** negate_clause(Node *node) *** 129,134 **** --- 129,135 ---- newopexpr->opfuncid = InvalidOid; newopexpr->useOr = !saopexpr->useOr; newopexpr->inputcollid = saopexpr->inputcollid; + newopexpr->aryArgIdx = saopexpr->aryArgIdx; newopexpr->args = saopexpr->args; newopexpr->location = saopexpr->location; return (Node *) newopexpr; diff --git a/src/backend/optimizer/util/clauses.c b/src/backend/optimizer/util/clauses.c index 2914c39..e233026 100644 *** a/src/backend/optimizer/util/clauses.c --- b/src/backend/optimizer/util/clauses.c *************** find_forced_null_var(Node *node) *** 1708,1714 **** static bool is_strict_saop(ScalarArrayOpExpr *expr, bool falseOK) { ! Node *rightop; /* The contained operator must be strict. */ set_sa_opfuncid(expr); --- 1708,1714 ---- static bool is_strict_saop(ScalarArrayOpExpr *expr, bool falseOK) { ! Node *arrayop; /* The contained operator must be strict. */ set_sa_opfuncid(expr); *************** is_strict_saop(ScalarArrayOpExpr *expr, *** 1719,1729 **** return true; /* Else, we have to see if the array is provably non-empty. */ Assert(list_length(expr->args) == 2); ! rightop = (Node *) lsecond(expr->args); ! if (rightop && IsA(rightop, Const)) { ! Datum arraydatum = ((Const *) rightop)->constvalue; ! bool arrayisnull = ((Const *) rightop)->constisnull; ArrayType *arrayval; int nitems; --- 1719,1729 ---- return true; /* Else, we have to see if the array is provably non-empty. */ Assert(list_length(expr->args) == 2); ! arrayop = (Node *) list_nth(expr->args, expr->aryArgIdx); ! if (arrayop && IsA(arrayop, Const)) { ! Datum arraydatum = ((Const *) arrayop)->constvalue; ! bool arrayisnull = ((Const *) arrayop)->constisnull; ArrayType *arrayval; int nitems; *************** is_strict_saop(ScalarArrayOpExpr *expr, *** 1734,1742 **** if (nitems > 0) return true; } ! else if (rightop && IsA(rightop, ArrayExpr)) { ! ArrayExpr *arrayexpr = (ArrayExpr *) rightop; if (arrayexpr->elements != NIL && !arrayexpr->multidims) return true; --- 1734,1742 ---- if (nitems > 0) return true; } ! else if (arrayop && IsA(arrayop, ArrayExpr)) { ! ArrayExpr *arrayexpr = (ArrayExpr *) arrayop; if (arrayexpr->elements != NIL && !arrayexpr->multidims) return true; *************** CommuteOpExpr(OpExpr *clause) *** 1853,1858 **** --- 1853,1893 ---- } /* + * CommuteScalarArrayOpExpr: commute a binary operator clause + * + * XXX the clause is destructively modified! + */ + void + CommuteScalarArrayOpExpr(ScalarArrayOpExpr *clause) + { + Oid opoid; + Node *temp; + + /* Sanity checks: caller is at fault if these fail */ + if (!IsA(clause, ScalarArrayOpExpr) || + list_length(clause->args) != 2) + elog(ERROR, "invalid scalar-array-operator clause"); + + opoid = get_commutator(clause->opno); + + if (!OidIsValid(opoid)) + elog(ERROR, "could not find commutator for operator %u", + clause->opno); + + /* + * modify the clause in-place! + */ + clause->opno = opoid; + clause->opfuncid = InvalidOid; + clause->aryArgIdx = 1 - clause->aryArgIdx; + /* opresulttype, opretset, opcollid, inputcollid need not change */ + + temp = linitial(clause->args); + linitial(clause->args) = lsecond(clause->args); + lsecond(clause->args) = temp; + } + + /* * CommuteRowCompareExpr: commute a RowCompareExpr clause * * XXX the clause is destructively modified! diff --git a/src/backend/optimizer/util/predtest.c b/src/backend/optimizer/util/predtest.c index a7e8372..2f7d0bf 100644 *** a/src/backend/optimizer/util/predtest.c --- b/src/backend/optimizer/util/predtest.c *************** predicate_classify(Node *clause, PredIte *** 782,788 **** if (IsA(clause, ScalarArrayOpExpr)) { ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause; ! Node *arraynode = (Node *) lsecond(saop->args); /* * We can break this down into an AND or OR structure, but only if we --- 782,788 ---- if (IsA(clause, ScalarArrayOpExpr)) { ScalarArrayOpExpr *saop = (ScalarArrayOpExpr *) clause; ! Node *arraynode = (Node *) list_nth(saop->args, saop->aryArgIdx); /* * We can break this down into an AND or OR structure, but only if we *************** arrayconst_startup_fn(Node *clause, Pred *** 890,896 **** info->state = (void *) state; /* Deconstruct the array literal */ ! arrayconst = (Const *) lsecond(saop->args); arrayval = DatumGetArrayTypeP(arrayconst->constvalue); get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), &elmlen, &elmbyval, &elmalign); --- 890,896 ---- info->state = (void *) state; /* Deconstruct the array literal */ ! arrayconst = (Const *) list_nth(saop->args, saop->aryArgIdx); arrayval = DatumGetArrayTypeP(arrayconst->constvalue); get_typlenbyvalalign(ARR_ELEMTYPE(arrayval), &elmlen, &elmbyval, &elmalign); *************** arrayconst_startup_fn(Node *clause, Pred *** 908,914 **** state->opexpr.opretset = false; state->opexpr.opcollid = InvalidOid; state->opexpr.inputcollid = saop->inputcollid; - state->opexpr.args = list_copy(saop->args); /* Set up a dummy Const node to hold the per-element values */ state->constexpr.xpr.type = T_Const; --- 908,913 ---- *************** arrayconst_startup_fn(Node *clause, Pred *** 917,923 **** state->constexpr.constcollid = arrayconst->constcollid; state->constexpr.constlen = elmlen; state->constexpr.constbyval = elmbyval; ! lsecond(state->opexpr.args) = &state->constexpr; /* Initialize iteration state */ state->next_elem = 0; --- 916,926 ---- state->constexpr.constcollid = arrayconst->constcollid; state->constexpr.constlen = elmlen; state->constexpr.constbyval = elmbyval; ! ! if (saop->aryArgIdx == 1) ! state->opexpr.args = list_make2(linitial(saop->args), &state->constexpr); ! else ! state->opexpr.args = list_make2(&state->constexpr, lsecond(saop->args)); /* Initialize iteration state */ state->next_elem = 0; *************** arrayexpr_startup_fn(Node *clause, PredI *** 979,985 **** state->opexpr.args = list_copy(saop->args); /* Initialize iteration variable to first member of ArrayExpr */ ! arrayexpr = (ArrayExpr *) lsecond(saop->args); state->next = list_head(arrayexpr->elements); } --- 982,988 ---- state->opexpr.args = list_copy(saop->args); /* Initialize iteration variable to first member of ArrayExpr */ ! arrayexpr = (ArrayExpr *) list_nth(saop->args, saop->aryArgIdx); state->next = list_head(arrayexpr->elements); } diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y index 1d39674..6e3c766 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** a_expr: c_expr { $$ = $1; } *** 10076,10081 **** --- 10076,10088 ---- n->location = @2; $$ = (Node *)n; } + | '(' sub_type select_with_parens subquery_Op a_expr ')' %prec Op + { + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("ANY/ALL() is not yet implemented"), + parser_errposition(@2))); + } | a_expr subquery_Op sub_type '(' a_expr ')' %prec Op { if ($3 == ANY_SUBLINK) *************** a_expr: c_expr { $$ = $1; } *** 10083,10088 **** --- 10090,10102 ---- else $$ = (Node *) makeA_Expr(AEXPR_OP_ALL, $2, $1, $5, @2); } + | '(' sub_type '(' a_expr ')' subquery_Op a_expr ')' %prec Op + { + if ($2 == ANY_SUBLINK) + $$ = (Node *) makeA_Expr(AEXPR_ANY_OP, $6, $4, $7, @2); + else + $$ = (Node *) makeA_Expr(AEXPR_ALL_OP, $6, $4, $7, @2); + } | UNIQUE select_with_parens { /* Not sure how to get rid of the parentheses diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index 08f0439..2b75925 100644 *** a/src/backend/parser/parse_expr.c --- b/src/backend/parser/parse_expr.c *************** static Node *transformAExprOp(ParseState *** 42,49 **** static Node *transformAExprAnd(ParseState *pstate, A_Expr *a); static Node *transformAExprOr(ParseState *pstate, A_Expr *a); static Node *transformAExprNot(ParseState *pstate, A_Expr *a); ! static Node *transformAExprOpAny(ParseState *pstate, A_Expr *a); ! static Node *transformAExprOpAll(ParseState *pstate, A_Expr *a); static Node *transformAExprDistinct(ParseState *pstate, A_Expr *a); static Node *transformAExprNullIf(ParseState *pstate, A_Expr *a); static Node *transformAExprOf(ParseState *pstate, A_Expr *a); --- 42,49 ---- static Node *transformAExprAnd(ParseState *pstate, A_Expr *a); static Node *transformAExprOr(ParseState *pstate, A_Expr *a); static Node *transformAExprNot(ParseState *pstate, A_Expr *a); ! static Node *transformAExprOpAny(ParseState *pstate, A_Expr *a, bool aryIsLeftArg); ! static Node *transformAExprOpAll(ParseState *pstate, A_Expr *a, bool aryIsLeftArg); static Node *transformAExprDistinct(ParseState *pstate, A_Expr *a); static Node *transformAExprNullIf(ParseState *pstate, A_Expr *a); static Node *transformAExprOf(ParseState *pstate, A_Expr *a); *************** transformExpr(ParseState *pstate, Node * *** 211,220 **** result = transformAExprNot(pstate, a); break; case AEXPR_OP_ANY: ! result = transformAExprOpAny(pstate, a); break; case AEXPR_OP_ALL: ! result = transformAExprOpAll(pstate, a); break; case AEXPR_DISTINCT: result = transformAExprDistinct(pstate, a); --- 211,226 ---- result = transformAExprNot(pstate, a); break; case AEXPR_OP_ANY: ! result = transformAExprOpAny(pstate, a, false); ! break; ! case AEXPR_ANY_OP: ! result = transformAExprOpAny(pstate, a, true); break; case AEXPR_OP_ALL: ! result = transformAExprOpAll(pstate, a, false); ! break; ! case AEXPR_ALL_OP: ! result = transformAExprOpAll(pstate, a, true); break; case AEXPR_DISTINCT: result = transformAExprDistinct(pstate, a); *************** transformAExprNot(ParseState *pstate, A_ *** 943,955 **** } static Node * ! transformAExprOpAny(ParseState *pstate, A_Expr *a) { Node *lexpr = transformExpr(pstate, a->lexpr); Node *rexpr = transformExpr(pstate, a->rexpr); return (Node *) make_scalar_array_op(pstate, a->name, true, lexpr, rexpr, --- 949,962 ---- } static Node * ! transformAExprOpAny(ParseState *pstate, A_Expr *a, bool aryIsLeftArg) { Node *lexpr = transformExpr(pstate, a->lexpr); Node *rexpr = transformExpr(pstate, a->rexpr); return (Node *) make_scalar_array_op(pstate, a->name, + aryIsLeftArg, true, lexpr, rexpr, *************** transformAExprOpAny(ParseState *pstate, *** 957,969 **** } static Node * ! transformAExprOpAll(ParseState *pstate, A_Expr *a) { Node *lexpr = transformExpr(pstate, a->lexpr); Node *rexpr = transformExpr(pstate, a->rexpr); return (Node *) make_scalar_array_op(pstate, a->name, false, lexpr, rexpr, --- 964,977 ---- } static Node * ! transformAExprOpAll(ParseState *pstate, A_Expr *a, bool aryIsLeftArg) { Node *lexpr = transformExpr(pstate, a->lexpr); Node *rexpr = transformExpr(pstate, a->rexpr); return (Node *) make_scalar_array_op(pstate, a->name, + aryIsLeftArg, false, lexpr, rexpr, *************** transformAExprIn(ParseState *pstate, A_E *** 1170,1175 **** --- 1178,1184 ---- result = (Node *) make_scalar_array_op(pstate, a->name, + false, useOr, lexpr, (Node *) newa, diff --git a/src/backend/parser/parse_oper.c b/src/backend/parser/parse_oper.c index 4a2f777..0f42f6c 100644 *** a/src/backend/parser/parse_oper.c --- b/src/backend/parser/parse_oper.c *************** make_op(ParseState *pstate, List *opname *** 846,869 **** */ Expr * make_scalar_array_op(ParseState *pstate, List *opname, ! bool useOr, Node *ltree, Node *rtree, int location) { ! Oid ltypeId, ! rtypeId, ! atypeId, ! res_atypeId; Operator tup; Form_pg_operator opform; Oid actual_arg_types[2]; Oid declared_arg_types[2]; List *args; Oid rettype; ScalarArrayOpExpr *result; ! ltypeId = exprType(ltree); ! atypeId = exprType(rtree); /* * The right-hand input of the operator will be the element type of the --- 846,883 ---- */ Expr * make_scalar_array_op(ParseState *pstate, List *opname, ! bool aryIsLeftArg, bool useOr, Node *ltree, Node *rtree, int location) { ! Oid stypeId /* Scalar type OID */, ! etypeId /* Array element type OID */, ! atypeId /* Array type OID */, ! res_atypeId /* Array type corresponding to the operator's ! * declared argument type. For polymorphoc ops, ! * this isn't any* but rather equals atypeId ! */; Operator tup; Form_pg_operator opform; + int aryArgIdx; Oid actual_arg_types[2]; Oid declared_arg_types[2]; List *args; Oid rettype; ScalarArrayOpExpr *result; ! if (aryIsLeftArg) ! { ! atypeId = exprType(ltree); ! stypeId = exprType(rtree); ! aryArgIdx = 0; ! } ! else ! { ! stypeId = exprType(ltree); ! atypeId = exprType(rtree); ! aryArgIdx = 1; ! } /* * The right-hand input of the operator will be the element type of the *************** make_scalar_array_op(ParseState *pstate, *** 871,889 **** * right, stay with that and hope we can resolve the operator. */ if (atypeId == UNKNOWNOID) ! rtypeId = UNKNOWNOID; else { ! rtypeId = get_base_element_type(atypeId); ! if (!OidIsValid(rtypeId)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("op ANY/ALL (array) requires array on right side"), parser_errposition(pstate, location))); } /* Now resolve the operator */ ! tup = oper(pstate, opname, ltypeId, rtypeId, false, location); opform = (Form_pg_operator) GETSTRUCT(tup); /* Check it's not a shell */ --- 885,906 ---- * right, stay with that and hope we can resolve the operator. */ if (atypeId == UNKNOWNOID) ! etypeId = UNKNOWNOID; else { ! etypeId = get_base_element_type(atypeId); ! if (!OidIsValid(etypeId)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("ANY/ALL (expression) requires array expression "), parser_errposition(pstate, location))); } /* Now resolve the operator */ ! if (aryArgIdx == 1) ! tup = oper(pstate, opname, stypeId, etypeId, false, location); ! else ! tup = oper(pstate, opname, etypeId, stypeId, false, location); opform = (Form_pg_operator) GETSTRUCT(tup); /* Check it's not a shell */ *************** make_scalar_array_op(ParseState *pstate, *** 898,905 **** parser_errposition(pstate, location))); args = list_make2(ltree, rtree); ! actual_arg_types[0] = ltypeId; ! actual_arg_types[1] = rtypeId; declared_arg_types[0] = opform->oprleft; declared_arg_types[1] = opform->oprright; --- 915,922 ---- parser_errposition(pstate, location))); args = list_make2(ltree, rtree); ! actual_arg_types[1-aryArgIdx] = stypeId; ! actual_arg_types[aryArgIdx] = etypeId; declared_arg_types[0] = opform->oprleft; declared_arg_types[1] = opform->oprright; *************** make_scalar_array_op(ParseState *pstate, *** 920,947 **** if (rettype != BOOLOID) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("op ANY/ALL (array) requires operator to yield boolean"), parser_errposition(pstate, location))); if (get_func_retset(opform->oprcode)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("op ANY/ALL (array) requires operator not to return a set"), parser_errposition(pstate, location))); /* ! * Now switch back to the array type on the right, arranging for any * needed cast to be applied. Beware of polymorphic operators here; * enforce_generic_type_consistency may or may not have replaced a * polymorphic type with a real one. */ ! if (IsPolymorphicType(declared_arg_types[1])) { /* assume the actual array type is OK */ res_atypeId = atypeId; } else { ! res_atypeId = get_array_type(declared_arg_types[1]); if (!OidIsValid(res_atypeId)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), --- 937,964 ---- if (rettype != BOOLOID) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("ANY/ALL (array) requires operator to yield boolean"), parser_errposition(pstate, location))); if (get_func_retset(opform->oprcode)) ereport(ERROR, (errcode(ERRCODE_WRONG_OBJECT_TYPE), ! errmsg("ANY/ALL (array) requires operator not to return a set"), parser_errposition(pstate, location))); /* ! * Now switch back to the array type on the array side, arranging for any * needed cast to be applied. Beware of polymorphic operators here; * enforce_generic_type_consistency may or may not have replaced a * polymorphic type with a real one. */ ! if (IsPolymorphicType(declared_arg_types[aryArgIdx])) { /* assume the actual array type is OK */ res_atypeId = atypeId; } else { ! res_atypeId = get_array_type(declared_arg_types[aryArgIdx]); if (!OidIsValid(res_atypeId)) ereport(ERROR, (errcode(ERRCODE_UNDEFINED_OBJECT), *************** make_scalar_array_op(ParseState *pstate, *** 949,956 **** format_type_be(declared_arg_types[1])), parser_errposition(pstate, location))); } ! actual_arg_types[1] = atypeId; ! declared_arg_types[1] = res_atypeId; /* perform the necessary typecasting of arguments */ make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types); --- 966,973 ---- format_type_be(declared_arg_types[1])), parser_errposition(pstate, location))); } ! actual_arg_types[aryArgIdx] = atypeId; ! declared_arg_types[aryArgIdx] = res_atypeId; /* perform the necessary typecasting of arguments */ make_fn_arguments(pstate, args, actual_arg_types, declared_arg_types); *************** make_scalar_array_op(ParseState *pstate, *** 961,966 **** --- 978,984 ---- result->opfuncid = opform->oprcode; result->useOr = useOr; /* inputcollid will be set by parse_collate.c */ + result->aryArgIdx = aryArgIdx; result->args = args; result->location = location; diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index 06cf6fa..b763819 100644 *** a/src/backend/utils/adt/ruleutils.c --- b/src/backend/utils/adt/ruleutils.c *************** get_rule_expr(Node *node, deparse_contex *** 4898,4911 **** if (!PRETTY_PAREN(context)) appendStringInfoChar(buf, '('); get_rule_expr_paren(arg1, context, true, node); ! appendStringInfo(buf, " %s %s (", ! generate_operator_name(expr->opno, ! exprType(arg1), ! get_base_element_type(exprType(arg2))), ! expr->useOr ? "ANY" : "ALL"); get_rule_expr_paren(arg2, context, true, node); ! appendStringInfoChar(buf, ')'); if (!PRETTY_PAREN(context)) appendStringInfoChar(buf, ')'); } --- 4898,4925 ---- if (!PRETTY_PAREN(context)) appendStringInfoChar(buf, '('); + if (expr->aryArgIdx == 0) + appendStringInfo(buf, "%s (", + expr->useOr ? "ANY" : "ALL"); get_rule_expr_paren(arg1, context, true, node); ! if (expr->aryArgIdx == 1) ! { ! appendStringInfo(buf, " %s %s (", ! generate_operator_name(expr->opno, ! exprType(arg1), ! get_base_element_type(exprType(arg2))), ! expr->useOr ? "ANY" : "ALL"); ! } ! else ! { ! appendStringInfo(buf, ") %s ", ! generate_operator_name(expr->opno, ! exprType(arg1), ! get_base_element_type(exprType(arg2)))); ! } get_rule_expr_paren(arg2, context, true, node); ! if (expr->aryArgIdx == 1) ! appendStringInfoChar(buf, ')'); if (!PRETTY_PAREN(context)) appendStringInfoChar(buf, ')'); } diff --git a/src/backend/utils/adt/selfuncs.c b/src/backend/utils/adt/selfuncs.c index 00ba19e..4393c92 100644 *** a/src/backend/utils/adt/selfuncs.c --- b/src/backend/utils/adt/selfuncs.c *************** scalararraysel(PlannerInfo *root, *** 1690,1697 **** { Oid operator = clause->opno; bool useOr = clause->useOr; ! Node *leftop; ! Node *rightop; Oid nominal_element_type; Oid nominal_element_collation; RegProcedure oprsel; --- 1690,1697 ---- { Oid operator = clause->opno; bool useOr = clause->useOr; ! Node *scalarop; ! Node *arrayop; Oid nominal_element_type; Oid nominal_element_collation; RegProcedure oprsel; *************** scalararraysel(PlannerInfo *root, *** 1712,1746 **** /* deconstruct the expression */ Assert(list_length(clause->args) == 2); ! leftop = (Node *) linitial(clause->args); ! rightop = (Node *) lsecond(clause->args); ! /* get nominal (after relabeling) element type of rightop */ ! nominal_element_type = get_base_element_type(exprType(rightop)); if (!OidIsValid(nominal_element_type)) return (Selectivity) 0.5; /* probably shouldn't happen */ /* get nominal collation, too, for generating constants */ ! nominal_element_collation = exprCollation(rightop); ! /* look through any binary-compatible relabeling of rightop */ ! rightop = strip_array_coercion(rightop); /* * We consider three cases: * ! * 1. rightop is an Array constant: deconstruct the array, apply the * operator's selectivity function for each array element, and merge the * results in the same way that clausesel.c does for AND/OR combinations. * ! * 2. rightop is an ARRAY[] construct: apply the operator's selectivity * function for each element of the ARRAY[] construct, and merge. * * 3. otherwise, make a guess ... */ ! if (rightop && IsA(rightop, Const)) { ! Datum arraydatum = ((Const *) rightop)->constvalue; ! bool arrayisnull = ((Const *) rightop)->constisnull; ArrayType *arrayval; int16 elmlen; bool elmbyval; --- 1712,1746 ---- /* deconstruct the expression */ Assert(list_length(clause->args) == 2); ! scalarop = (Node *) list_nth(clause->args, 1 - clause->aryArgIdx); ! arrayop = (Node *) list_nth(clause->args, clause->aryArgIdx); ! /* get nominal (after relabeling) element type of arrayop */ ! nominal_element_type = get_base_element_type(exprType(arrayop)); if (!OidIsValid(nominal_element_type)) return (Selectivity) 0.5; /* probably shouldn't happen */ /* get nominal collation, too, for generating constants */ ! nominal_element_collation = exprCollation(arrayop); ! /* look through any binary-compatible relabeling of arrayop */ ! arrayop = strip_array_coercion(arrayop); /* * We consider three cases: * ! * 1. arrayop is an Array constant: deconstruct the array, apply the * operator's selectivity function for each array element, and merge the * results in the same way that clausesel.c does for AND/OR combinations. * ! * 2. arrayop is an ARRAY[] construct: apply the operator's selectivity * function for each element of the ARRAY[] construct, and merge. * * 3. otherwise, make a guess ... */ ! if (arrayop && IsA(arrayop, Const)) { ! Datum arraydatum = ((Const *) arrayop)->constvalue; ! bool arrayisnull = ((Const *) arrayop)->constisnull; ArrayType *arrayval; int16 elmlen; bool elmbyval; *************** scalararraysel(PlannerInfo *root, *** 1764,1778 **** { List *args; Selectivity s2; ! ! args = list_make2(leftop, ! makeConst(nominal_element_type, ! -1, ! nominal_element_collation, ! elmlen, ! elem_values[i], ! elem_nulls[i], ! elmbyval)); if (is_join_clause) s2 = DatumGetFloat8(FunctionCall5(&oprselproc, PointerGetDatum(root), --- 1764,1781 ---- { List *args; Selectivity s2; ! ! Node *elem = (Node *) makeConst(nominal_element_type, ! -1, ! nominal_element_collation, ! elmlen, ! elem_values[i], ! elem_nulls[i], ! elmbyval); ! if (clause->aryArgIdx == 1) ! args = list_make2(scalarop, elem); ! else ! args = list_make2(elem, scalarop); if (is_join_clause) s2 = DatumGetFloat8(FunctionCall5(&oprselproc, PointerGetDatum(root), *************** scalararraysel(PlannerInfo *root, *** 1792,1801 **** s1 = s1 * s2; } } ! else if (rightop && IsA(rightop, ArrayExpr) && ! !((ArrayExpr *) rightop)->multidims) { ! ArrayExpr *arrayexpr = (ArrayExpr *) rightop; int16 elmlen; bool elmbyval; ListCell *l; --- 1795,1804 ---- s1 = s1 * s2; } } ! else if (arrayop && IsA(arrayop, ArrayExpr) && ! !((ArrayExpr *) arrayop)->multidims) { ! ArrayExpr *arrayexpr = (ArrayExpr *) arrayop; int16 elmlen; bool elmbyval; ListCell *l; *************** scalararraysel(PlannerInfo *root, *** 1814,1820 **** * insert a RelabelType, but it seems unlikely that any operator * estimation function would really care ... */ ! args = list_make2(leftop, elem); if (is_join_clause) s2 = DatumGetFloat8(FunctionCall5(&oprselproc, PointerGetDatum(root), --- 1817,1826 ---- * insert a RelabelType, but it seems unlikely that any operator * estimation function would really care ... */ ! if (clause->aryArgIdx == 1) ! args = list_make2(scalarop, elem); ! else ! args = list_make2(elem, scalarop); if (is_join_clause) s2 = DatumGetFloat8(FunctionCall5(&oprselproc, PointerGetDatum(root), *************** scalararraysel(PlannerInfo *root, *** 1842,1848 **** int i; /* ! * We need a dummy rightop to pass to the operator selectivity * routine. It can be pretty much anything that doesn't look like a * constant; CaseTestExpr is a convenient choice. */ --- 1848,1854 ---- int i; /* ! * We need a dummy arrayop to pass to the operator selectivity * routine. It can be pretty much anything that doesn't look like a * constant; CaseTestExpr is a convenient choice. */ *************** scalararraysel(PlannerInfo *root, *** 1850,1856 **** dummyexpr->typeId = nominal_element_type; dummyexpr->typeMod = -1; dummyexpr->collation = clause->inputcollid; ! args = list_make2(leftop, dummyexpr); if (is_join_clause) s2 = DatumGetFloat8(FunctionCall5(&oprselproc, PointerGetDatum(root), --- 1856,1865 ---- dummyexpr->typeId = nominal_element_type; dummyexpr->typeMod = -1; dummyexpr->collation = clause->inputcollid; ! if (clause->aryArgIdx == 1) ! args = list_make2(scalarop, dummyexpr); ! else ! args = list_make2(dummyexpr, scalarop); if (is_join_clause) s2 = DatumGetFloat8(FunctionCall5(&oprselproc, PointerGetDatum(root), diff --git a/src/backend/utils/fmgr/fmgr.c b/src/backend/utils/fmgr/fmgr.c index 21fb5ad..6862105 100644 *** a/src/backend/utils/fmgr/fmgr.c --- b/src/backend/utils/fmgr/fmgr.c *************** get_call_expr_argtype(Node *expr, int ar *** 2360,2366 **** * array. */ if (IsA(expr, ScalarArrayOpExpr) && ! argnum == 1) argtype = get_base_element_type(argtype); else if (IsA(expr, ArrayCoerceExpr) && argnum == 0) --- 2360,2366 ---- * array. */ if (IsA(expr, ScalarArrayOpExpr) && ! argnum == ((ScalarArrayOpExpr *) expr)->aryArgIdx) argtype = get_base_element_type(argtype); else if (IsA(expr, ArrayCoerceExpr) && argnum == 0) diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h index 14937d4..b1e6aa4 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef enum A_Expr_Kind *** 227,233 **** --- 227,235 ---- AEXPR_OR, AEXPR_NOT, AEXPR_OP_ANY, /* scalar op ANY (array) */ + AEXPR_ANY_OP, /* ANY (array) op scalar */ AEXPR_OP_ALL, /* scalar op ALL (array) */ + AEXPR_ALL_OP, /* ALL (array) op scalar */ AEXPR_DISTINCT, /* IS DISTINCT FROM - name must be "=" */ AEXPR_NULLIF, /* NULLIF - name must be "=" */ AEXPR_OF, /* IS [NOT] OF - name must be "=" or "<>" */ diff --git a/src/include/nodes/primnodes.h b/src/include/nodes/primnodes.h index f1e20ef..7d2ce5e 100644 *** a/src/include/nodes/primnodes.h --- b/src/include/nodes/primnodes.h *************** typedef OpExpr NullIfExpr; *** 405,416 **** /* * ScalarArrayOpExpr - expression node for "scalar op ANY/ALL (array)" * ! * The operator must yield boolean. It is applied to the left operand ! * and each element of the righthand array, and the results are combined * with OR or AND (for ANY or ALL respectively). The node representation * is almost the same as for the underlying operator, but we need a useOr * flag to remember whether it's ANY or ALL, and we don't have to store * the result type (or the collation) because it must be boolean. */ typedef struct ScalarArrayOpExpr { --- 405,423 ---- /* * ScalarArrayOpExpr - expression node for "scalar op ANY/ALL (array)" * ! * The operator must yield boolean. It is applied to the scalar operand ! * and each element of the array operand, and the results are combined * with OR or AND (for ANY or ALL respectively). The node representation * is almost the same as for the underlying operator, but we need a useOr * flag to remember whether it's ANY or ALL, and we don't have to store * the result type (or the collation) because it must be boolean. + * + * aryArgIdx contains the index (0 or 1) of the array operand. We usually + * strive to construct ScalarArrayOpExprs with aryArgIdx = 1, but if we + * encounter "ANY/ALL (array) op scalar" and op has no commutator we have + * no choice but set aryArgIdx = 0. Such expressions are never indexable, + * but we don't care about that. All reasonable indexable operators ought + * to have commutators anyway if "const op field" is supposed to be indexable. */ typedef struct ScalarArrayOpExpr { *************** typedef struct ScalarArrayOpExpr *** 419,424 **** --- 426,432 ---- Oid opfuncid; /* PG_PROC OID of underlying function */ bool useOr; /* true for ANY, false for ALL */ Oid inputcollid; /* OID of collation that operator should use */ + int aryArgIdx; /* The index (0 or 1) of the array operand */ List *args; /* the scalar and array operands */ int location; /* token location, or -1 if unknown */ } ScalarArrayOpExpr; diff --git a/src/include/optimizer/clauses.h b/src/include/optimizer/clauses.h index dde6d82..7f059fe 100644 *** a/src/include/optimizer/clauses.h --- b/src/include/optimizer/clauses.h *************** extern bool is_pseudo_constant_clause_re *** 73,78 **** --- 73,79 ---- extern int NumRelids(Node *clause); extern void CommuteOpExpr(OpExpr *clause); + extern void CommuteScalarArrayOpExpr(ScalarArrayOpExpr *clause); extern void CommuteRowCompareExpr(RowCompareExpr *clause); extern Node *strip_implicit_coercions(Node *node); diff --git a/src/include/parser/parse_oper.h b/src/include/parser/parse_oper.h index 4ae8aef..f526a60 100644 *** a/src/include/parser/parse_oper.h --- b/src/include/parser/parse_oper.h *************** extern Oid oprfuncid(Operator op); *** 62,68 **** extern Expr *make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree, int location); extern Expr *make_scalar_array_op(ParseState *pstate, List *opname, ! bool useOr, Node *ltree, Node *rtree, int location); #endif /* PARSE_OPER_H */ --- 62,68 ---- extern Expr *make_op(ParseState *pstate, List *opname, Node *ltree, Node *rtree, int location); extern Expr *make_scalar_array_op(ParseState *pstate, List *opname, ! bool aryIsLeftArg, bool useOr, Node *ltree, Node *rtree, int location); #endif /* PARSE_OPER_H */ diff --git a/src/test/regress/expected/any_all.out b/src/test/regress/expected/any_all.out index ...1c664c7 . *** a/src/test/regress/expected/any_all.out --- b/src/test/regress/expected/any_all.out *************** *** 0 **** --- 1,662 ---- + --- + --- ANY/ALL Tests + --- + -- Array expressions + SELECT NULL::int = ANY(ARRAY[]::int[]); + ?column? + ---------- + f + (1 row) + + SELECT NULL::int = ANY(ARRAY[NULL::int]); + ?column? + ---------- + + (1 row) + + SELECT NULL::int = ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT 1 = ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + t + (1 row) + + SELECT 2 = ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + t + (1 row) + + SELECT 3 = ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT NULL::int = ALL(ARRAY[]::int[]); + ?column? + ---------- + t + (1 row) + + SELECT NULL::int = ALL(ARRAY[NULL::int]); + ?column? + ---------- + + (1 row) + + SELECT NULL::int = ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT 1 = ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + f + (1 row) + + SELECT 1 = ALL(ARRAY[NULL, 1]); + ?column? + ---------- + + (1 row) + + SELECT 1 = ALL(ARRAY[1]); + ?column? + ---------- + t + (1 row) + + SELECT 2 = ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + f + (1 row) + + SELECT 3 = ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + f + (1 row) + + SELECT NULL::int > ANY(ARRAY[]::int[]); + ?column? + ---------- + f + (1 row) + + SELECT NULL::int > ANY(ARRAY[NULL::int]); + ?column? + ---------- + + (1 row) + + SELECT NULL::int > ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT 1 > ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT 2 > ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + t + (1 row) + + SELECT 3 > ANY(ARRAY[NULL, 1, 2]); + ?column? + ---------- + t + (1 row) + + SELECT NULL::int > ALL(ARRAY[]::int[]); + ?column? + ---------- + t + (1 row) + + SELECT NULL::int > ALL(ARRAY[NULL::int]); + ?column? + ---------- + + (1 row) + + SELECT NULL::int > ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT 1 > ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + f + (1 row) + + SELECT 1 > ALL(ARRAY[NULL, 1]); + ?column? + ---------- + f + (1 row) + + SELECT 1 > ALL(ARRAY[1]); + ?column? + ---------- + f + (1 row) + + SELECT 2 > ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + f + (1 row) + + SELECT 3 > ALL(ARRAY[NULL, 1, 2]); + ?column? + ---------- + + (1 row) + + SELECT 3 > ALL(ARRAY[1, 2]); + ?column? + ---------- + t + (1 row) + + -- Array expressions reversed + SELECT (ANY(ARRAY[]::int[]) = NULL::int); + ?column? + ---------- + f + (1 row) + + SELECT (ANY(ARRAY[NULL::int]) = NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) = NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) = 1); + ?column? + ---------- + t + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) = 2); + ?column? + ---------- + t + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) = 3); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[]::int[]) = NULL::int); + ?column? + ---------- + t + (1 row) + + SELECT (ALL(ARRAY[NULL::int]) = NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) = NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) = 1); + ?column? + ---------- + f + (1 row) + + SELECT (ALL(ARRAY[NULL, 1]) = 1); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[1]) = 1); + ?column? + ---------- + t + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) = 2); + ?column? + ---------- + f + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) = 3); + ?column? + ---------- + f + (1 row) + + SELECT (ANY(ARRAY[]::int[]) < NULL::int); + ?column? + ---------- + f + (1 row) + + SELECT (ANY(ARRAY[NULL::int]) < NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) < NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) < 1); + ?column? + ---------- + + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) < 2); + ?column? + ---------- + t + (1 row) + + SELECT (ANY(ARRAY[NULL, 1, 2]) < 3); + ?column? + ---------- + t + (1 row) + + SELECT (ALL(ARRAY[]::int[]) < NULL::int); + ?column? + ---------- + t + (1 row) + + SELECT (ALL(ARRAY[NULL::int]) < NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) < NULL::int); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) < 1); + ?column? + ---------- + f + (1 row) + + SELECT (ALL(ARRAY[NULL, 1]) < 1); + ?column? + ---------- + f + (1 row) + + SELECT (ALL(ARRAY[1]) < 1); + ?column? + ---------- + f + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) < 2); + ?column? + ---------- + f + (1 row) + + SELECT (ALL(ARRAY[NULL, 1, 2]) < 3); + ?column? + ---------- + + (1 row) + + SELECT (ALL(ARRAY[1, 2]) < 3); + ?column? + ---------- + t + (1 row) + + -- Index and TID Scans + CREATE TABLE any_all_test (id int PRIMARY KEY); + NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "any_all_test_pkey" for table "any_all_test" + INSERT INTO any_all_test (id) SELECT id FROM generate_series(1, 100000) id; + ANALYZE any_all_test; + SELECT COUNT(*) FROM any_all_test WHERE id = ANY(ARRAY[1, 2, 3]); + count + ------- + 3 + (1 row) + + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE id = ANY(ARRAY[1, 2, 3]); + QUERY PLAN + ------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on any_all_test + Recheck Cond: (id = ANY ('{1,2,3}'::integer[])) + -> Bitmap Index Scan on any_all_test_pkey + Index Cond: (id = ANY ('{1,2,3}'::integer[])) + (5 rows) + + SELECT COUNT(*) FROM any_all_test WHERE (ANY(ARRAY[1, 2, 3]) = id); + count + ------- + 3 + (1 row) + + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE (ANY(ARRAY[1, 2, 3]) = id); + QUERY PLAN + ---------------------------------------------------------------------------------- + Aggregate + -> Bitmap Heap Scan on any_all_test + Recheck Cond: (ANY ('{1,2,3}'::integer[]) OPERATOR(pg_catalog.=) id) + -> Bitmap Index Scan on any_all_test_pkey + Index Cond: (ANY ('{1,2,3}'::integer[]) OPERATOR(pg_catalog.=) id) + (5 rows) + + SELECT COUNT(*) FROM any_all_test WHERE id > ALL(ARRAY[100000]); + count + ------- + 0 + (1 row) + + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE id > ALL(ARRAY[100000]); + QUERY PLAN + ---------------------------------------------------- + Aggregate + -> Seq Scan on any_all_test + Filter: (id > ALL ('{100000}'::integer[])) + (3 rows) + + SELECT COUNT(*) FROM any_all_test WHERE (ALL(ARRAY[100000]) < id); + count + ------- + 0 + (1 row) + + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE (ALL(ARRAY[100000]) < id); + QUERY PLAN + ------------------------------------------------------------------------- + Aggregate + -> Seq Scan on any_all_test + Filter: (ALL ('{100000}'::integer[]) OPERATOR(pg_catalog.<) id) + (3 rows) + + SELECT * FROM any_all_test WHERE CTID = ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]); + id + -------- + 100000 + (1 row) + + EXPLAIN (COSTS OFF) + SELECT * FROM any_all_test WHERE CTID = ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]); + QUERY PLAN + --------------------------------------------------------------------------- + Tid Scan on any_all_test + TID Cond: (ctid = ANY (ARRAY[$0])) + InitPlan 1 (returns $0) + -> Limit + -> Index Scan Backward using any_all_test_pkey on any_all_test + (5 rows) + + SELECT * FROM any_all_test WHERE (ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]) = CTID); + id + -------- + 100000 + (1 row) + + EXPLAIN (COSTS OFF) + SELECT * FROM any_all_test WHERE (ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]) = CTID); + QUERY PLAN + --------------------------------------------------------------------------- + Tid Scan on any_all_test + TID Cond: (ANY (ARRAY[$0]) OPERATOR(pg_catalog.=) ctid) + InitPlan 1 (returns $0) + -> Limit + -> Index Scan Backward using any_all_test_pkey on any_all_test + (5 rows) + + DROP TABLE any_all_test; + -- Constraints + -- Supposed to check that ANY/ALL work for expression also, + -- not only for full plans. Also checks that the reverse + -- form of ANY/ALL works for operators without commutators. + CREATE TABLE any_all_constraint_test ( + string TEXT[] CHECK ((ALL(string) ~ '^[a-z]*$')) + ); + INSERT INTO any_all_constraint_test VALUES (NULL); + INSERT INTO any_all_constraint_test VALUES (ARRAY[]::TEXT[]); + INSERT INTO any_all_constraint_test VALUES (ARRAY['a', 'b']); + -- Should fail + INSERT INTO any_all_constraint_test VALUES (ARRAY['a', 'B']); + ERROR: new row for relation "any_all_constraint_test" violates check constraint "any_all_constraint_test_string_check" + SELECT * FROM any_all_constraint_test; + string + -------- + + {} + {a,b} + (3 rows) + + DROP TABLE any_all_constraint_test; + -- Subselects + SELECT NULL::int = ANY(SELECT 0 WHERE FALSE); + ?column? + ---------- + f + (1 row) + + SELECT NULL::int = ANY(SELECT NULL::int); + ?column? + ---------- + + (1 row) + + SELECT NULL::int = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT 1 = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + t + (1 row) + + SELECT 2 = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + t + (1 row) + + SELECT 3 = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT NULL::int = ALL(SELECT 0 WHERE FALSE); + ?column? + ---------- + t + (1 row) + + SELECT NULL::int = ALL(SELECT NULL::int); + ?column? + ---------- + + (1 row) + + SELECT NULL::int = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT 1 = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + f + (1 row) + + SELECT 1 = ALL(SELECT NULL UNION SELECT 1); + ?column? + ---------- + + (1 row) + + SELECT 1 = ALL(SELECT 1); + ?column? + ---------- + t + (1 row) + + SELECT 2 = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + f + (1 row) + + SELECT 3 = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + f + (1 row) + + SELECT NULL::int > ANY(SELECT 0 WHERE FALSE); + ?column? + ---------- + f + (1 row) + + SELECT NULL::int > ANY(SELECT NULL::int); + ?column? + ---------- + + (1 row) + + SELECT NULL::int > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT 1 > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT 2 > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + t + (1 row) + + SELECT 3 > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + t + (1 row) + + SELECT NULL::int > ALL(SELECT 0 WHERE FALSE); + ?column? + ---------- + t + (1 row) + + SELECT NULL::int > ALL(SELECT NULL::int); + ?column? + ---------- + + (1 row) + + SELECT NULL::int > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT 1 > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + f + (1 row) + + SELECT 1 > ALL(SELECT NULL UNION SELECT 1); + ?column? + ---------- + f + (1 row) + + SELECT 1 > ALL(SELECT 1); + ?column? + ---------- + f + (1 row) + + SELECT 2 > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + f + (1 row) + + SELECT 3 > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + ?column? + ---------- + + (1 row) + + SELECT 3 > ALL(SELECT 1 UNION SELECT 2); + ?column? + ---------- + t + (1 row) + diff --git a/src/test/regress/expected/arrays.out b/src/test/regress/expected/arrays.out index 6e55349..6cd8a4d 100644 *** a/src/test/regress/expected/arrays.out --- b/src/test/regress/expected/arrays.out *************** select 33.4 > all (array[1,2,3]); *** 921,931 **** -- errors select 33 * any ('{1,2,3}'); ! ERROR: op ANY/ALL (array) requires operator to yield boolean LINE 1: select 33 * any ('{1,2,3}'); ^ select 33 * any (44); ! ERROR: op ANY/ALL (array) requires array on right side LINE 1: select 33 * any (44); ^ -- nulls --- 921,931 ---- -- errors select 33 * any ('{1,2,3}'); ! ERROR: ANY/ALL (array) requires operator to yield boolean LINE 1: select 33 * any ('{1,2,3}'); ^ select 33 * any (44); ! ERROR: ANY/ALL (expression) requires array expression LINE 1: select 33 * any (44); ^ -- nulls diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 376f28d..591ff25 100644 *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** test: select_into select_distinct select *** 79,85 **** # ---------- # Another group of parallel tests # ---------- ! test: privileges security_label collate test: misc # rules cannot run concurrently with any test that creates a view --- 79,85 ---- # ---------- # Another group of parallel tests # ---------- ! test: privileges security_label collate any_all test: misc # rules cannot run concurrently with any test that creates a view diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule index bb654f9..53ca8ec 100644 *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** ignore: random *** 81,86 **** --- 81,87 ---- test: random test: portals test: arrays + test: any_all test: btree_index test: hash_index test: update diff --git a/src/test/regress/sql/any_all.sql b/src/test/regress/sql/any_all.sql index ...3205065 . *** a/src/test/regress/sql/any_all.sql --- b/src/test/regress/sql/any_all.sql *************** *** 0 **** --- 1,167 ---- + --- + --- ANY/ALL Tests + --- + + -- Array expressions + + SELECT NULL::int = ANY(ARRAY[]::int[]); + SELECT NULL::int = ANY(ARRAY[NULL::int]); + SELECT NULL::int = ANY(ARRAY[NULL, 1, 2]); + SELECT 1 = ANY(ARRAY[NULL, 1, 2]); + SELECT 2 = ANY(ARRAY[NULL, 1, 2]); + SELECT 3 = ANY(ARRAY[NULL, 1, 2]); + + SELECT NULL::int = ALL(ARRAY[]::int[]); + SELECT NULL::int = ALL(ARRAY[NULL::int]); + SELECT NULL::int = ALL(ARRAY[NULL, 1, 2]); + SELECT 1 = ALL(ARRAY[NULL, 1, 2]); + SELECT 1 = ALL(ARRAY[NULL, 1]); + SELECT 1 = ALL(ARRAY[1]); + SELECT 2 = ALL(ARRAY[NULL, 1, 2]); + SELECT 3 = ALL(ARRAY[NULL, 1, 2]); + + SELECT NULL::int > ANY(ARRAY[]::int[]); + SELECT NULL::int > ANY(ARRAY[NULL::int]); + SELECT NULL::int > ANY(ARRAY[NULL, 1, 2]); + SELECT 1 > ANY(ARRAY[NULL, 1, 2]); + SELECT 2 > ANY(ARRAY[NULL, 1, 2]); + SELECT 3 > ANY(ARRAY[NULL, 1, 2]); + + SELECT NULL::int > ALL(ARRAY[]::int[]); + SELECT NULL::int > ALL(ARRAY[NULL::int]); + SELECT NULL::int > ALL(ARRAY[NULL, 1, 2]); + SELECT 1 > ALL(ARRAY[NULL, 1, 2]); + SELECT 1 > ALL(ARRAY[NULL, 1]); + SELECT 1 > ALL(ARRAY[1]); + SELECT 2 > ALL(ARRAY[NULL, 1, 2]); + SELECT 3 > ALL(ARRAY[NULL, 1, 2]); + SELECT 3 > ALL(ARRAY[1, 2]); + + -- Array expressions reversed + + SELECT (ANY(ARRAY[]::int[]) = NULL::int); + SELECT (ANY(ARRAY[NULL::int]) = NULL::int); + SELECT (ANY(ARRAY[NULL, 1, 2]) = NULL::int); + SELECT (ANY(ARRAY[NULL, 1, 2]) = 1); + SELECT (ANY(ARRAY[NULL, 1, 2]) = 2); + SELECT (ANY(ARRAY[NULL, 1, 2]) = 3); + + SELECT (ALL(ARRAY[]::int[]) = NULL::int); + SELECT (ALL(ARRAY[NULL::int]) = NULL::int); + SELECT (ALL(ARRAY[NULL, 1, 2]) = NULL::int); + SELECT (ALL(ARRAY[NULL, 1, 2]) = 1); + SELECT (ALL(ARRAY[NULL, 1]) = 1); + SELECT (ALL(ARRAY[1]) = 1); + SELECT (ALL(ARRAY[NULL, 1, 2]) = 2); + SELECT (ALL(ARRAY[NULL, 1, 2]) = 3); + + SELECT (ANY(ARRAY[]::int[]) < NULL::int); + SELECT (ANY(ARRAY[NULL::int]) < NULL::int); + SELECT (ANY(ARRAY[NULL, 1, 2]) < NULL::int); + SELECT (ANY(ARRAY[NULL, 1, 2]) < 1); + SELECT (ANY(ARRAY[NULL, 1, 2]) < 2); + SELECT (ANY(ARRAY[NULL, 1, 2]) < 3); + + SELECT (ALL(ARRAY[]::int[]) < NULL::int); + SELECT (ALL(ARRAY[NULL::int]) < NULL::int); + SELECT (ALL(ARRAY[NULL, 1, 2]) < NULL::int); + SELECT (ALL(ARRAY[NULL, 1, 2]) < 1); + SELECT (ALL(ARRAY[NULL, 1]) < 1); + SELECT (ALL(ARRAY[1]) < 1); + SELECT (ALL(ARRAY[NULL, 1, 2]) < 2); + SELECT (ALL(ARRAY[NULL, 1, 2]) < 3); + SELECT (ALL(ARRAY[1, 2]) < 3); + + -- Index and TID Scans + + CREATE TABLE any_all_test (id int PRIMARY KEY); + INSERT INTO any_all_test (id) SELECT id FROM generate_series(1, 100000) id; + ANALYZE any_all_test; + + SELECT COUNT(*) FROM any_all_test WHERE id = ANY(ARRAY[1, 2, 3]); + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE id = ANY(ARRAY[1, 2, 3]); + + SELECT COUNT(*) FROM any_all_test WHERE (ANY(ARRAY[1, 2, 3]) = id); + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE (ANY(ARRAY[1, 2, 3]) = id); + + SELECT COUNT(*) FROM any_all_test WHERE id > ALL(ARRAY[100000]); + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE id > ALL(ARRAY[100000]); + + SELECT COUNT(*) FROM any_all_test WHERE (ALL(ARRAY[100000]) < id); + EXPLAIN (COSTS OFF) + SELECT COUNT(*) FROM any_all_test WHERE (ALL(ARRAY[100000]) < id); + + SELECT * FROM any_all_test WHERE CTID = ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]); + EXPLAIN (COSTS OFF) + SELECT * FROM any_all_test WHERE CTID = ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]); + + SELECT * FROM any_all_test WHERE (ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]) = CTID); + EXPLAIN (COSTS OFF) + SELECT * FROM any_all_test WHERE (ANY(ARRAY[( + SELECT CTID FROM any_all_test ORDER BY ID DESC LIMIT 1 + )]) = CTID); + + DROP TABLE any_all_test; + + -- Constraints + -- Supposed to check that ANY/ALL work for expression also, + -- not only for full plans. Also checks that the reverse + -- form of ANY/ALL works for operators without commutators. + + CREATE TABLE any_all_constraint_test ( + string TEXT[] CHECK ((ALL(string) ~ '^[a-z]*$')) + ); + + INSERT INTO any_all_constraint_test VALUES (NULL); + INSERT INTO any_all_constraint_test VALUES (ARRAY[]::TEXT[]); + INSERT INTO any_all_constraint_test VALUES (ARRAY['a', 'b']); + -- Should fail + INSERT INTO any_all_constraint_test VALUES (ARRAY['a', 'B']); + + SELECT * FROM any_all_constraint_test; + + DROP TABLE any_all_constraint_test; + + -- Subselects + + SELECT NULL::int = ANY(SELECT 0 WHERE FALSE); + SELECT NULL::int = ANY(SELECT NULL::int); + SELECT NULL::int = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 1 = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 2 = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 3 = ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + + SELECT NULL::int = ALL(SELECT 0 WHERE FALSE); + SELECT NULL::int = ALL(SELECT NULL::int); + SELECT NULL::int = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 1 = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 1 = ALL(SELECT NULL UNION SELECT 1); + SELECT 1 = ALL(SELECT 1); + SELECT 2 = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 3 = ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + + SELECT NULL::int > ANY(SELECT 0 WHERE FALSE); + SELECT NULL::int > ANY(SELECT NULL::int); + SELECT NULL::int > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 1 > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 2 > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 3 > ANY(SELECT NULL UNION SELECT 1 UNION SELECT 2); + + SELECT NULL::int > ALL(SELECT 0 WHERE FALSE); + SELECT NULL::int > ALL(SELECT NULL::int); + SELECT NULL::int > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 1 > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 1 > ALL(SELECT NULL UNION SELECT 1); + SELECT 1 > ALL(SELECT 1); + SELECT 2 > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 3 > ALL(SELECT NULL UNION SELECT 1 UNION SELECT 2); + SELECT 3 > ALL(SELECT 1 UNION SELECT 2);