Re: MySQL search query is not executing in Postgres DB - Mailing list pgsql-hackers

On Tue, Nov 27, 2012 at 3:21 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Robert Haas <robertmhaas@gmail.com> writes:
>> I do think that applying some kind of explicit flag to the function
>> indicating whether it should allow implicit assignment
>> casting/implicit casting to text/overloading/whatever is a possibly
>> interesting alternative.
>
> That idea seems possibly worth pursuing.  The thing that I find scary
> about the current proposal is that it applies to all functions (and
> operators) willy-nilly, which seems to raise the risk of unexpected
> side effects pretty high.  If we could confine the behavioral change
> to a relatively small number of functions for which there was consensus
> that they should accept most anything, I'd feel better about it.
>
> (Of course, we might then conclude that something close to the
> quote_literal solution would work as well as a new function property.
> But it's worth thinking about.)

>>  The fact that quote_literal() allows (by the expedient of
>> overloading) implicit casts to text and that lpad() does not seems
>> fairly random to me in hindsight; is there a general principle there
>> that we'd all sign on to?
>
> I don't find that random in the slightest.  The entire purpose of
> quote_literal is "manufacture a SQL-literal string representation of
> this value", and that clearly might apply to data of any type.  lpad()
> is, first last and only, a textual operation.  Somebody who thinks it
> should apply directly to an integer is guilty of sloppy thinking at
> best, or not even understanding what a data type is at worst.

Well, considering I made that "mistake" while working with PostgreSQL
8.2, and considering further that other databases allow it, I'm a
little reluctant to accept this theory.  I'm willing to bet that the
fine folks in Redwood understand what a data type is just fine, and
I'm pretty sure that I do, too.  Sloppy thinking?  Perhaps.  But I
think you could make a perfectly fine argument that the function of
lpad() is to concatenate something onto the string representation of a
value, or conversely that the function of quote_literal() is to escape
a string.  You might not agree with either of those arguments but I
don't care to label someone who does as an idiot.  The problem I have
with the explicit labeling approach is that it seems to depend heavily
on how you conceptualize what the function is trying to do, and not
everyone is going to conceptualize that the same way.  Clearly there
are a lot of people who expect at least some string operators to work
on numbers, including the OP, and are confused when they don't.  We
can call those people nasty names but that's not going to win us many
friends.

Anyway, I'm not averse to thinking about some kind of labeling
solution but I'm not exactly sure what would work well - and I'd still
like to see some hard evidence that the collateral damage from my er
your proposal is unacceptably high.  The most plausible scenario for
how this could break things that has been presented thus far is that
someone might create a function, use it with a data type that requires
assignment-casting, and then create another function, and have things
break.  But as Jeff pointed out, that can happen already: in fact, it
did, in core, with pg_size_pretty(), and while you had doubts about
that change at the time, none of us realized exactly what the failure
scenario was until it was too late to change it.  Would that kind of
thing happen more often under this proposal?  Kind of hard to say, but
if it made us think twice before overloading system catalog functions,
it might even work out to a net positive.

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