Re: Strange Type Mismatch on Insert

Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> writes:
> I have a fix, using oper() to look for an = operator. I noticed
> while doing that though that varchar and int4 appear to be comparable
> while text and int4 aren't which seemed rather odd.

regression=# select '44'::varchar = 44::int4;
?column?
----------
t
(1 row)

regression=# select '44'::text = 44::int4;
ERROR: Unable to identify an operator '=' for types 'text' and 'int4'
You will have to retype this query using an explicit cast
regression=#

Bizarre. Investigation shows that the first case is handled as

varchareq('44'::varchar, varchar(44::int4))

where the conversion function is pg_proc OID 1619. The exact same
C function is also declared as text(int4), OID 112, but the system
won't make the comparable promotion in that case.

Upon stepping through oper_select_candidate, I find that the problem
is that text is *too* coercible. We have relatively few coercions
from varchar to something else, so the routine is fairly easily able
to choose a single candidate operator (it has only 2 candidates after
the first round of eliminations, and only 1 after the second). But
text has a ton of coercions to other types, meaning that 18 candidate
operators survive the first round, 5 the second and third rounds,
and even after round 4 there are two candidates left (text = text and
oid = int4, as it happens). So it fails to choose a unique candidate.

This is a little discouraging, since it raises the likelihood that
oper_select_candidate will break down entirely if we were to do any
wholesale extension of coercibility (like making text convertible to
anything via datatype input functions). We wouldn't have this failure
if there weren't an oid(text) coercion function ... now imagine what
happens if there's text-to-anything.

I've always been a tad bothered by the way we handle ambiguity
resolution, but I don't have a better answer to offer offhand.
Something to think about for the future.

regards, tom lane