Re: Boolean operators without commutators vs. ALL/ANY

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 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:
> >> 
> >> 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 |
Tiporesultado |                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