Re: MySQL search query is not executing in Postgres DB

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Bruce Momjian <bruce(at)momjian(dot)us>
Cc: Robert Haas <robertmhaas(at)gmail(dot)com>, Andrew Dunstan <andrew(at)dunslane(dot)net>, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com>, premanand <kottiprem(at)gmail(dot)com>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: MySQL search query is not executing in Postgres DB
Date: 2012-08-27 20:03:05
Message-ID: 8180.1346097785@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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).

It's conceivable that a change in the lexer behavior combined with a
massive reorganization of the integer-related operators would bring us
to a nicer place than where we are now. But it'd be a lot of work for
dubious reward, and it would almost certainly generate a pile of
application compatibility problems.

Some history:
http://archives.postgresql.org/pgsql-hackers/2002-11/msg00468.php
http://archives.postgresql.org/pgsql-hackers/2010-09/msg00223.php
(A lot of the specific details in the 2002 thread are obsolete now,
but the general point remains, I fear.)

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Dean Rasheed 2012-08-27 20:10:35 Re: Optimize referential integrity checks (todo item)
Previous Message Bruce Momjian 2012-08-27 19:42:35 Re: Optimize referential integrity checks (todo item)