Boolean operators without commutators vs. ALL/ANY
Hi
I've recently wanted to define a check constraint on an array
column that verifies that all array entries match some regular
expression. Unfortunately, t
The most natural way of expressing such a check would be
CHECK ('<regexp>' ~ ANY(field)),
but that doesn't work, because "~" expects the *value*
to be the left argument and the *pattern* to be the right.
The next try was
CHECK (ANY(field) ~ '<regexp>'),
but that doesn't even parse.
Ok, so then use UNNEST() and BOOL_AND() I figured, and wrote
CHECK ((SELECT BOOL_AND(v ~ '<regexp>') FROM UNNEST(field) v)).
But that of course lead to nothing but
ERROR: cannot use subquery in check constraint
So I the end, I had to wrap the sub-query in a SQL-language
function and use that in the check constraint. While this
solved my immediate problem, the necessity of doing that
highlights a few problems
(A) "~" is an extremely bad name for the regexp-matching
operators, since it's visual form is symmetric but it's
behaviour isn't. This doesn't only make its usage very
error-prone, it also makes it very hard to come up with
sensible name for an commutator of "~". I suggest that we
add "=~" as an alias for "~", "~=" as an commutator
for "=~", and deprecate "~". The same holds for "~~".
We might want to do this starting with 9.1.
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.
Ideally, we'd support "ANY(<array>) <operator> <value>",
but if that's not possible grammar-wise, I suggest we extend
the OPERATOR() syntax to allow
<value> OPERATOR(COMMUTATOR <operator>) ANY(<array>).
OPERATOR(COMMUTATOR <operator>) would use the COMMUTATOR
of the specified operator if one exists, and otherwise
use the original operator with the arguments swapped.
(C) Why do we forbid sub-queries in CHECK constraints?
I do realize that any non-IMMUTABLE CHECK constraint is
a foot-gun, but since we already allow STABLE and even
VOLATILE functions to be used inside CHECK constraint,
forbidding sub-queries seems a bit pointless...
best regards,
Florian Pflug
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
So I the end, I had to wrap the sub-query in a SQL-language
function and use that in the check constraint. While this
solved my immediate problem, the necessity of doing that
highlights a few problems(A) "~" is an extremely bad name for the regexp-matching
operators, since it's visual form is symmetric but it's
behaviour isn't. This doesn't only make its usage very
error-prone, it also makes it very hard to come up with
sensible name for an commutator of "~". I suggest that we
add "=~" as an alias for "~", "~=" as an commutator
for "=~", and deprecate "~". The same holds for "~~".
Does any other database or programming language implement it this way?
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.
Ideally, we'd support "ANY(<array>) <operator> <value>",
but if that's not possible grammar-wise, I suggest we extend
the OPERATOR() syntax to allow
<value> OPERATOR(COMMUTATOR <operator>) ANY(<array>).
OPERATOR(COMMUTATOR <operator>) would use the COMMUTATOR
of the specified operator if one exists, and otherwise
use the original operator with the arguments swapped.
It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names. I'm
not crazy about the proposed syntax, though; it seems cumbersome, and
it's really only needed for SOME/ALL/ANY, not in general operator
expressions. Since ANY is a reserved keyword, I believe we could
allow something like "expr op ANY BACKWARD ( ... )" -- or some other
keyword in lieu of BACKWARD if you prefer.
Hath the spec anything to say about this?
(C) Why do we forbid sub-queries in CHECK constraints?
I do realize that any non-IMMUTABLE CHECK constraint is
a foot-gun, but since we already allow STABLE and even
VOLATILE functions to be used inside CHECK constraint,
forbidding sub-queries seems a bit pointless...
Dunno. Maybe it's just an implementation restriction?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.
It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names.
There are syntactic reasons not to do that. It'd be a lot easier just
to provide a commutator operator for ~.
(C) Why do we forbid sub-queries in CHECK constraints?
Dunno. Maybe it's just an implementation restriction?
(1) We don't want to invoke the planner in the places where we'd
have to do so to make that work.
(2) It's just about inevitable that a sub-query would have results
dependent on other rows beside the one being checked. As such, it
would be trying to enforce semantics that you simply can't enforce
via CHECK. (And yes, you can bypass that with a function, but guess
what: it still won't actually work.)
regards, tom lane
On Sun, Jun 12, 2011 at 11:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names.There are syntactic reasons not to do that. It'd be a lot easier just
to provide a commutator operator for ~.
Details?
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Jun13, 2011, at 05:12 , Robert Haas wrote:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
So I the end, I had to wrap the sub-query in a SQL-language
function and use that in the check constraint. While this
solved my immediate problem, the necessity of doing that
highlights a few problems(A) "~" is an extremely bad name for the regexp-matching
operators, since it's visual form is symmetric but it's
behaviour isn't. This doesn't only make its usage very
error-prone, it also makes it very hard to come up with
sensible name for an commutator of "~". I suggest that we
add "=~" as an alias for "~", "~=" as an commutator
for "=~", and deprecate "~". The same holds for "~~".Does any other database or programming language implement it this way?
Ruby has "=~", which returns the position of the regexp's first
match, or nil if there is none.
$ ruby -e "puts 'hello' =~ /l+/"
2
$ ruby -e "puts 'hello' =~ /x+/"
nil
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.
Ideally, we'd support "ANY(<array>) <operator> <value>",
but if that's not possible grammar-wise, I suggest we extend
the OPERATOR() syntax to allow
<value> OPERATOR(COMMUTATOR <operator>) ANY(<array>).
OPERATOR(COMMUTATOR <operator>) would use the COMMUTATOR
of the specified operator if one exists, and otherwise
use the original operator with the arguments swapped.It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names.
Well, the issue of "~" being anti-self-explanatory remains
independent from whether we do (B) or not.
I'm
not crazy about the proposed syntax, though; it seems cumbersome, and
it's really only needed for SOME/ALL/ANY, not in general operator
expressions. Since ANY is a reserved keyword, I believe we could
allow something like "expr op ANY BACKWARD ( ... )" -- or some other
keyword in lieu of BACKWARD if you prefer.
Hm, that's less bulky but more kludgy, I'd say. But wait a minute...
If ANY and ALL are reserved anyway, should it be possible to
make "(ANY(..) <op> <expr>)" and "(ALL(...) <op> <expr>)"
work grammar-wise? (Note the enclosing parens)
I just tried that, and it seems to work. bison doesn't report
and conflicts, the regression tests still succeed, and
I get the following
postgres=# select (all(array[1,2]) = 1);
ERROR: ANY()/ALL() <op> <expr> is not yet implemented at character 9
STATEMENT: select (all(array[1,2]) = 1);
ERROR: ANY()/ALL() <op> <expr> is not yet implemented
LINE 1: select (all(array[1,2]) = 1);
^
I've attached a patch with the changes to gram.y.
best regards,
Florian Pflug
Attachments:
pg_anyall_reversed.v0.patchapplication/octet-stream; name=pg_anyall_reversed.v0.patchDownload
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1d39674..d1ff0f3 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() <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
+ {
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("ANY()/ALL() <op> <expr> is not yet implemented"),
+ parser_errposition(@2)));
+ }
| UNIQUE select_with_parens
{
/* Not sure how to get rid of the parentheses
On Sun, Jun 12, 2011 at 6:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.
FWIW, in case people were unaware, this is getting close to Perl 6
junctions/superpositions. See:
<http://dev.perl.org/perl6/doc/design/exe/E06.html> "The Wonderful
World of Junctions". Beyond "any" and "all" they propose "one" and
"none".
On Jun13, 2011, at 05:44 , Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(C) Why do we forbid sub-queries in CHECK constraints?
Dunno. Maybe it's just an implementation restriction?
(1) We don't want to invoke the planner in the places where we'd
have to do so to make that work.
Hm, OK, I get that.
(2) It's just about inevitable that a sub-query would have results
dependent on other rows beside the one being checked. As such, it
would be trying to enforce semantics that you simply can't enforce
via CHECK. (And yes, you can bypass that with a function, but guess
what: it still won't actually work.)
Yeah, I never expected non-immutable CHECK constraints to work. I was
simply pointing out that UNNEST() allows one to write all kinds of
interesting CHECK constraints, all of which are immutable.
Now, if supporting these would increase code complexity, or cause
a performance drop for non-sub-query CHECK constraints, I'm perfectly
fine with leaving them unsupported. I just wanted to make sure we
aren't simply nannying the user - especially since it's a nanny who's
out-smarted by function calls.
best regards,
Florian Pflug
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 11:44 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
There are syntactic reasons not to do that. It'd be a lot easier just
to provide a commutator operator for ~.
Details?
Well, for one, it becomes unobvious what
A op ANY (B) op C
means. This has come up before, and I seem to recall that we identified
some more-compelling problems, but that's the best I can do before
consuming any caffeine.
In any case, if you dig around enough to notice all the infrastructure
that's involved with this, you'll definitely come to the conclusion that
it'd be a lot less work to just add the missing commutator operators.
regards, tom lane
On Mon, Jun 13, 2011 at 3:01 AM, Florian Pflug <fgp@phlo.org> wrote:
On Jun13, 2011, at 05:12 , Robert Haas wrote:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
So I the end, I had to wrap the sub-query in a SQL-language
function and use that in the check constraint. While this
solved my immediate problem, the necessity of doing that
highlights a few problems(A) "~" is an extremely bad name for the regexp-matching
operators, since it's visual form is symmetric but it's
behaviour isn't. This doesn't only make its usage very
error-prone, it also makes it very hard to come up with
sensible name for an commutator of "~". I suggest that we
add "=~" as an alias for "~", "~=" as an commutator
for "=~", and deprecate "~". The same holds for "~~".Does any other database or programming language implement it this way?
Ruby has "=~", which returns the position of the regexp's first
match, or nil if there is none.$ ruby -e "puts 'hello' =~ /l+/"
2
$ ruby -e "puts 'hello' =~ /x+/"
nil
Sure. Some languages use =~ and some use just ~... I was just
wondering if anyone thought the commutator of =~ was ~=...
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On 06/13/2011 10:07 AM, Robert Haas wrote:
Some languages use =~ and some use just ~... I was just
wondering if anyone thought the commutator of =~ was ~=...
My feeling is it's a bit dangerous. It's too easy to fat-finger the
reverse op, and get something quite unintended.
cheers
andrew (whose lack of coordination sadly increases with age)
On Mon, Jun 13, 2011 at 09:01:45AM +0200, Florian Pflug wrote:
Hm, that's less bulky but more kludgy, I'd say. But wait a minute...
If ANY and ALL are reserved anyway, should it be possible to
make "(ANY(..) <op> <expr>)" and "(ALL(...) <op> <expr>)"
work grammar-wise? (Note the enclosing parens)
This would be a very, very useful feature. :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Jun13, 2011, at 16:19 , Andrew Dunstan wrote:
On 06/13/2011 10:07 AM, Robert Haas wrote:
Some languages use =~ and some use just ~... I was just
wondering if anyone thought the commutator of =~ was ~=...My feeling is it's a bit dangerous. It's too easy to fat-finger the reverse op, and get something quite unintended.
Well, but with "~" you need to *remember* that the regexp
goes on the right side and the text on the left. That seems
worse than the risk of fat-fingering "=~" and getting "~=".
At, at least, have looked up the argument order of "~"
countless in the past...
best regards,
Florian Pflug
On Jun13, 2011, at 05:44 , Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names.There are syntactic reasons not to do that. It'd be a lot easier just
to provide a commutator operator for ~.
My suggestion would be the add a commutator for "~" as a short-term
solution (preferably in 9.1).
Since "~" doesn't inspire any obvious names for a possible commutator,
I suggest adding "=~" and "~=".
Is there any support for that proposal?
In the long term, I'd like to add support for "(ANY() <op> <expr>)"
(Note the enclosing parens). I've checked that this works grammar-wise,
but haven't no idea how much tweaking the executor needs to support
that...
best regards,
Florian Pflug
On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug <fgp@phlo.org> wrote:
On Jun13, 2011, at 05:44 , Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names.There are syntactic reasons not to do that. It'd be a lot easier just
to provide a commutator operator for ~.My suggestion would be the add a commutator for "~" as a short-term
solution (preferably in 9.1).
I don't think we want to bump catversion again before release if we
can avoid it. And I don't see this as being a terribly urgent problem
- it's not like this is a new regression, and I can't remember hearing
any complaints about it prior to two days ago.
Since "~" doesn't inspire any obvious names for a possible commutator,
I suggest adding "=~" and "~=".Is there any support for that proposal?
I'm OK with adding a commutator but I guess I don't see the point of
adding a synonym for ~ along the way. The existing use of ~ is
consistent with, for example, awk, so it's not like we've dreamed up
something utterly crazy that we now need to fix. I'd suggest we just
come up with some arbitrary variant, like ~~ or <~ or #~ or
!#!%@~bikeshed++!.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Jun14, 2011, at 14:29 , Robert Haas wrote:
On Tue, Jun 14, 2011 at 6:10 AM, Florian Pflug <fgp@phlo.org> wrote:
On Jun13, 2011, at 05:44 , Tom Lane wrote:
Robert Haas <robertmhaas@gmail.com> writes:
On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp@phlo.org> wrote:
(B) There should be a way to use ANY()/ALL() with the
array elements becoming the left arguments of the operator.It seems to me that if we provided some way of handling this, your
first proposal would be moot; and I have to say I like the idea of
allowing this a lot more than tinkering with the operator names.There are syntactic reasons not to do that. It'd be a lot easier just
to provide a commutator operator for ~.My suggestion would be the add a commutator for "~" as a short-term
solution (preferably in 9.1).I don't think we want to bump catversion again before release if we
can avoid it. And I don't see this as being a terribly urgent problem
- it's not like this is a new regression, and I can't remember hearing
any complaints about it prior to two days ago.
Hm, OK, that makes sense...
Since "~" doesn't inspire any obvious names for a possible commutator,
I suggest adding "=~" and "~=".Is there any support for that proposal?
I'm OK with adding a commutator but I guess I don't see the point of
adding a synonym for ~ along the way. The existing use of ~ is
consistent with, for example, awk, so it's not like we've dreamed up
something utterly crazy that we now need to fix. I'd suggest we just
come up with some arbitrary variant, like ~~ or <~ or #~ or
!#!%@~bikeshed++!.
That, however, I'm not at all happy with. Quite frankly, operator
naming is already a bit of a mess, and readability of queries
suffers as a result. The geometric types are especially vile
offenders in this regard, but the various array-related operators
aren't poster children either.
I think we should try to work towards more mnemonic operator
naming, not add to the mess by defining commutator pairs whose
names bear no visual resemblance whatsoever to one each other.
I'm not wedded to "=~", it's just the only name I could come
up which
(a) has a natural commutator
(b) gives visual indication of which argument constitutes the
text and which the pattern
(c) there is precedent for.
BTW, there's actually precedent for a commutator of "~", namely
"@". Some of the geometric types (polygon, box, circle, point,
path) use "~" as a commutator for "@" (which stands for "contains").
But IMHO that mainly proves that the geometric types are vile
offenders when it comes to readability...
The pair ("@", "~" ) is also the only pair of commutators whose
names are totally unrelated to each other. Given a suitable
definition of a reverse() function for text [1]I used create or replace function reverse(text) returns text as $$ select string_agg(substring($1, i, 1), '') from generate_series(length($1), 1, -1) i $$ language sql immutable;, the following query
select
o1.oprleft::regtype || ' ' || o1.oprname || ' ' || o1.oprright::regtype as opr,
o2.oprleft::regtype || ' ' || o2.oprname || ' ' || o2.oprright::regtype as com,
o1.oprcode as opr_code,
o2.oprcode as com_code
from pg_operator o1
join pg_operator o2 on o1.oprcom = o2.oid or o2.oprcom = o1.oid
where
o1.oid < o2.oid and
o1.oprname <> reverse(translate(o2.oprname, '<>', '><')) and
o1.oprname <> translate(o2.oprname, '<>', '><');
produces
opr | com | opr_code | com_code
-------------------+-------------------+---------------------+-------------------
polygon @ polygon | polygon ~ polygon | poly_contained | poly_contain
box @ box | box ~ box | box_contained | box_contain
circle @ circle | circle ~ circle | circle_contained | circle_contain
point @ path | path ~ point | on_ppath | path_contain_pt
point @ polygon | polygon ~ point | pt_contained_poly | poly_contain_pt
point @ circle | circle ~ point | pt_contained_circle | circle_contain_pt
(6 rows)
best regards,
Florian Pflug
[1]: I used create or replace function reverse(text) returns text as $$ select string_agg(substring($1, i, 1), '') from generate_series(length($1), 1, -1) i $$ language sql immutable;
create or replace function reverse(text) returns text as $$
select string_agg(substring($1, i, 1), '') from generate_series(length($1), 1, -1) i
$$ language sql immutable;
On Jun13, 2011, at 17:41 , David Fetter wrote:
On Mon, Jun 13, 2011 at 09:01:45AM +0200, Florian Pflug wrote:
Hm, that's less bulky but more kludgy, I'd say. But wait a minute...
If ANY and ALL are reserved anyway, should it be possible to
make "(ANY(..) <op> <expr>)" and "(ALL(...) <op> <expr>)"
work grammar-wise? (Note the enclosing parens)This would be a very, very useful feature. :)
There, you dared me. So here's a patch :-P
So far it supports (ANY(array) op scalar) and (ALL(array) op scalar),
but not (ANY/ALL(subselect) op scalar). "ANY/ALL op scalar" is
supported independent from whether <op> has a commutator or not.
In the latter case, the clause isn't indexable, but thats no
different from the case "const op field". Both ANY(...) = ctid
and ctid = ANY(...) are correctly executed as TID scans (if the
array expression is a pseudo-constant, that is).
The patch adds a fields "aryArgIdx" (0 or 1) to ScalarArrayOpExpr
which stores the index of the array-valued argument. Thus, for
the traditional "scalar op ANY/ALL(array)" variant, aryArgIdx == 1,
for "ANY/ALL(array) op scalar" it's zero.
I've updates all places that I could find which inspect
ScalarArrayOpExprs to take the aryArgIdx into account. And boy,
those were quite a few, which explains the size of the patch.
Most of the changes are pretty trivial, though.
The indexing support works similar to the case "scalar op scalar",
i.e. match_clause_to_indexcol() consideres the clause to be index-able
if the scalar argument matches an index definition, and relies on
fix_indexqual_references() to commute the ScalarArrayOpExprs if
the scalar argument is on the right side instead of on the left.
I noticed that ANY/ALL is hardly exercised by the regression tests
at all, so I added a (pretty exhaustive, I think) test any_all.
The test exercises the traditional and the reversed form of
ANY/ALL and verify that an index is used if possible.
Comments are extremely welcome, especially ones regarding
the overall approach taken in this patch. If people consider
that to be acceptable, I'd try to add the missing features
and add documentation.
best regards,
Florian Pflug
Attachments:
pg_anyall_reversed.v0.patchapplication/octet-stream; name=pg_anyall_reversed.v0.patchDownload
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);
Florian Pflug <fgp@phlo.org> writes:
Comments are extremely welcome, especially ones regarding
the overall approach taken in this patch. If people consider
that to be acceptable, I'd try to add the missing features
and add documentation.
Quite honestly, I don't like this one bit and would rather you not
pursue the idea. There is no such syntax in the standard, and
presumably that's not because the SQL committee never thought of it.
They may have some incompatible idea in mind for the future, who knows?
But in any case, this won't provide any functionality whatever that we
couldn't provide at much less effort and risk, just by providing
commutator operators for the few missing cases.
(FWIW, I've come around to liking the idea of using =~ and the obvious
variants of that for regex operators, mainly because of the Perl
precedent.)
regards, tom lane
On mån, 2011-06-13 at 10:19 -0400, Andrew Dunstan wrote:
On 06/13/2011 10:07 AM, Robert Haas wrote:
Some languages use =~ and some use just ~... I was just
wondering if anyone thought the commutator of =~ was ~=...My feeling is it's a bit dangerous. It's too easy to fat-finger the
reverse op, and get something quite unintended.
Yes, it looked highly dangerous to me as well.
On tis, 2011-06-14 at 15:38 +0200, Florian Pflug wrote:
BTW, there's actually precedent for a commutator of "~", namely
"@". Some of the geometric types (polygon, box, circle, point,
path) use "~" as a commutator for "@" (which stands for "contains").
I wouldn't have a problem with naming the reverse operator "@".
On ons, 2011-06-15 at 22:19 -0400, Tom Lane wrote:
(FWIW, I've come around to liking the idea of using =~ and the obvious
variants of that for regex operators, mainly because of the Perl
precedent.)
Maybe I'm not completely up to date on this, but I observe that Perl
itself doesn't appear to have a commutator for =~ .
Peter Eisentraut <peter_e@gmx.net> writes:
On tis, 2011-06-14 at 15:38 +0200, Florian Pflug wrote:
BTW, there's actually precedent for a commutator of "~", namely
"@". Some of the geometric types (polygon, box, circle, point,
path) use "~" as a commutator for "@" (which stands for "contains").
I wouldn't have a problem with naming the reverse operator "@".
We deprecated those names for the geometric operators largely because
there wasn't any visual correlation between the commutator pairs.
I can't see introducing the same pairing for regex operators if we
already decided the geometric case was a bad idea.
regards, tom lane
Peter Eisentraut <peter_e@gmx.net> writes:
On ons, 2011-06-15 at 22:19 -0400, Tom Lane wrote:
(FWIW, I've come around to liking the idea of using =~ and the obvious
variants of that for regex operators, mainly because of the Perl
precedent.)
Maybe I'm not completely up to date on this, but I observe that Perl
itself doesn't appear to have a commutator for =~ .
Nope, it doesn't. But that doesn't mean we don't need one. Aside from
the ANY/ALL business, the index infrastructure is asymmetrical: it will
only deal with indexable WHERE clauses that have the index column on the
left. So those are two very good reasons to make sure that
operators returning boolean all have commutators. (I will refrain for
the moment from speculating whether we'll ever have an index type that
supports regexp match directly as an indexable operator...)
At the moment, this query:
select oid::regoperator, oprcode from pg_operator
where oprkind = 'b' and oprresult = 'bool'::regtype and oprcom = 0;
says we have 83 such operators without commutators. Of these, if I'm
counting correctly, 26 are the LIKE and regex operators in question.
It looks like at least another twenty do in fact constitute commutator
pairs, they're just not documented as such via oprcom. The remaining
forty or less are a morass of functions for obsolete types, duplicate
names for the same function, etc. I don't think it's unreasonable at
all to establish an expectation that all non-legacy binary boolean
operators should come in commutator pairs.
regards, tom lane
On Jun16, 2011, at 04:19 , Tom Lane wrote:
Florian Pflug <fgp@phlo.org> writes:
Comments are extremely welcome, especially ones regarding
the overall approach taken in this patch. If people consider
that to be acceptable, I'd try to add the missing features
and add documentation.Quite honestly, I don't like this one bit and would rather you not
pursue the idea. There is no such syntax in the standard, and
presumably that's not because the SQL committee never thought of it.
They may have some incompatible idea in mind for the future, who knows?
The SQL standard doesn't have CREATE OPERATOR though, so for them
the asymmetry of the ANY/ALL constructs don't translate to a missing
feature, though. For us, however it does. We might try to doge that
by decreeing that boolean operators better have commutators, but that
doesn't help for non-core-defined operators.
So I'd very much like us to provide some way to get the effect
of "ANY/ALL op scalar" without having to resort to UNNEST and BOOL_AND/OR.
But I'm absolutely not wedded to the syntax "ANY/ALL op scalar".
One other idea I've been kicking around is to generate commutators
automatically for all binary operators that return boolean. If no name
for the commutator is specified (i.e. if CREATE OPERATOR is called without
a value for COMMUTATOR), it's be named "COMMUTATOR <op>", and would thus
be useably only via OPERATOR(...).
Implementation-wise we'd need to add a flag to FmgrInfo which tells
the fmgr to swap the function's arguments, and would need to centralize
the translation of operator OIDs to FmgrInfos.
best regards,
Florian Pflug
On Thu, Jun 16, 2011 at 12:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We deprecated those names for the geometric operators largely because
there wasn't any visual correlation between the commutator pairs.
I can't see introducing the same pairing for regex operators if we
already decided the geometric case was a bad idea.
I'm having trouble avoiding the conclusion that we're trying to shove
a round peg into a square hole. The idea that we have to have a
commutator for every operator just because we don't handle left and
right symmetrically sits poorly with me. I can't really argue with
your statement that it's the easiest way to address Florian's gripe,
but because it almost surely is. But it still feels like a kludge.
The syntax foo = ANY(bar) is really quite a poorly-designed syntax,
because the top-level operation is really "ANY", and it has three
arguments: foo, =, bar. If the SQL committee had standardized on
ANY(foo = $0, bar) or some such thing we wouldn't be having this
conversation.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Robert Haas <robertmhaas@gmail.com> writes:
I'm having trouble avoiding the conclusion that we're trying to shove
a round peg into a square hole. The idea that we have to have a
commutator for every operator just because we don't handle left and
right symmetrically sits poorly with me. I can't really argue with
your statement that it's the easiest way to address Florian's gripe,
but because it almost surely is. But it still feels like a kludge.
The syntax foo = ANY(bar) is really quite a poorly-designed syntax,
because the top-level operation is really "ANY", and it has three
arguments: foo, =, bar. If the SQL committee had standardized on
ANY(foo = $0, bar) or some such thing we wouldn't be having this
conversation.
[ shrug... ] Take it up with the committee. The syntax is what it is,
and we should select our operators to fit it, not vice versa.
regards, tom lane
On Jun16, 2011, at 19:54 , Robert Haas wrote:
On Thu, Jun 16, 2011 at 12:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We deprecated those names for the geometric operators largely because
there wasn't any visual correlation between the commutator pairs.
I can't see introducing the same pairing for regex operators if we
already decided the geometric case was a bad idea.I'm having trouble avoiding the conclusion that we're trying to shove
a round peg into a square hole. The idea that we have to have a
commutator for every operator just because we don't handle left and
right symmetrically sits poorly with me. I can't really argue with
your statement that it's the easiest way to address Florian's gripe,
but because it almost surely is. But it still feels like a kludge.
Well, I think there are basically three choices here, kludge or no
kludge.
(1) We either decree once and for all that binary operations ought to
have commutators, modify CREATE TYPE to issue a warning if you
create one without, add the missing ones, and add a check for
that to opr_sanity (possibly excluding some deprecated operators).
or
(2) We arrange for commutators of binary operators to be created
automatically.
or
(3) Or we bit the bullet and provide something similar to
"ANY/ALL op scalar". We do have the liberty to pick whatever syntax we
feel comfortable with, though, since we're out of SQL standard territory
anyway.
What I *wouldn't* like us to is just a few missing commutators and be
done with it. That pretty much guarantees that this issue will pop up
again some time in the future.
I personally prefer (3), but would also be content with (1), and be
ready to provide a patch for that. To be fair, (1) really doesn't seem
that kludgy if one takes into account that all indexable operators must
have commutators anyway.
I haven't checked how viable (2) actually is, but I dare say that it's
probably quite a bit of work. Essentially, we'd need a way to automatically
swap a function's argument before invoking the function, which I'm not
sure that fmgr can cleanly be persuaded to do.
Now all that's required is to agree on a way forward ;-)
best regards,
Florian Pflug
On Thu, Jun 16, 2011 at 2:22 PM, Florian Pflug <fgp@phlo.org> wrote:
On Jun16, 2011, at 19:54 , Robert Haas wrote:
On Thu, Jun 16, 2011 at 12:50 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
We deprecated those names for the geometric operators largely because
there wasn't any visual correlation between the commutator pairs.
I can't see introducing the same pairing for regex operators if we
already decided the geometric case was a bad idea.I'm having trouble avoiding the conclusion that we're trying to shove
a round peg into a square hole. The idea that we have to have a
commutator for every operator just because we don't handle left and
right symmetrically sits poorly with me. I can't really argue with
your statement that it's the easiest way to address Florian's gripe,
but because it almost surely is. But it still feels like a kludge.Well, I think there are basically three choices here, kludge or no
kludge.(1) We either decree once and for all that binary operations ought to
have commutators, modify CREATE TYPE to issue a warning if you
create one without, add the missing ones, and add a check for
that to opr_sanity (possibly excluding some deprecated operators).or
(2) We arrange for commutators of binary operators to be created
automatically.or
(3) Or we bit the bullet and provide something similar to
"ANY/ALL op scalar". We do have the liberty to pick whatever syntax we
feel comfortable with, though, since we're out of SQL standard territory
anyway.What I *wouldn't* like us to is just a few missing commutators and be
done with it. That pretty much guarantees that this issue will pop up
again some time in the future.I personally prefer (3), but would also be content with (1), and be
ready to provide a patch for that. To be fair, (1) really doesn't seem
that kludgy if one takes into account that all indexable operators must
have commutators anyway.I haven't checked how viable (2) actually is, but I dare say that it's
probably quite a bit of work. Essentially, we'd need a way to automatically
swap a function's argument before invoking the function, which I'm not
sure that fmgr can cleanly be persuaded to do.Now all that's required is to agree on a way forward ;-)
Well, Tom seems pretty strongly in favor of #1, or some variant of it,
and while I don't find that to be enormously elegant it does have the
virtue of being quite a bit less work than any of the other options.
I think the chances of that being a complete and permanent solution
are less than 50%, but perhaps it's close enough for government work.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
Florian Pflug <fgp@phlo.org> writes:
Well, I think there are basically three choices here, kludge or no
kludge.
(1) We either decree once and for all that binary operations ought to
have commutators, modify CREATE TYPE to issue a warning if you
create one without, add the missing ones, and add a check for
that to opr_sanity (possibly excluding some deprecated operators).
or
(2) We arrange for commutators of binary operators to be created
automatically.
or
(3) Or we bit the bullet and provide something similar to
"ANY/ALL op scalar". We do have the liberty to pick whatever syntax we
feel comfortable with, though, since we're out of SQL standard territory
anyway.
All three of these are massive overkill. What we need is a general
policy that providing commutators is a good idea. We do not need to try
to make it 100.00% with an enforcement mechanism. As for #2, what's
your plan for automatically selecting a commutator operator name?
(Having said that, I *was* thinking of adding an opr_sanity test ... but
not expecting that we'd get it to find zero rows.)
regards, tom lane
On tor, 2011-06-16 at 00:50 -0400, Tom Lane wrote:
Peter Eisentraut <peter_e@gmx.net> writes:
On tis, 2011-06-14 at 15:38 +0200, Florian Pflug wrote:
BTW, there's actually precedent for a commutator of "~", namely
"@". Some of the geometric types (polygon, box, circle, point,
path) use "~" as a commutator for "@" (which stands for"contains").
I wouldn't have a problem with naming the reverse operator "@".
We deprecated those names for the geometric operators largely because
there wasn't any visual correlation between the commutator pairs.
I can't see introducing the same pairing for regex operators if we
already decided the geometric case was a bad idea.
I actually reported the exact issue that Florian reported a while ago
and we had this same sort of discussion. I think I'm running with a
custom operator named ~~~ somewhere in production. So yay for adding a
commutator in any case.
I don't really agree that visual correlation needs to trump everything.
If say
foo =~ bar
and
foo ~= bar
were to produce completely different results, this would introduce bugs
all over the place. Most programming languages would get away with this
kind of issue because the pattern has a different data type than the
string to be matched against, so mistakes will be caught.
Looking at the list of geometric operators, I can't help but feel that
the silliness of operator naming is reaching its limits. We can
probably come up with a few more for this particular problem, but long
term we might want to think of other solutions, such as attaching the
optimization information to functions instead, and/or inventing an infix
function call syntax like in Haskell.
Peter Eisentraut <peter_e@gmx.net> writes:
I don't really agree that visual correlation needs to trump everything.
If say
foo =~ bar
and
foo ~= bar
were to produce completely different results, this would introduce bugs
all over the place.
Huh? That's about like arguing that standard mathematical notation is
broken because a < b and a > b don't produce the same result.
regards, tom lane
On Jun16, 2011, at 21:49 , Tom Lane wrote:
All three of these are massive overkill. What we need is a general
policy that providing commutators is a good idea. We do not need to try
to make it 100.00% with an enforcement mechanism.
What parts of (1) do you think are overkill exactly, then?
As for #2, what's
your plan for automatically selecting a commutator operator name?
I figured we'd name it "COMMUTATOR <op>" or something along this line.
That'd mean it'd only be useable with the OPERATOR() syntax, but that's
way better than nothing. Or we could even make the COMMUTATOR argument
mandatory for binary operators returning boolean. After all, if a
commutator doesn't require a second function, than I fail to see why
you'd ever want to define a predicate without a commutator.
In any case, yeah, (2) is pretty hand-weavy. I included so that we'd
have all the options on the table, not because I think it's particularly
elegant, easy, or interesting to implement (actually, it's probably
none of these).
(Having said that, I *was* thinking of adding an opr_sanity test ... but
not expecting that we'd get it to find zero rows.)
Well, as long as there is some regression test failure for
missing commutators of newly added binary boolean operators, I'm
content.
best regards,
Florian Pflug
Excerpts from Tom Lane's message of jue jun 16 17:33:17 -0400 2011:
Peter Eisentraut <peter_e@gmx.net> writes:
I don't really agree that visual correlation needs to trump everything.
If say
foo =~ bar
and
foo ~= bar
were to produce completely different results, this would introduce bugs
all over the place.Huh? That's about like arguing that standard mathematical notation is
broken because a < b and a > b don't produce the same result.
The difference is that the mnemonic for > and < is very simple and in
widespread knowledge; not something I would say for =~'s rule of "the ~
is on the side of the regexp". I know I used to get it wrong in Perl
(i.e. I wrote ~= occasionally).
To make matters worse, our delimiters for regexes are the same as for
strings, the single quote. So you get
foo =~ 'bar' /* foo is the text column, bar is the regex */
'bar' =~ foo /* no complaint but it's wrong */
'bar' ~= foo /* okay */
'foo' ~= bar /* no complaint but it's wrong */
How do I tell which is the regex here? If we used, say, /, that would
be a different matter:
foo =~ /bar/
/bar/ ~= foo /* both okay */
If we had that and you get it wrong, the parser would immediately barf
at you if you got it wrong:
/bar/ =~ foo /* wrong: LHS wanted text, got regex */
foo ~= /bar/ /* wrong: LHS wanted regex, got text */
(Note: I'm not suggesting we use / as delimiter. This is just an
example.)
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Jun17, 2011, at 03:42 , Alvaro Herrera wrote:
To make matters worse, our delimiters for regexes are the same as for
strings, the single quote. So you getfoo =~ 'bar' /* foo is the text column, bar is the regex */
'bar' =~ foo /* no complaint but it's wrong */'bar' ~= foo /* okay */
'foo' ~= bar /* no complaint but it's wrong */How do I tell which is the regex here? If we used, say, /, that would
be a different matter:
How is this different from the situation today where the operator
is just "~"?
best regards,
Florian Pflug
Excerpts from Florian Pflug's message of vie jun 17 04:46:32 -0400 2011:
On Jun17, 2011, at 03:42 , Alvaro Herrera wrote:
To make matters worse, our delimiters for regexes are the same as for
strings, the single quote. So you getfoo =~ 'bar' /* foo is the text column, bar is the regex */
'bar' =~ foo /* no complaint but it's wrong */'bar' ~= foo /* okay */
'foo' ~= bar /* no complaint but it's wrong */How do I tell which is the regex here? If we used, say, /, that would
be a different matter:How is this different from the situation today where the operator
is just "~"?
Err, we don't have commutators today?
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Jun17, 2011, at 15:36 , Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 04:46:32 -0400 2011:
On Jun17, 2011, at 03:42 , Alvaro Herrera wrote:
To make matters worse, our delimiters for regexes are the same as for
strings, the single quote. So you getfoo =~ 'bar' /* foo is the text column, bar is the regex */
'bar' =~ foo /* no complaint but it's wrong */'bar' ~= foo /* okay */
'foo' ~= bar /* no complaint but it's wrong */How do I tell which is the regex here? If we used, say, /, that would
be a different matter:How is this different from the situation today where the operator
is just "~"?Err, we don't have commutators today?
So? How does that reduce that risk of somebody writing "pattern ~ text"
instead of "text ~ pattern"? Modifying your quote from above
--------
foo ~ 'bar' /* foo is the text column, bar is the regex */
'bar' ~ foo /* no complaint but it's wrong */
How do I tell which is the regex here?
--------
How is that worse than the situation with "=~" and "~="?
"=~" and "~=" at least don't *look* symmetric when they really are
not, which is the heart of the complaint, and also what makes defining
a sensible commutator impossible.
Also, do you have a better suggestion for how we can fix my original
gripe? Adding support for 'ANY/ALL op scalar" was shot down by Tom,
so it looks like we need a commutator for "~". "@" is severely disliked
by Tom, on the grounds that it's already been deprecated in other places.
"=~" is argued against by you and Robert Haas (I think). We're running
out of options here...
best regards,
Florian Pflug
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
On Jun17, 2011, at 15:36 , Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 04:46:32 -0400 2011:
On Jun17, 2011, at 03:42 , Alvaro Herrera wrote:
To make matters worse, our delimiters for regexes are the same as for
strings, the single quote. So you getfoo =~ 'bar' /* foo is the text column, bar is the regex */
'bar' =~ foo /* no complaint but it's wrong */'bar' ~= foo /* okay */
'foo' ~= bar /* no complaint but it's wrong */How do I tell which is the regex here? If we used, say, /, that would
be a different matter:How is this different from the situation today where the operator
is just "~"?Err, we don't have commutators today?
So? How does that reduce that risk of somebody writing "pattern ~ text"
instead of "text ~ pattern"? Modifying your quote from above
--------
foo ~ 'bar' /* foo is the text column, bar is the regex */
'bar' ~ foo /* no complaint but it's wrong */How do I tell which is the regex here?
--------
The regex is always to the right of the operator.
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
I have sometimes needed to look up which is which on ~ and ~~.
I assume that whichever way we go here, we're still going to have to
look up operator definitions in docs or online help. This kind of help
doesn't, err, help all that much:
alvherre=# \doS ~
Listado de operadores
Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción
------------+--------+--------------+--------------+----------------+--------------------------------------------
...
pg_catalog | ~ | text | text | boolean | matches regular expression, case-sensitive
Note that there's no way to tell which is the regex here. It'd be a lot
better if the description was explicit about it. (Or, alternatively,
use a different data type for regexes than plain text ... but that has
been in the Todo list for years ...)
"=~" and "~=" at least don't *look* symmetric when they really are
not, which is the heart of the complaint, and also what makes defining
a sensible commutator impossible.
Also, do you have a better suggestion for how we can fix my original
gripe? Adding support for 'ANY/ALL op scalar" was shot down by Tom,
so it looks like we need a commutator for "~". "@" is severely disliked
by Tom, on the grounds that it's already been deprecated in other places.
"=~" is argued against by you and Robert Haas (I think). We're running
out of options here...
Have ~ keep its existing semantics, use ~= for the commutator? There
are a lot more chars allowed in operator names anyway, it doesn't seem
to me like we need to limit ourselves to ~, = and @.
I *do* like the idea of having commutate-ability for ANY/ALL, having
needed it a couple of times in the past.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On 06/17/2011 10:20 AM, Alvaro Herrera wrote:
alvherre=# \doS ~
Listado de operadores
Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción
------------+--------+--------------+--------------+----------------+--------------------------------------------
...
pg_catalog | ~ | text | text | boolean | matches regular expression, case-sensitiveNote that there's no way to tell which is the regex here. It'd be a lot
better if the description was explicit about it. (Or, alternatively,
use a different data type for regexes than plain text ... but that has
been in the Todo list for years ...)
+1 for improving the description.
Have ~ keep its existing semantics, use ~= for the commutator? There
are a lot more chars allowed in operator names anyway, it doesn't seem
to me like we need to limit ourselves to ~, = and @.
Yeah, maybe something like ~< for the commutator. (I know, we're
bikeshedding somewhat.)
I *do* like the idea of having commutate-ability for ANY/ALL, having
needed it a couple of times in the past.
Indeed. me too.
cheers
andrew
On Jun17, 2011, at 16:20 , Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
So? How does that reduce that risk of somebody writing "pattern ~ text"
instead of "text ~ pattern"? Modifying your quote from above
--------
foo ~ 'bar' /* foo is the text column, bar is the regex */
'bar' ~ foo /* no complaint but it's wrong */How do I tell which is the regex here?
--------The regex is always to the right of the operator.
Which is something you have to remember... It's not in any
way deducible from "foo ~ bar" alone.
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
It's always where the tilde is. Yeah, you have to remember that.
Just as today you have to remember that the pattern goes on the
right side.
I have sometimes needed to look up which is which on ~ and ~~.
I assume that whichever way we go here, we're still going to have to
look up operator definitions in docs or online help. This kind of help
doesn't, err, help all that much:alvherre=# \doS ~
Listado de operadores
Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción
------------+--------+--------------+--------------+----------------+--------------------------------------------
...
pg_catalog | ~ | text | text | boolean | matches regular expression, case-sensitiveNote that there's no way to tell which is the regex here. It'd be a lot
better if the description was explicit about it.
I'm all for it, let's change the description then! Shall I submit a patch?
(Or, alternatively,
use a different data type for regexes than plain text ... but that has
been in the Todo list for years ...)
I actually like that idea. Since we probably don't want a type for every
kind of pattern we support (like, similar to, regexp), such a type wouldn't
be much more than a synonym for text though. I personally don't have a
problem with that, but I somehow feel there's gonna be quite some pushback...
Also, do you have a better suggestion for how we can fix my original
gripe? Adding support for 'ANY/ALL op scalar" was shot down by Tom,
so it looks like we need a commutator for "~". "@" is severely disliked
by Tom, on the grounds that it's already been deprecated in other places.
"=~" is argued against by you and Robert Haas (I think). We're running
out of options here...Have ~ keep its existing semantics, use ~= for the commutator?
So how does that make it any easier to tell what
foo ~ bar
and
foo ~= bar
mean? With that, neither the "pattern is always on the right" nor
the "pattern goes where the tilde is" mnemonic works.
Also, do we really want to end up with a large number of commutator
pairs with totally unrelated names? I fear that this *will* seriously
harm readability of SQL statements, and we'll regret it badly.
best regards,
Florian Pflug
On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
To throw my user opinion into this ring (as a long time user of regexes
in many different systems) I've always taken the ~ to be short hand for
the 'approximately' notation (a squiggly equals) which has good semantic
match in my mind: a regex match is sort of a fuzzy equality. With that
model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the
pattern) is next to the squiggles, the 'concrete' part goes by the
equals.
I have sometimes needed to look up which is which on ~ and ~~.
which has no such directionality, so yeah, no hinting there.
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
On Jun17, 2011, at 17:15 , Ross J. Reedstrom wrote:
On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
To throw my user opinion into this ring (as a long time user of regexes
in many different systems) I've always taken the ~ to be short hand for
the 'approximately' notation (a squiggly equals) which has good semantic
match in my mind: a regex match is sort of a fuzzy equality. With that
model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the
pattern) is next to the squiggles, the 'concrete' part goes by the
equals.
Hey, that's my mnemonic device! ;-)
best regards,
Florian Pflug
Excerpts from Florian Pflug's message of vie jun 17 10:49:46 -0400 2011:
On Jun17, 2011, at 16:20 , Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
So? How does that reduce that risk of somebody writing "pattern ~ text"
instead of "text ~ pattern"? Modifying your quote from above
--------
foo ~ 'bar' /* foo is the text column, bar is the regex */
'bar' ~ foo /* no complaint but it's wrong */How do I tell which is the regex here?
--------The regex is always to the right of the operator.
Which is something you have to remember... It's not in any
way deducible from "foo ~ bar" alone.
Maybe, but the mnemonic rule seems quite a bit easier (to me anyway).
In my head I think of ~ as "matches", so "text matches regex", whereas
"regex matches text" doesn't make as much sense. (Hmm now that I see
it, maybe in english this is not so clear, but in spanish the difference
is pretty obvious).
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
It's always where the tilde is. Yeah, you have to remember that.
Just as today you have to remember that the pattern goes on the
right side.
Well, the mnemonic would be that ~ is still "text matches regex", while
~= is "the weird operator that goes the other way around", so it's still
pretty clear.
I have sometimes needed to look up which is which on ~ and ~~.
I assume that whichever way we go here, we're still going to have to
look up operator definitions in docs or online help. This kind of help
doesn't, err, help all that much:alvherre=# \doS ~
Listado de operadores
Esquema | Nombre | Tipo arg izq | Tipo arg der | Tipo resultado | Descripción
------------+--------+--------------+--------------+----------------+--------------------------------------------
...
pg_catalog | ~ | text | text | boolean | matches regular expression, case-sensitiveNote that there's no way to tell which is the regex here. It'd be a lot
better if the description was explicit about it.I'm all for it, let's change the description then! Shall I submit a patch?
Yes, please.
(Or, alternatively,
use a different data type for regexes than plain text ... but that has
been in the Todo list for years ...)I actually like that idea. Since we probably don't want a type for every
kind of pattern we support (like, similar to, regexp), such a type wouldn't
be much more than a synonym for text though. I personally don't have a
problem with that, but I somehow feel there's gonna be quite some pushback...
Hmm, why? Maybe that's something we can discuss.
Also, do we really want to end up with a large number of commutator
pairs with totally unrelated names? I fear that this *will* seriously
harm readability of SQL statements, and we'll regret it badly.
Hmm.
I guess this wouldn't be much of a problem if you could use ANY/ALL with
a function instead of an operator, c.f. map().
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, Jun 17, 2011 at 11:46 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:
I guess this wouldn't be much of a problem if you could use ANY/ALL with
a function instead of an operator, c.f. map().
Yeah. Or really what you want is a lambda-expression, rather than a
predefined function.
fold(bool_and, map { val ~ $0 } array)
I suspect that's darn hard to make work though.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
On Fri, Jun 17, 2011 at 05:21:10PM +0200, Florian Pflug wrote:
On Jun17, 2011, at 17:15 , Ross J. Reedstrom wrote:
On Fri, Jun 17, 2011 at 10:20:04AM -0400, Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 10:03:56 -0400 2011:
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
To throw my user opinion into this ring (as a long time user of regexes
in many different systems) I've always taken the ~ to be short hand for
the 'approximately' notation (a squiggly equals) which has good semantic
match in my mind: a regex match is sort of a fuzzy equality. With that
model, the suggested pair is fairly mnemonic - the 'fuzzy' part i(the
pattern) is next to the squiggles, the 'concrete' part goes by the
equals.Hey, that's my mnemonic device! ;-)
Ah, good, so since this is almost mathematics, and we have two
instances, that's a proof then. :-)
Ross
--
Ross Reedstrom, Ph.D. reedstrm@rice.edu
Systems Engineer & Admin, Research Scientist phone: 713-348-6166
Connexions http://cnx.org fax: 713-348-3665
Rice University MS-375, Houston, TX 77005
GPG Key fingerprint = F023 82C8 9B0E 2CC6 0D8E F888 D3AE 810E 88F0 BEDE
On Jun17, 2011, at 18:00 , Robert Haas wrote:
On Fri, Jun 17, 2011 at 11:46 AM, Alvaro Herrera
<alvherre@commandprompt.com> wrote:I guess this wouldn't be much of a problem if you could use ANY/ALL with
a function instead of an operator, c.f. map().Yeah. Or really what you want is a lambda-expression, rather than a
predefined function.fold(bool_and, map { val ~ $0 } array)
Yeah, to bad we can't just write
SELECT BOOL_AND(val ~ e) FROM UNNEST(array)
Hey...wait a minute... ;-)
(I guess you actually meant
fold(bool_and, map { val ~ $0 } array)
which the equivalent sub-select
SELECT BOOL_AND(e ~ val) FROM UNNEST(array))
Still, you can't put that into a CHECK constraint (because it
counts as sub-select) and it's considerable longer and harder
to read then
val = ANY(array)
best regards,
Florian Pflug
On Jun17, 2011, at 17:46 , Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of vie jun 17 10:49:46 -0400 2011:
Maybe, but the mnemonic rule seems quite a bit easier (to me anyway).
In my head I think of ~ as "matches", so "text matches regex", whereas
"regex matches text" doesn't make as much sense. (Hmm now that I see
it, maybe in english this is not so clear, but in spanish the difference
is pretty obvious).
I can't really argue with that, only state for that record that it's
different for me. I think of "~" as "similar" or "approximately equal",
and hence intuitively expect it to be symmetric. Whether or not
"matches" technically implies some direction or not I cannot say as
I'm not an english native speaker myself. But if I had to guess, I'd say
it doesn't.
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
It's always where the tilde is. Yeah, you have to remember that.
Just as today you have to remember that the pattern goes on the
right side.Well, the mnemonic would be that ~ is still "text matches regex", while
~= is "the weird operator that goes the other way around", so it's still
pretty clear.
Again, that depends on a person's background. For me it'd be
"~= is the regexp matching operator" and "~ is for some strange
reasons its commutator".
'm all for it, let's change the description then! Shall I submit a patch?
Yes, please.
Will do, but after we've reached an overall agreement about the fate
or "~" and friends.
(Or, alternatively,
use a different data type for regexes than plain text ... but that has
been in the Todo list for years ...)I actually like that idea. Since we probably don't want a type for every
kind of pattern we support (like, similar to, regexp), such a type wouldn't
be much more than a synonym for text though. I personally don't have a
problem with that, but I somehow feel there's gonna be quite some pushback...Hmm, why? Maybe that's something we can discuss.
Ok, I'll start a new thread for this.
best regards,
Florian Pflug
On Thu, Jun 16, 2011 at 6:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
(I will refrain for
the moment from speculating whether we'll ever have an index type that
supports regexp match directly as an indexable operator...)
Fwiw I looked into this at one point and have some ideas if anyone is
keen to try it.
--
greg
On Sun, Jun 19, 2011 at 02:48:58PM +0100, Greg Stark wrote:
On Thu, Jun 16, 2011 at 6:11 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
(I will refrain for the moment from speculating whether we'll ever
have an index type that supports regexp match directly as an
indexable operator...)Fwiw I looked into this at one point and have some ideas if anyone
is keen to try it.
Please post them :)
Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
On Fri, Jun 17, 2011 at 3:49 PM, Florian Pflug <fgp@phlo.org> wrote:
The regex is always to the right of the operator.
Which is something you have to remember... It's not in any
way deducible from "foo ~ bar" alone.
Except that it's always been this way, going back to perl4 or tcl or
their predecessors. The regexp binding operator always has the regexp
on the right.
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
It's always where the tilde is. Yeah, you have to remember that.
And when you get it wrong it will fail silently. No errors, just wrong results.
While I've never accidentally written /foo/ =~ $_ in perl I have
*frequently* forgotten whether the operator is ~= or =~. Actually I
forget that pretty much every time I start writing some perl. I just
put whichever comes first and if I get an error I reverse it.
I can see the temptation to make it symmetric but it's going to cause
an awful lot of confusion.
Perhaps we could name the operators ~~= and =~~ and then have a =~
short-cut for compatibility? (and ~ too I guess?)
--
greg
On Jun20, 2011, at 03:16 , Greg Stark wrote:
On Fri, Jun 17, 2011 at 3:49 PM, Florian Pflug <fgp@phlo.org> wrote:
The regex is always to the right of the operator.
Which is something you have to remember... It's not in any
way deducible from "foo ~ bar" alone.Except that it's always been this way, going back to perl4 or tcl or
their predecessors. The regexp binding operator always has the regexp
on the right.
Yeah. The strength of that argument really depends on one's
prior exposure to these languages, though...
How is that worse than the situation with "=~" and "~="?
With =~ it is to the right, with ~= it is to the left.
It's always where the tilde is. Yeah, you have to remember that.
And when you get it wrong it will fail silently. No errors, just wrong results.
Yeah, but this is hardly the only case where you'll get
unintended results if you mix up operator names.
Now, one might argue, I guess, that mixing up "=~" and "~="
or more likely than mixing up, say, "~" and "~~". But ultimately,
whether or not that is highly dependent on one's personal background,
so we're unlikely to ever reach agreement on that...
While I've never accidentally written /foo/ =~ $_ in perl I have
*frequently* forgotten whether the operator is ~= or =~. Actually I
forget that pretty much every time I start writing some perl. I just
put whichever comes first and if I get an error I reverse it.
Yeah, the nice thing in perl (and ruby also, which is *my* background)
is that regexp's and strings are distinguished by the type system,
and also by the parser.
The latter (i.e. regexp literals enclosed by /../) probably isn't
desirably for postgres, but the former definitely is (i.e. distinguishing
regexp's and text in the type system). Please see the thread
"Adding a distinct pattern type to resolve the ~ commutator stalemate"
for the details of the proposal.
I can see the temptation to make it symmetric but it's going to cause
an awful lot of confusion.
I do believe that by adding a distinct type we can actually *reduce*
confusion. It makes "text ~ pattern" readable even for people who
don't intuitively know that the pattern always goes on the right.
best regards,
Florian Pflug
Excerpts from Florian Pflug's message of lun jun 20 06:55:42 -0400 2011:
The latter (i.e. regexp literals enclosed by /../) probably isn't
desirably for postgres, but the former definitely is (i.e. distinguishing
regexp's and text in the type system). Please see the thread
"Adding a distinct pattern type to resolve the ~ commutator stalemate"
for the details of the proposal.
'your text' ~ regexp 'your.*foo'
column ~ regexp 'your.*foo'
So you could do
regexp 'foo.*bar' ~ 'your text'
and it's immediately clear what's up.
The question is what to do wrt implicit casting of text to regexp.
If we don't, there's a backwards compatibility hit.
--
Álvaro Herrera <alvherre@commandprompt.com>
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Jun20, 2011, at 19:22 , Alvaro Herrera wrote:
Excerpts from Florian Pflug's message of lun jun 20 06:55:42 -0400 2011:
The latter (i.e. regexp literals enclosed by /../) probably isn't
desirably for postgres, but the former definitely is (i.e. distinguishing
regexp's and text in the type system). Please see the thread
"Adding a distinct pattern type to resolve the ~ commutator stalemate"
for the details of the proposal.'your text' ~ regexp 'your.*foo'
column ~ regexp 'your.*foo'So you could do
regexp 'foo.*bar' ~ 'your text'
and it's immediately clear what's up.
The question is what to do wrt implicit casting of text to regexp.
If we don't, there's a backwards compatibility hit.
No, we certainly musn't allow text to be implicitly converted to
regexp, for otherwise e.g. "varchar ~ varchar" becomes ambiguous.
I posted a primitive prototype for a pattern type on said thread,
which seems to do everything we require without causing compatibility
problems.
best regards,
Florian Pflug