Re: MySQL search query is not executing in Postgres DB

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On Tue, Aug 28, 2012 at 4:47 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> 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.

That argument would hold water if we got rid of every single usage of
overloading in the system-defined operators/functions, which as you well
know is not an attractive idea. Since that's not going to happen,
arguing for this on the basis that your customers don't overload
function names is missing the point. Any loosening of the rules is
going to create issues for system-function resolution ... unless you're
going to propose that we somehow do this differently for user and system
defined functions.

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

The reason it's dead is that we killed it in 8.3. I don't want it
coming back to life, but I think that that will be exactly the outcome
if we let any implicit casts to text get back into the rules for
operator/function overloading resolution.

An example of the sort of problem that I don't want to hear about
ever again is somebody trying to use max() on a "point" column.
We don't have linear sort ordering for points, so this is nonsensical
and should draw an error. Which it does, today. With your proposal,
the system would silently use max(pointcol::text), producing results
that might even look plausible if the user wasn't paying too much
attention. If that's the behavior the user actually wants, fine: let
him say so with an explicit cast to text. But I don't want the system
trapping users into such hard-to-find errors because we are so focused
on mysql compatibility that we let people omit conceptually-critical
casts in the name of ease of use.

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

Really? You've not had experience with very many programming languages,
then. Just about every one I've ever dealt with that's at a higher
conceptual level than C or BASIC *is* sticky about this sort of thing.

regards, tom lane