Re: MySQL search query is not executing in Postgres DB

On Wed, Aug 29, 2012 at 3:54 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
>> On 8/29/12 11:40 AM, Tom Lane wrote:
>>> regression=# select lpad(42,8);
>
>> We are not seriously intending to make the above query work, are we?
>
> Well, *I* don't want to, but apparently Robert does.
>
> I don't really want to go much further than finding a way to handle the
> "integer constant passed to smallint function argument" case. Maybe we
> should consider a narrow fix for that rather than opening up the general
> assignment-cast scenario.

If we could just do that, it would be a huge improvement. I'm not
very sanguine about the possibility of a clean fix in the lexer, but
maybe there is some other tweak to the system that would make it work.

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. I wrote code that did that for years (to pad numeric fields out
to a specific width by adding leading zeros) and until I upgraded to
8.3 it caused me no problems. I knew what I meant, and so did the
database, and we were both happy. The argument seems to be that we
shouldn't have been happy, but we were. Some of the other examples
Tom mentions are, indeed, icky, and I don't know what to do about
that, but LPAD() does indeed seem pretty harmless to me. And, on a
more pragmatic level, 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.

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