Re: MySQL search query is not executing in Postgres DB

On Tue, Nov 27, 2012 at 4:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)net> wrote:
> On 11/25/12 6:36 PM, Robert Haas wrote:
>> I think that is true. But for whatever it's worth, and at the risk of
>> beating a horse that seems not to be dead yet in spite of the fact
>> that I feel I've already administered one hell of a beating, the LPAD
>> case is unambiguous, and therefore it is hard to see what sort of
>> programming mistake we are protecting users against.
>
> Upstream applications passing wrong data down to the database.

The circumstantial evidence suggests that many users don't want to be
protected against that in the way that we are currently protecting
them - or at least not all of them do (see Merlin's email elsewhere on
this thread). What's frustrating about the status quo is that not
only do you need lots of extra casts, but there's no real way to
improve the situation. If you add an implicit cast from integer to
text, for example, then 4 || 'foo' breaks. Now, you may think that
adding an implicit cast from integer to text is a dumb idea, and maybe
it is, but don't get too hung up on that example. The point is that
if you're unhappy with the quote_literal() case (because it accepts
too much), or the lpad() case (because it doesn't accept enough), or
the foo(smallint) case (because it can't be happy with foo(42)), you
don't really have a lot of options for adjusting the behavior as
things stand today. I accept that some people think that decorating
their code with lots of extra casts helps to avoid errors, and maybe
it does, but there is plenty of evidence that a lot of users don't
want to. And we not only don't give them the behavior they want; we
don't even have a meaningful way to give the option of opting into
that behavior at initdb or create-database time.

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