Resolving MySQL implicit conversion issues

1. Problem Description

root@mysqldb 22:12: [xucl]> show create table t1\G
*************************** 1. row ***************************
 Table: t1
Create Table: CREATE TABLE `t1` (
 `id` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
 
root@mysqldb 22:19: [xucl]> select * from t1;
+--------------------+
|id|
+--------------------+
| 204027026112927605 |
| 204027026112927603 |
| 2040270261129276 |
| 2040270261129275 |
| 100 |
| 101 |
+--------------------+
6 rows in set (0.00 sec)

Strange phenomenon:

root@mysqldb 22:19: [xucl]> select * from t1 where id=204027026112927603;
+--------------------+
|id|
+--------------------+
| 204027026112927605 |
| 204027026112927603 |
+--------------------+
2 rows in set (0.00 sec)
640?wx_fmt=jpeg 

What the hell, I checked 204027026112927603, why did 204027026112927605 also come out?

2. Source code explanation

The call stack relationship is as follows:

JOIN::exec() is the execution entry point, and Arg_comparator::compare_real() is a function for equal value judgment, which is defined as follows

int Arg_comparator::compare_real()
{
 /*
 Fix yet another manifestation of Bug#2338. 'Volatile' will instruct
 gcc to flush double values ​​out of 80-bit Intel FPU registers before
 performing the comparison.
 */
 volatile double val1, val2;
 val1= (*a)->val_real();
 if (!(*a)->null_value)
 {
 val2 = (*b)->val_real();
 if (!(*b)->null_value)
 {
 if (set_null)
 owner->null_value = 0;
 if (val1 < val2) return -1;
 if (val1 == val2) return 0;
 return 1;
 }
 }
 if (set_null)
 owner->null_value = 1;
 return -1;
}

The comparison steps are shown in the figure below. The id column of the t1 table is read row by row and put into val1. The constant 204027026112927603 exists in the cache and its type is double (2.0402702611292762E+17). Therefore, after the value is passed to val2, val2=2.0402702611292762E+17.

When scanning to the first row, the value of 204027026112927605 converted to doule is 2.0402702611292762e17, the equation is established, and it is determined to be a qualified row, and the scan continues. Similarly, 204027026112927603 also meets the conditions.

How to detect whether the conversion of string type numbers to double type overflows? After testing here, when the number exceeds 16 digits, the conversion to double type is no longer accurate. For example, 20402702611292711 will be expressed as 20402702611292712 (as shown in val1 in the figure)

The definition function for converting MySQL string to double is as follows:

{
 char buf[DTOA_BUFF_SIZE];
 double res;
 DBUG_ASSERT(end != NULL && ((str != NULL && *end != NULL) ||
    (str == NULL && *end == NULL)) &&
  error != NULL);

 res = my_strtod_int(str, end, error, buf, sizeof(buf));
 return (*error == 0) ? res : (res < 0 ? -DBL_MAX : DBL_MAX);
}

The actual conversion function my_strtod_int is located in dtoa.c (too complicated, just post a comment)

/*
 strtod for IEEE--arithmetic machines.
 
 This strtod returns a nearest machine number to the input decimal
 string (or sets errno to EOVERFLOW). Ties are broken by the IEEE round-even
 rule.
 
 Inspired loosely by William D. Clinger's paper "How to Read Floating
 Point Numbers Accurately" [Proc. ACM SIGPLAN '90, pp. 92-101].
 
 Modifications:
 
 1. We only require IEEE (not IEEE double-extended).
 2. We get by with floating-point arithmetic in a case that
 Clinger missed -- when we're computing d * 10^n
 for a small integer d and the integer n is not too
 much larger than 22 (the maximum integer k for which
 we can represent 10^k exactly), we may be able to
 compute (d*10^k) * 10^(ek) with just one roundoff.
 3. Rather than a bit-at-a-time adjustment of the binary
 As a result in the hard case, we use floating-point
 arithmetic to determine the adjustment to within
 one bit; only in really hard cases do we need to
 compute a second residual.
 4. Because of 3., we don't need a large table of powers of 10
 for ten-to-e (just some small tables, eg of 10^k
 for 0 <= k <= 22).
*/

In this case, let's test the case where there is no overflow

root@mysqldb 23:30: [xucl]> select * from t1 where id=2040270261129276;
+------------------+
|id|
+------------------+
| 2040270261129276 |
+------------------+
1 row in set (0.00 sec)
 
root@mysqldb 23:30: [xucl]> select * from t1 where id=101;
+------+
|id|
+------+
| 101 |
+------+
1 row in set (0.00 sec)

The result is as expected, and in this case, the correct way to write it should be

root@mysqldb 22:19: [xucl]> select * from t1 where id='204027026112927603';
+--------------------+
|id|
+--------------------+
| 204027026112927603 |
+--------------------+
1 row in set (0.01 sec)

Conclusion

Avoid implicit type conversions. Implicit conversions mainly include inconsistent field types, multiple types in the in parameter, inconsistent character set types or proofreading rules, etc.

Implicit type conversion may result in the inability to use indexes, inaccurate query results, etc., so you must carefully identify them when using them.

It is recommended to define the numeric type as int or bigint when defining the field. When the table is linked, the associated fields must maintain the same type, character set, and collation rules.

Finally, let me post the official website's description of implicit type conversion.

1. If one or both arguments are NULL, the result of the comparison is NULL, except for the NULL-safe
<=> equality comparison operator. For NULL <=> NULL, the result is true. No conversion is needed.
2.If both arguments in a comparison operation are strings, they are compared as strings.
3. If both arguments are integers, they are compared as integers.
4. Hexadecimal values ​​are treated as binary strings if not compared to a number.
5. If one of the arguments is a TIMESTAMP or DATETIME column and the other argument is a
constant, the constant is converted to a timestamp before the comparison is performed. This is
done to be more ODBC-friendly. This is not done for the arguments to IN(). To be safe, always
Use complete datetime, date, or time strings when doing comparisons. For example, to achieve best
results when using BETWEEN with date or time values, use CAST() to explicitly convert the values ​​to
the desired data type.
A single-row subquery from a table or tables is not considered a constant. For example, if a subquery
returns an integer to be compared to a DATETIME value, the comparison is done as two integers.
The integer is not converted to a temporal value. To compare the operands as DATETIME values,
use CAST() to explicitly convert the subquery value to DATETIME.
6. If one of the arguments is a decimal value, comparison depends on the other argument.
The arguments are compared as decimal values ​​if the other argument is a decimal or integer value, or as
floating-point values ​​if the other argument is a floating-point value.
7. In all other cases, the arguments are compared as floating-point (real) numbers.

Summarize

The above is the MySQL implicit conversion introduced by the editor. I hope it will be helpful to everyone. If you have any questions, please leave me a message and the editor will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website!
If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!