Re: MySQL search query is not executing in Postgres DB

On Mon, Aug 27, 2012 at 4:03 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Bruce Momjian <bruce(at)momjian(dot)us> writes:
>> On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote:
>>> Come on, really? Note that the above example works without casts if
>>> you use int *or* bigint *or* numeric, but not smallint. That could be
>>> fixed by causing sufficiently-small integers to lex as smallints,
>
>> Is there any general interest in adjusting smallint casting?
>
> We tried that once, years ago, and it was a miserable failure: it opened
> up far too many ambiguities, eg should "int4col + 1" invoke int4pl or
> int42pl? (That particular case works, because there's an exact match
> to int42pl, but we found an awful lot of cases where the parser couldn't
> resolve a best choice. IIRC there were dozens of failures in the
> regression tests then, and there would be more now.)
>
> There's also the problem that if "2 + 2" starts getting parsed as
> smallint int2pl smallint, cases like "20000 + 20000" will overflow when
> they didn't before. IMO smallint is a bit too narrow to be a useful
> general-purpose integer type, so we'd end up wanting int2pl to yield
> int4 to avoid unexpected overflows --- and that opens up more cans of
> worms, like which version of f() gets called for f(2+2).

I agree that redefining the lexer behavior is a can of worms. What I
don't understand is why f(2+2) can't call f(smallint) when that's the
only extant f. It seems to me that we could do that without breaking
anything that works today: if you look for candidates and don't find
any, try again, allowing assignment casts the second time.

We really ought to put some effort into solving this problem. I've
seen a few Oracle-migration talks at conferences, and *every one* of
them has mentioned the smallint problem. It hits our customers, too.

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