pg_anyall_reversed.v0.patch
application/octet-stream
Filename: pg_anyall_reversed.v0.patch
Type: application/octet-stream
Part: 0
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(<query>) <op> <expr> 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);