Re: BUG #4078: ERROR: operator does not exist: numeric = character varying

The following bug has been logged online:

Bug reference: 4078
Logged by: rupesh
Email address: patil(dot)rupesh(at)gmail(dot)com
PostgreSQL version: 8.3
Operating system: linux 4.0
Description: ERROR: operator does not exist: numeric = character
varying
Details:

ERROR: operator does not exist: numeric = character varying at character
675
HINT: No operator matches the given name and argument type(s). You might
need to add explicit type casts.
(0.735 sec)

This was previously working in 8.2.3 but not in 8.3

Am Dienstag, 1. April 2008 schrieb rupesh:
> ERROR:  operator does not exist: numeric = character varying at character
> 675
> HINT:  No operator matches the given name and argument type(s). You might
> need to add explicit type casts.
> (0.735 sec)
>
>
> This was previously working in 8.2.3 but not in 8.3

This was an intentional change. Please read the release notes about fixing
your code.

Eric Haszlakiewicz <erh(at)swapsimple(dot)com> writes:
> I created this, which seems to solve the problem:

> create function casting_eq_operator(integer, "char")
> returns boolean as 'begin
> return $1 = cast ($2 as integer);
> end;' language plpgsql immutable strict;

> CREATE OPERATOR = (PROCEDURE = casting_eq_operator,
> LEFTARG = integer , RIGHTARG = "char",
> COMMUTATOR = =, NEGATOR = !=, HASHES, MERGES
> );

> Can this be included by default?

No. Even if we desired to reverse the decision about not having
implicit casting behavior, this definition of the operator would not be
appropriate because it provides the opposite of the old behavior.
The pre-8.3 behavior would have been to cast the integer to text and
apply a textual comparison; which gives different comparison behavior,
eg leading zeroes in the string would affect the result. Not to mention
that the cast to integer in this definition would fail outright if the
string didn't look like an integer.

A large part of the reasoning for getting rid of the implicit casts
was exactly that it's not very clear what a comparison of this sort
should act like, and most people who are accidentally invoking it
haven't thought that through either.

(Some other problems: I'm pretty sure you meant to refer to text or
varchar not "char"; you referenced commutator and negator operators
without defining them; this operator certainly does not hash, and
I don't think it merges either, though maybe you could make the latter
work if you'd provided all the requisite btree-opfamily infrastructure.)

regards, tom lane

On Mon, Oct 13, 2008 at 09:10:41PM -0400, Tom Lane wrote:
> Eric Haszlakiewicz <erh(at)swapsimple(dot)com> writes:
> > I created this, which seems to solve the problem:
>
> > create function casting_eq_operator(integer, "char")
> > returns boolean as 'begin
> > return $1 = cast ($2 as integer);
> > end;' language plpgsql immutable strict;
>
> > CREATE OPERATOR = (PROCEDURE = casting_eq_operator,
> > LEFTARG = integer , RIGHTARG = "char",
> > COMMUTATOR = =, NEGATOR = !=, HASHES, MERGES
> > );
>
> > Can this be included by default?
>
> No. Even if we desired to reverse the decision about not having
> implicit casting behavior, this definition of the operator would not be
> appropriate because it provides the opposite of the old behavior.
> The pre-8.3 behavior would have been to cast the integer to text and
> apply a textual comparison; which gives different comparison behavior,
> eg leading zeroes in the string would affect the result. Not to mention
> that the cast to integer in this definition would fail outright if the
> string didn't look like an integer.
>
> A large part of the reasoning for getting rid of the implicit casts
> was exactly that it's not very clear what a comparison of this sort
> should act like, and most people who are accidentally invoking it
> haven't thought that through either.

hmm.. I was thinking that a comparison between a number and a string
would cause an error if the string wasn't parseable. Now that I
think about it more, I can see that having it just return false might
be reasonable too.

>
> (Some other problems: I'm pretty sure you meant to refer to text or
> varchar not "char"; you referenced commutator and negator operators
> without defining them; this operator certainly does not hash, and
> I don't think it merges either, though maybe you could make the latter
> work if you'd provided all the requisite btree-opfamily infrastructure.)

Yeah, you're right. I didn't think that through entirely.
Sorry for the noise.

eric