Re: Boolean operators without commutators vs. ALL/ANY

On Jun13, 2011, at 05:12 , Robert Haas wrote:
> On Sun, Jun 12, 2011 at 7:46 AM, Florian Pflug <fgp(at)phlo(dot)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