Bug #92729 mysql equal condition checking fail between decimal and varchar
Submitted: 10 Oct 2018 8:55 Modified: 10 Oct 2018 13:40
Reporter: dennis gao Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:5.6/5.7/8.0 OS:Any
Assigned to: CPU Architecture:Any

[10 Oct 2018 8:55] dennis gao 
Description:
If the where condition is a equal condition between a decimal column and a varchar column, mysql may return a wrong result.

How to repeat:
mysql> create table t (`SCHEMA_ID` decimal(16,0) NOT NULL ,`SCHEMA_NO` varchar(16) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, primary key (SCHEMA_ID));
Query OK, 0 rows affected (0.39 sec)
mysql> insert into t values (9413031410036312,'9413031410036311');
Query OK, 1 row affected (0.26 sec)

mysql> select * from t where SCHEMA_ID=SCHEMA_NO;
+------------------+------------------+
| SCHEMA_ID        | SCHEMA_NO        |
+------------------+------------------+
| 9413031410036312 | 9413031410036311 |
+------------------+------------------+
1 row in set (0.58 sec)

mysql> 

IMO, mysql should return an empty result.
[10 Oct 2018 13:40] MySQL Verification Team 
Thank you for the bu report.