Skip site navigation (1) Skip section navigation (2)

Re: MySQL search query is not executing in Postgres DB

From: Bruce Momjian <bruce(at)momjian(dot)us>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Andrew Dunstan <andrew(at)dunslane(dot)net>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>,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 19:13:45
Message-ID: 20120827191345.GT11088@momjian.us (view raw or flat)
Thread:
Lists: pgsql-hackers
On Fri, Feb 17, 2012 at 02:52:20PM -0500, Robert Haas wrote:
> Here's yet another case where the current rules are thoroughly disagreeable.
> 
> rhaas=# create or replace function z(smallint) returns smallint as
> $$select $1+1$$ language sql;
> ERROR:  return type mismatch in function declared to return smallint
> DETAIL:  Actual return type is integer.
> CONTEXT:  SQL function "z"
> 
> So cast the result from an integer to a smallint.  What's the big deal?
> 
> But, OK, I'll do it your way:
> 
> rhaas=# create or replace function z(smallint) returns smallint as
> $$select $1+1::smallint$$ language sql;
> CREATE FUNCTION
> rhaas=# select z(1);
> ERROR:  function z(integer) does not exist
> LINE 1: select z(1);
>                ^
> HINT:  No function matches the given name and argument types. You
> might need to add explicit type casts.
> 
> 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, but
> if you think implicit datatype coercions are evil, you ought to be
> outraged by the fact that we are already going out of our way to blur
> the line between int, bigint, and numeric.  We let people write 2.0 +
> 3 and get 5.0 - surely it's only a short step from there to human
> sacrifice, cats and dogs living together... mass hysteria!   I mean,
> the whole reason for rejecting integer = text is that we aren't sure
> whether to coerce the text to an integer or the integer to a string,
> and it's better to throw an error than to guess.  But in the case of
> 2.0 + 3, we feel 100% confident in predicting that the user will be
> happy to convert the integer to a numeric rather than the numeric to
> an integer, so no error.  We do that because we know that the domain
> of numeric is a superset of the domain of integer, or in other words,
> we are using context clues to deduce what the user probably meant
> rather than forcing them to be explicit about it.

Is there any general interest in adjusting smallint casting?

-- 
  Bruce Momjian  <bruce(at)momjian(dot)us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +


In response to

Responses

pgsql-hackers by date

Next:From: Dean RasheedDate: 2012-08-27 19:26:11
Subject: Re: Proof of concept: auto updatable views
Previous:From: Alvaro HerreraDate: 2012-08-27 18:31:59
Subject: Re: pg_upgrade's exec_prog() coding improvement

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group