Re: MySQL search query is not executing in Postgres DB

On Fri, Feb 17, 2012 at 1:21 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> Assuming we had the cast, What would "intval like '1%'" mean? You're going
> to match 1, 10..19, 100..199, 1000..1999 ...

Yep.

> Now maybe there's a good use for such a test, but I'm have a VERY hard time
> imagining what it might be.

Dunno. Presumably the test is meaningful for the OP's IDs, or he
wouldn't have written the query that way.

The time I got bitten by this was actually with LPAD(), rather than
LIKE. I had a serial column which I wanted to use to generate record
identifiers off of a sequence: B00001, B00002, B00003, B00004, etc.
So I wrote 'B' || lpad(id, 5, '0'). When the implicit casting
changes came along, I had to go back and change that to id::text.
Fortunately that wasn't a lot of work, especially since by that time I
was following pgsql-hackers enough to understand immediately why it
broke, but it did and does seem unnecessary, because there is no real
ambiguity there. Yeah, there could be ambiguity, if someone created
another LPAD() function... but no one did.

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.

And yet in other cases, such as LIKE or LPAD with an integer rather
than a string, or just about anything involving smallint, the user is
required to be explicit, even though in most cases there is only one
reasonable implementation of the query. What is the value in erroring
out on a query that's not actually ambiguous? Numerous people here
are defending that behavior as if it were somehow morally superior,
but I think it's merely an accident of how the post-8.3 type system
happens to work. On pre-8.3 systems this all works differently, and
some of those old behaviors are worse than what we have now, while
others are better. If we really believed that implicit casts any form
were evil, we would have removed them entirely instead of trimming
them back. I don't see why it's heretical to suggest that the 8.3
casting changes brought us to exactly that point in the universe where
everything is perfect and nothing can be further improved; does anyone
seriously believe that?

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