Re: Boolean operators without commutators vs. ALL/ANY - Mailing list pgsql-hackers

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-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.

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