An exception about comparison operators

On Wed, 2021-05-26 at 17:15 +0800, 张元超 wrote:

I encountered a problem when using PostgreSQL's comparison operators. The problem is as follows:
Problem Description:
When I use the comparison operator "!=" as the query condition, such as "select * from t1 where c1 !=-1", the database returns an error: "!=-operator does not exist". Because there is no space
between ‘=’ and ‘-’, if you enter a space between them, the sql can be executed normally. Therefore, although we can make sql execute normally by adding spaces, its behavior is different from other
comparison operators (such as ">,<,>=,<=,=,<>"). Other comparisons Operators will not have such problems.

I guess that this should be because the database did not correctly handle the "!=" operator during sql parsing, so I think this should be a bug. This problem exists in the 11, 12, and 13 versions of
PostgreSQL.

At the same time, I tried other databases, such as Oracle, but did not find the same problem.
Looking forward to your reply.

See this comment in "scan.l":

/*
* For SQL compatibility, '+' and '-' cannot be the
* last char of a multi-char operator unless the operator
* contains chars that are not in SQL operators.
* The idea is to lex '=-' as two operators, but not
* to forbid operator names like '?-' that could not be
* sequences of SQL operators.
*/

That means that a training minus is only treated as not belonging to
the operator name if the preceding characters belong to a standard SQL
operator name. Now "<" and ">" are standard operator characters, so
"<>-" is treated as two tokens.
But "!" does not appear in SQL standard operators, so there is no special
processing.

This is a hack to allow constructs like 1<>-2, which are required to
comply with the SQL standard.

If you want this behavior, sitch to standard SQL operator names and
don't use !=.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com