From: | Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> |
---|---|
To: | 张元超 <chaoge145(at)163(dot)com>, pgsql-general(at)lists(dot)postgresql(dot)org |
Subject: | Re: An exception about comparison operators |
Date: | 2021-05-26 10:42:59 |
Message-ID: | 7b3af05a756efa4b06c037805183af312c95feaa.camel@cybertec.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Rob Sargent | 2021-05-26 17:50:19 | How different is AWS-RDS postgres? |
Previous Message | 张元超 | 2021-05-26 09:15:00 | An exception about comparison operators |