Re: MySQL search query is not executing in Postgres DB

Robert Haas <robertmhaas(at)gmail(dot)com> writes:
> On the more general issue, I continue to see minimal risk of harm in
> allowing things like LPAD() to implicitly cast the first argument to
> text.

Well, I see your point about LPAD(), but the problem is how to tell
the difference between a harmless cast omission and an actual mistake
that the user will be very grateful if we point out. If we allow
implicit casts to text in the general case in function/operator calls,
we are definitely going to re-introduce a lot of room for mistakes.

Upthread you were complaining about how we'd reject calls even when
there was only one possible interpretation. I wonder whether there'd be
any value in taking that literally: that is, allow use of assignment
rules when there is, in fact, exactly one function with the right number
of parameters visible in the search path. This would solve the LPAD()
problem (at least as stated), and probably many other practical cases
too, since I admit your point that an awful lot of users do not use
function overloading. The max() example I mentioned earlier would not
get broken since there's more than one max(), and in general it seems
likely that cases where there's a real risk would involve overloaded
names.

The main downside I can see is that code that used to work is likely
to stop working as soon as someone creates a potential overloading
situation. Worse, the error message could be pretty confusing, since
if you had been successfully calling f(smallint) with f(42), you'd get
"f(integer) does not exist", not something like "f() is ambiguous",
after adding f(float8) to the mix. This seems related to the confusing
changes in regression test cases that I got in my experiments yesterday.
This may be sufficient reason to reject the idea, since the very last
thing we need in this area is any degradation in the relevance of the
error messages.

> ... as long as I work for a company that helps
> people migrate from other database systems, I'm not going to be able
> to stop caring about this issue even in cases where I don't personally
> think implicit casting is a good idea, because other people who are
> not me have tens of thousands of lines of procedural code written for
> those other systems and if you tell them they've got to go through and
> add hundreds or thousands of casts before they can migrate, it tends
> to turn them off. Maybe there's no perfect solution to that problem,
> but the status quo is definitely not perfect either.

Meh. I tend to think that a better solution to those folks' problem is
a package of add-on casts that they could install for use with their
legacy code; not dumbing down the system's error detection capability
for everyone. Peter's original try at re-adding implicit text casts
in that way didn't work very well IIRC, but maybe we could try harder.

regards, tom lane