Bug #118037 column with numeric type doesn't work expected in condition

• Submitted: 23 Apr 8:56
• Modified: 2 May 7:22
• Reporter: Chunling Qin
• Status: Duplicate
• Category: MySQL Server: DML
• Severity: S3 (Non-critical)
• Version: 8.4.3
• OS: CentOS
• CPU Architecture: Any

[23 Apr 8:56] Chunling Qin

Description:
MySQL [fuzztest]> CREATE TABLE t (id int, b numeric);
Query OK, 0 rows affected (0.00 sec)

MySQL [fuzztest]> insert /*! IGNORE */  into t values (2,0);
Query OK, 1 row affected (0.00 sec)

MySQL [fuzztest]> select * from t where  b < -0.9  ;<====where b is 0, the condition is false, so it should return empty.

+------+------+
| id   | b    |
+------+------+
|    2 |    0 |
+------+------+
1 row in set (0.00 sec)

MySQL [fuzztest]> 
MySQL [fuzztest]> select 0>-0.9 from dual; <===
+--------+
| 0>-0.9 |
+--------+
|      1 |
+--------+
1 row in set (0.00 sec)
MySQL [fuzztest]> select 0<-0.9 from dual; <===here, it is expected.
+--------+
| 0<-0.9 |
+--------+
|      0 |
+--------+
1 row in set (0.00 sec)

The result from pg:
postgres=# drop table t;
DROP TABLE
postgres=# CREATE TABLE t (id int, b numeric);
CREATE TABLE
postgres=# insert /*! IGNORE */  into t values (2,0);
INSERT 0 1
postgres=# select * from t where  b < -0.9  ;
 id | b 
----+---
(0 rows)

postgres=# 
postgres=# select 0>-0.9 from dual;
 ?column? 
----------
 t
(1 row)

How to repeat:
drop table t;
CREATE TABLE t (id int, b numeric);
insert /*! IGNORE */  into t values (2,0);
select * from t where  b < -0.9  ;

select 0>-0.9 from dual;

[2 May 5:38] MySQL Verification Team

Hello Chunling Qin,

Thank you for the bug report.
Verified as described.

Regards,
Ashwini Patil

[2 May 7:21] Roy Lyseng

Posted by developer:
 
This is duplicate of bug#114196/36361165, which is fixed in MySQL 9.0 and upwards.