Bug #118137 Contradictory query results for decimal data type
Submitted: 8 May 11:49 Modified: 8 May 12:08
Reporter: ximin liang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DML Severity:S3 (Non-critical)
Version:9.3, 8.0, 8.4 OS:Any
Assigned to: CPU Architecture:Any

[8 May 11:49] ximin liang 
Description:
Hello MySQL team: 
  Seems like query results with implicit conversion of DECIMAL data type are problematic.
  Can be repeated in MySQL 9.3.

How to repeat:
CREATE TABLE decimaltest ( djxh decimal(20, 0) NOT NULL, PRIMARY KEY (djxh) );
INSERT INTO decimaltest(djxh) VALUES (10112102010000126008);
SELECT djxh FROM decimaltest WHERE djxh IN ( "10112102010000126001", "1","2",3,4,5,6) order by djxh;
+----------------------+
| djxh                 |
+----------------------+
| 10112102010000126008 |
+----------------------+
1 row in set (0.002 sec)

mysql> SELECT djxh FROM decimaltest WHERE djxh IN ( "10112102010000126001") order by djxh;
Empty set (0.001 sec)

seems const table optimize causes differences between two queries.
[8 May 12:08] MySQL Verification Team 
Hello ximin liang,

Thank you for the report and test case.

regards,
Umesh