Re: MySQL search query is not executing in Postgres DB

On Tue, Aug 28, 2012 at 4:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Well, I think that when there is only one LPAD function, there is also
>> very little chance that the results will come out differently than the
>> user expected.
>
> [ shrug... ] I'm having a hard time resisting the temptation to point
> out that there are two.

Fine, but as they have different numbers of arguments it has no
bearing on the point at hand, which is that right now it is very easy
to write a call that matches unexpectedly fails to match either one.

> The real point here though is that the proposed
> behavior change will affect all functions, not only the cases where you
> think there is only one sane behavior. And features such as search paths
> and default parameters frequently mean that there are more potential
> matches than the user thought of while writing the query.

I'm totally unpersuaded by this argument. I have yet to run into a
customer who defined multiple functions with the same name and then
complained because we called the wrong one, or even because we threw
an error instead of just picking one. I have run into MANY customers
who have been forced to insert typecasts into applications to work
around our willingness to consider calling the only plausible
candidate function or operator. Now some of this is no doubt because
we have very few customers running on pre-8.3 releases (woohoo!), but
that's exactly the point: the bad old days when you could break your
application by accidentally invoking the wrong function are gone.
That problem is dead. What we ought to be focusing on now is fixing
the collateral damage.

> In the end, SQL is a fairly strongly typed language, especially in our
> manifestation of it. I don't think we should give that up, especially
> not for benefits as dubious as not having to write a cast to make it
> clear that yes you really do want a timestamp to be treated as text.
> IMO, saving people from the errors that inevitably arise from that sort
> of sloppy thinking is a benefit, not a cost, of having a typed language.

The benefit is that it allows us to be compatible with other SQL
systems. If PostgreSQL were the only database in the world, I might
agree with you, but it isn't: it's just the only one that requires you
to insert this many casts. It's hard to accept the argument that
there's no sensible alternative when other people have clearly found
something that works for them and their users. We can dig in our
heels and insist we know better, but what does that do other than
drive away users? For most people, the database is just a tool, and
they want it to work with a minimum of fuss, not force them to jump
through unexpected and unwelcome hoops. Again, if there's real
ambiguity then that is one thing, but what I'm proposing does not
change the behavior in any case we currently consider ambiguous. I
don't know of any other programming language or system where it is
considered a virtue to force the user to inject unnecessary
decorations into their code. Indeed, many systems go to quite some
lengths to minimize the amount of such decoration that is required.

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