Re: MySQL search query is not executing in Postgres DB

On Sun, Nov 25, 2012 at 7:39 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> I think this is ignoring the fact that we have an extensible type
> system, and thus a lot more room for problems if we allow too many
> implicit casts.

I don't deny that.

> It might also be worth noting that some of this complexity comes from
> the SQL standard.  It is at least arguable that the three-way coercion
> distinction exists in the standard: they have got different rules for
> what happens in an explicit CAST, in an assignment context, and in plain
> expressions.  So it's not that relevant whether other mainstream
> programming languages have comparable constructs.
>
> Having said that, though, I think you could make an argument that
> there's some support for this idea in the SQL standard.  In SQL99
> 10.4 <routine invocation>, it appears that once you've identified
> a target routine to be called, you're supposed to use the "store
> assignment" rules to decide how to convert the supplied expression(s)
> to the parameter data type(s).  However, it's not clear to me that
> that should be taken as conclusive, because the $64 question here
> is exactly how sure you are in your identification of the target
> routine.  SQL99 doesn't seem to allow anywhere near as much function
> overloading as we do --- and of course they have no notion of
> overloaded or user-defined operators at all.  As far as I can tell
> from 10.4, you are supposed to be able to identify the target routine
> without any consideration of the actual parameters' types.

FWIW, neither MySQL nor Oracle supports function overloading for plain
functions, so the question doesn't arise for them in the context of
something like LPAD().  Oracle does support overloading for package
functions, and I'm not sure exactly how they identify candidate
functions in that context, but they do complain about ambiguous calls
in some circumstances.

Personally, I'm not sure that anyone has come up with an altogether
satisfactory solution to the function overloading problem.  If you
have an exact type match in every argument position for one of the
possible candidate functions, then surely any system that permits
overloading at all is going to pick that candidate.  Conversely, if
you have one or many candidates all of which are completely
incompatible with the actual argument types, then any system is going
to fail.  The tension is all around what to do when you have several
candidates which are about equally good.  You can either reject the
call as ambiguous (which will sometimes annoy users who don't feel
that a cast should be needed) or you can use some sort of tiebreak
system to pick a candidate (which risks picking a different function
than the user expected).  I tend to think it's better to err on the
side of the former, and I think we do, but there might nonetheless be
some for improvement in that area, with due regard for the possibility
of breaking currently-working applications.

That, however, is a separate question from what's under discussion
here, because the case at issue for the proposed patch is the one in
which only one possible candidate exists, and the question is whether
we ought to allow the use of assignment casts to allow the call to
work rather than fail, NOT which of several overloaded functions we
ought to pick.  In any situation in which overloading is in use, the
patch as proposed changes nothing.  I'm not generally very good at
interpreting the SQL standard text, but if it says that you ought to
use assignment casts to match actual argument types to the chosen
candidate function, then that seems like it's advocating for
essentially the same position that you arrived at independently and
that the patch also takes, which furthermore happens to be compatible
with what other RDBMS systems do, at least in the no-overloading case.

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