Re: MySQL search query is not executing in Postgres DB

Hello

2012/12/10 Robert Haas <robertmhaas@gmail.com>:
> On Tue, Nov 27, 2012 at 4:36 PM, Peter Eisentraut <peter_e@gmx.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.
>

it is looking so our design missing some feature, flag, that can
signalize safety of implicit cast - or allow more exactly to specify
casting rules for related functionality. For some functions we do this
magic inside parser and rewriter, but we don't allow this for custom
functions.

Few years ago I proposed a parser hooks, where this task can be
solved. The parser hook is probably too generic, but probably we don't
design good solution just by simple change of some parameter of
current design, and we should to enhance current design - maybe some
new parameter modifiers?

Regards

Pavel


> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers