Re: Differences in WHERE clause of SELECT - Mailing list pgsql-hackers

From Robert Haas
Subject Re: Differences in WHERE clause of SELECT
Date
Msg-id CA+TgmoZzw9DGuG9iCKQVX=ZGQLXyyBNDgT92Fs=AwtuqSL=Fog@mail.gmail.com
Whole thread Raw
In response to Re: Differences in WHERE clause of SELECT  (Tom Lane <tgl@sss.pgh.pa.us>)
Responses Re: Differences in WHERE clause of SELECT
List pgsql-hackers
On Tue, Jul 16, 2013 at 12:05 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I don't agree with this opinion.

I know.

> '1' + '2' might be claimed to have
> only one plausible meaning as well, but that doesn't mean that we should
> accept it.

That example clearly has multiple sensible interpretations, because
surely we have no way of knowing whether the user wants +(int2,int2),
+(int4,int4), +(int8,int8), +(real,real), +(money,money), etc., etc.
There are 42 binary + operators, of which at least 10 or so are
plausible interpretations of that call.  I have never proposed
changing the behavior of this case - though, to be fair, if we found a
way to just decide on int4, say, I bet we'd get very few complaints.

> There is a very fundamental distinction between numbers and
> strings, and we'd do our users no service by pretending there isn't.
> What's more, your argument for this essentially rests on the assumption
> that LIKE represents only one possible operator choice, which is already
> false today (see \do ~~) and might be even more false in future.

This is a better argument, but I'm still not buying it.  Sure, there
are four candidate functions there, but they all perform the same
logical operation. It looks to me like bpcharlike and textlike both
call the same underlying function, and on a quick glance namelike
appears only trivially different.  Only bytealike is significantly
different, in that it operates on the underlying bytes rather than the
hex-escaped (or backslash-escaped) version of those bytes.  It's a
pretty good bet that the user did not want an implicit cast to bytea.

PostgreSQL requires more casts that any other mainstream database
system on the planet, and it's one of the biggest barriers to
migrating from other database systems to PostgreSQL.  I understand
that there is a consensus in this forum to pretend that our
intolerance is a virtue, but I don't agree with that consensus. A
request for =(text,int) is ambiguous; we really do not know what is
meant.  A request for ~~(int,text) is not ambiguous; we reject it out
of pedantry, not necessity.

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



pgsql-hackers by date:

Previous
From: David Fetter
Date:
Subject: Re: SSL renegotiation
Next
From: Greg Smith
Date:
Subject: Re: Improvement of checkpoint IO scheduler for stable transaction responses