Re: MySQL search query is not executing in Postgres DB

On Fri, Feb 17, 2012 at 10:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Robert Haas <robertmhaas(at)gmail(dot)com> writes:
>> I understand why it's a bad idea to rely on integer = text doing
>> anything sane - is that text equality or numeric equality?
>
>> And in theory the same issue could exist here if there were another
>> meaning for LIKE.  But there isn't.
>
> Really?  LIKE is just a different spelling for operator ~~, and there is
> nothing stopping people from defining more operators named that, not to
> mention that there are already four of them in core PG.  In particular
> the bytea ~~ bytea version is explicitly intended to provide a LIKE
> implementation for non-text types.  I see some operators named ~~ in
> contrib as well.

As far as I know, the fact that LIKE is another spelling for ~~ is a
PostgreSQL implementation detail with which users ought not to be
burdened. But even given that, there are many situations where we
currently complain about ambiguity even though the ambiguity is
entirely hypothetical: there COULD be a ~~(int,text) operator, but
there actually isn't. Now, I'll admit that this is not an easy
problem to solve without giving up something somewhere, since it's
clearly undesirable for the meaning of something that worked before to
silently change when and if someone defines a new operator.

But on the other hand, I think that labeling the user's coding
practices as sloppy is a cop-out. There are many, many people running
on not only MySQL but also on Oracle who have written large amounts of
code that requires fewer type casts on those systems than it does on
PostgreSQL. That fact presents serious migration challenges for such
users when they move over to PostgreSQL. Labeling the code as the
problem excuses us from the need to think about how to make our type
system work any better than it does today. Boo, hiss. If we're not
doing anything about this because we have carefully examined the
subject and decided that this is a trade-off we must make because
MySQL or Oracle doesn't support XYZ and we do, then let's give that
explanation to the user instead of telling them the problem is that
their code stinks. Otherwise, we have some soul-searching to do, as
time permits.

I remember there was a time when you couldn't say "SELECT a x FROM
foo" in PostgreSQL. We told people that it was because our syntax was
more flexible - we have postfix operators, or something. I no longer
remember the details of where the grammar conflict came from. But
somebody (probably you or Hiroshi Saito, judging by the commit log)
figured out a way to get around the problem, and now that syntax works
fine in 99% of the cases people care about. That is a huge usability
improvement for people coming from other database systems where AS was
never required. I don't know whether a similar improvement is
possible in this area, but we're certainly not going to get there by
labeling the user's expectations as unreasonable. I don't think they
are, and the people who wrote MySQL and Oracle evidently agree.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company