Bug #111332 | Something went wrong with the implicit conversion when we select it. | ||
---|---|---|---|
Submitted: | 8 Jun 2023 10:43 | Modified: | 12 Jun 2023 6:54 |
Reporter: | linkang zhang | Email Updates: | |
Status: | Not a Bug | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S3 (Non-critical) |
Version: | 5.7, 8.0 | OS: | Any |
Assigned to: | CPU Architecture: | Any | |
Tags: | implicit conversion |
[8 Jun 2023 10:43]
linkang zhang
[8 Jun 2023 14:45]
MySQL Verification Team
HI Mr. zhang, Thank you for your bug report. However, this is not a bug. This is intended behaviour and it also persists in version 8.0. Simply, MySQL does not do strict data type checks. That would be probably best solution, but millions of our customers and users depend on this behaviour, so it will not changed soon. If you print warnings after each operation you will get much more helpful information, since the column that is compared has a value of 0: id uid 1 0 Level Code Message Warning 1292 Truncated incorrect DOUBLE value: 'my_name_is_it' id uid 1 0 Level Code Message Warning 1292 Truncated incorrect DOUBLE value: 'xxx' id uid 1 0 Level Code Message Warning 1292 Truncated incorrect DOUBLE value: 'xxx' Hence, the behaviour that you wish to be changed will not be supported for quite some time. Unsupported.
[9 Jun 2023 1:50]
linkang zhang
Change from MySQL Server to Data Types
[9 Jun 2023 1:54]
linkang zhang
OK, but in document, it means VARCHAR() type will be converted to DOUBLE type, and then convert to INT type. In this way, all VARCHAR() are changed to 0 is unreasonable.
[9 Jun 2023 12:02]
MySQL Verification Team
Hi Mr. zhang, Please, read out entire Reference Manual. There is a chapter on how MySQL handles exceptions. It has a set of converting types. Hence, your conclusion is not correct. Since, VARCHAR and INT types are not comparable, then BOTH are converted to DOUBLE and then compared. When the conversion does not work, then that tuple's value becomes 0.0 (zero). Hence, you should always look at the warnings ..... This report will remain in the status of "Unsupported" for a very good reason. There are millions of applications in the world that relies on this behaviour. Many of us would prefer the strict type checking. That means that for each of your queries you will get no results at all, but only the error. Hence, introducing such a huge change would break too many applications and installations.
[12 Jun 2023 6:54]
Roy Lyseng
This is not a bug. When comparing an integer and a character string, MySQL converts both to double precision floating point before the comparison. Standard SQL does not allow comparing a number and a character string, so this is a MySQL extension. Normally, a character string field must be interpreted fully as a floating point number. If there is something more than a number in the character string, the conversion is rejected with this error message: ERROR 1292 (22007): Truncated incorrect DOUBLE value: 'xxx' It can be seen if you do: INSERT INTO ins SELECT id FROM tb WHERE uid = 'xxx'; The handling of this error is governed by the strict mode setting. Strict mode ON is the default for MySQL, however unfortunately this setting is only enabled for data change statements like INSERT and UPDATE, it is not enabled for SELECT statements. Thus, for SELECT you will see the error message converted to a warning: Warning | 1292 | Truncated incorrect DOUBLE value: 'xxx' One way to transition this for future versions of MySQL might be to add a "super-strict" mode that is also in effect for SELECT statements. Over time, this could be made the default setting in the server, and eventually we could deprecate and remove non-strict handling, which is mostly confusing for users, in addition to complicating the logic of the MySQL server. But there is no way we can do this as a simple bugfix, since there might be a large number of applications actually relying upon this feature.