Re: Differences in WHERE clause of SELECT

On Tue, Jul 9, 2013 at 7:12 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
> Like uses the operator class "text_pattern_ops" which doesn't include an
> implict cast.

This is wrong. Casts are associated with data types, not operator classes.

> For one thing, the implicit cast is from text -->
> integer, not the other way around, and there is no LIKE operator for
> integers.

This is also wrong. There's no implicit cast between text and integer
in either direction - only assignment casts.

I think the reason why the first few examples work and the last one
fails is that, in the first few cases, there are integer literals and
unknown literals, and the operations in question are defined for
integers (and for text), so we pick the integer interpretation and
call it good. But in the non-working case, the operation is defined
to work only on text, and an integer argument is supplied. So we have
to cast, and there's no implicit cast, ergo we fail.

The point is that we use a different procedure to decide what to do
with a quoted literal ('1', or '3.14159', or 'foo') than we do to
decide whether it's OK to cast a value of a type we already know. For
example this fails:

rhaas=# create table foo (a int, b varchar);
CREATE TABLE
rhaas=# insert into foo values ('1', '1');
INSERT 0 1
rhaas=# select * from foo where a = b;
ERROR: operator does not exist: integer = character varying
LINE 1: select * from foo where a = b;
^

This is just like the OP's first example (which worked) except that
here there's no unknown literal, so we actually need to cast, and we
refuse to do so and fail.

I continue to be of the opinion that our behavior in this area is
bone-headed. It's reasonable to reject integer = character varying on
the basis that we don't know whether integer or character varying
comparison semantics are wanted, and the two might give different
answers (think: leading zeroes), so we'd better ask the user to
clarify. But '1' LIKE 1 is not ambiguous; there is only one
plausible meaning for that, and we ought to adopt it, per the patch I
proposed previously. Rejecting SQL that other systems happily accept
is unhelpful and unfriendly and it is costing us users and mind-share.

</rant>

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