| Bug #115096 | select query returns abnormal data | ||
|---|---|---|---|
| Submitted: | 23 May 9:08 | Modified: | 23 May 9:57 |
| Reporter: | pengbo li | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | 8.0.37，5.7.43，5.7.21 | OS: | Linux |
| Assigned to: | CPU Architecture: | x86 | |
[23 May 9:10]
pengbo li
mysql> select * from db1.t1; +---------------------+ | id | +---------------------+ | 2401161653005274542 | +---------------------+ 1 row in set (0.00 sec) mysql> mysql> show create table db1.t1; +-------+--------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------+ | t1 | CREATE TABLE `t1` ( `id` bigint NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin | +-------+--------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
[23 May 9:36]
MySQL Verification Team
Hi Mr. Li, Thank you for your bug report. However , this is not a bug. You are using completely different data types in the expression. You are mixing integer type with a string type. According to the SQL Standard, you should get the error message, without any results. However, MySQL tries to do more, so we have set our own conversion technique. When two different data types are mixed in an expression, which is forbidden by standard, we choose a third data type that could cover the error as well as it is possible. When you mix integer and string types, MySQL's common denominator is the floating point type. Hence, you do not get a result that you wished. In order to get correct result, do not use strings for integers, just use simple integers. Not a bug.
[23 May 9:52]
pengbo li
So a better way is when the implicit conversion fails, MySQL should return an error or failure message, not a seemingly normal data!
[23 May 9:57]
pengbo li
However, MySQL did not prompt any warning or error messages during the data return process,This is not very rigorous, and for a mature database software, it is intolerable
[23 May 9:58]
MySQL Verification Team
HI Mr. li, We are afraid that this is not possible. A vast majority of users and customers prefer NOT to get the error message. Indeed, we do not know of any RDBMS that returns the error. Most of them choose the common denominator. Each one a different set of common denominators.
Description: Hi,My Dear Friends: Today, I discovered that during the select query process in MySQL, when there is an implicit conversion from char type to bigint type in the where condition (the value of the implicit conversion is large), it will return incorrect data! How to repeat: like this: mysql> select @@version; +-----------+ | @@version | +-----------+ | 8.0.37 | +-----------+ 1 row in set (0.00 sec) mysql> create database db1; Query OK, 1 row affected (0.02 sec) mysql> use db1; Database changed mysql> CREATE TABLE t1 ( -> `id` bigint NOT NULL -> ); Query OK, 0 rows affected (0.05 sec) mysql> mysql> INSERT INTO t1 VALUES (2401161653005274542); Query OK, 1 row affected (0.00 sec) mysql> mysql> select id from t1 where id IN ( '2401161653005274571','24043014181510711899'); +---------------------+ | id | +---------------------+ | 2401161653005274542 | +---------------------+ 1 row in set (0.00 sec)