differences between <> and != when using signed values on the right hand side

Started by David Hunnisettalmost 2 years ago3 messagesbugs
Jump to latest
#1David Hunnisett
david.hunnisett@probit.io

When using a signed comparison != does not behave like <> if the right hand side comparator is signed and there is no space between the comparator and the sign

<>+1
<>-1
Both bahave as the inverse to
=+1
=-1
But
!=-1
!=+1
Throw errors.

Tested with 16.3,13.15 and 9.6.24

postgres=# select version();
version
---------------------------------------------------------------------------------------------------------------------------
PostgreSQL 16.3 (Debian 16.3-1.pgdg120+1) on aarch64-unknown-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
(1 row)

postgres=# select 1<>-1.0;
?column?
----------
t
(1 row)

postgres=# select 1<>-1;
?column?
----------
t
(1 row)

postgres=# select 1!=-1.0;
ERROR: operator does not exist: integer !=- numeric
LINE 1: select 1!=-1.0;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.
postgres=# select 1!=-1;
ERROR: operator does not exist: integer !=- integer
LINE 1: select 1!=-1;
^
HINT: No operator matches the given name and argument types. You might need to add explicit type casts.

Adding a space does produce the expected answer

select 1!= -1.0;
?column?
----------
t
(1 row)

#2David G. Johnston
david.g.johnston@gmail.com
In reply to: David Hunnisett (#1)
Re: differences between <> and != when using signed values on the right hand side

On Thursday, May 16, 2024, David Hunnisett <david.hunnisett@probit.io>
wrote:

When using a signed comparison != does not behave like <> if the right
hand side comparator is signed and there is no space between the comparator
and the sign

This is not a bug. You are seeing practical examples of the behavior
documented here:

https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS

In short, a consequence of allowing users to define their own operators.

David J.

#3Tom Lane
tgl@sss.pgh.pa.us
In reply to: David G. Johnston (#2)
Re: differences between <> and != when using signed values on the right hand side

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Thursday, May 16, 2024, David Hunnisett <david.hunnisett@probit.io>
wrote:

When using a signed comparison != does not behave like <> if the right
hand side comparator is signed and there is no space between the comparator
and the sign

This is not a bug. You are seeing practical examples of the behavior
documented here:
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-OPERATORS

Yeah, it would probably have been better to forbid multicharacter
operator names that end in '+' or '-'. But that ship sailed a long
time ago, so we have this kluge to (effectively) allow it only if
the operator can't be a SQL-standard one. Thus, in the example
"!=-" is being read as a single operator name.

regards, tom lane