Bug #106272 int-decimal. using left join, the returned result is incorrect
Submitted: 25 Jan 2022 9:07 Modified: 2 Feb 2022 14:10
Reporter: yu zhao Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Optimizer Severity:S2 (Serious)
Version:mysql Ver 8.0.27 for Linux on x86_64 (M, 8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[25 Jan 2022 9:07] yu zhao 
Description:
int-decimal. using left join, the returned result is incorrect

How to repeat:
drop table if exists t1, t2;
create table t1  (c_int int, c_str varchar(40) , c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6), c_enum enum('blue','green','red','yellow','white','orange','purple'), primary key (c_int)  , key(c_str)) ;
create table t2  (c_int int, c_str varchar(40) character set utf8 collate utf8_bin, c_datetime datetime, c_timestamp timestamp, c_double double, c_decimal decimal(12, 6), c_enum enum('blue','green','red','yellow','white','orange','purple'), primary key (c_int)   , key(c_datetime)) ;
insert into t1 values (1, 'eager mclean', '2020-03-25 17:57:29', '2020-03-12 04:42:00', 72.186047, 6.456, 'orange'), (2, 'elegant cannon', '2020-02-04 11:12:39', '2020-06-28 19:49:40', 30.090353, 3.225, 'purple'), (3, 'charming fermat', '2020-04-10 14:23:34', '2020-01-02 05:15:39', 63.706913, 8.128, 'blue'), (4, 'hardcore murdock', '2020-02-07 17:41:18', '2020-02-29 22:33:41', 85.608637, 2.454, 'white'), (5, 'festive lederberg', '2020-04-21 21:38:13', '2020-05-26 07:32:57', 33.274556, 4.544, 'red');
insert into t1 values (6, 'objective torvalds', '2020-01-24 13:17:09', '2020-02-18 03:56:08', 58.319620, 2.309, 'purple'), (7, 'elegant ptolemy', '2020-06-10 20:11:12', '2020-01-02 12:38:42', 1.360234, 6.602, 'purple'), (8, 'ecstatic borg', '2020-02-24 05:26:23', '2020-01-29 12:26:30', 79.420195, 6.134, 'orange'), (9, 'busy chebyshev', '2020-01-14 13:33:42', '2020-06-06 19:51:46', 70.315198, 8.447, 'red'), (10, 'recursing cori', '2020-04-14 09:09:35', '2020-04-18 03:11:11', 10.990345, 9.088, 'white');
insert into t2 select * from t1 ;
select * from t1 left join t2 on t1.c_int - t1.c_decimal = t2.c_int - t2.c_decimal ;

expect:
mysql> select * from t1 left join t2 on t1.c_int - t1.c_decimal = t2.c_int - t2.c_decimal ;
+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+
| c_int | c_str              | c_datetime          | c_timestamp         | c_double  | c_decimal | c_enum | c_int | c_str              | c_datetime          | c_timestamp         | c_double  | c_decimal | c_enum |
+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+
|     1 | eager mclean       | 2020-03-25 17:57:29 | 2020-03-12 04:42:00 | 72.186047 |  6.456000 | orange |     1 | eager mclean       | 2020-03-25 17:57:29 | 2020-03-12 04:42:00 | 72.186047 |  6.456000 | orange |
|     2 | elegant cannon     | 2020-02-04 11:12:39 | 2020-06-28 19:49:40 | 30.090353 |  3.225000 | purple |     2 | elegant cannon     | 2020-02-04 11:12:39 | 2020-06-28 19:49:40 | 30.090353 |  3.225000 | purple |
|     3 | charming fermat    | 2020-04-10 14:23:34 | 2020-01-02 05:15:39 | 63.706913 |  8.128000 | blue   |     3 | charming fermat    | 2020-04-10 14:23:34 | 2020-01-02 05:15:39 | 63.706913 |  8.128000 | blue   |
|     4 | hardcore murdock   | 2020-02-07 17:41:18 | 2020-02-29 22:33:41 | 85.608637 |  2.454000 | white  |     4 | hardcore murdock   | 2020-02-07 17:41:18 | 2020-02-29 22:33:41 | 85.608637 |  2.454000 | white  |
|     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |
|     6 | objective torvalds | 2020-01-24 13:17:09 | 2020-02-18 03:56:08 |  58.31962 |  2.309000 | purple |     6 | objective torvalds | 2020-01-24 13:17:09 | 2020-02-18 03:56:08 |  58.31962 |  2.309000 | purple |
|     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |
|     8 | ecstatic borg      | 2020-02-24 05:26:23 | 2020-01-29 12:26:30 | 79.420195 |  6.134000 | orange |     8 | ecstatic borg      | 2020-02-24 05:26:23 | 2020-01-29 12:26:30 | 79.420195 |  6.134000 | orange |
|     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |
|    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |
+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+
10 rows in set (0.01 sec)

actual:
mysql> select * from t1 left join t2 on t1.c_int - t1.c_decimal = t2.c_int - t2.c_decimal ;
+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+
| c_int | c_str              | c_datetime          | c_timestamp         | c_double  | c_decimal | c_enum | c_int | c_str              | c_datetime          | c_timestamp         | c_double  | c_decimal | c_enum |
+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+
|     1 | eager mclean       | 2020-03-25 17:57:29 | 2020-03-12 04:42:00 | 72.186047 |  6.456000 | orange |     1 | eager mclean       | 2020-03-25 17:57:29 | 2020-03-12 04:42:00 | 72.186047 |  6.456000 | orange |
|     2 | elegant cannon     | 2020-02-04 11:12:39 | 2020-06-28 19:49:40 | 30.090353 |  3.225000 | purple |     2 | elegant cannon     | 2020-02-04 11:12:39 | 2020-06-28 19:49:40 | 30.090353 |  3.225000 | purple |
|     3 | charming fermat    | 2020-04-10 14:23:34 | 2020-01-02 05:15:39 | 63.706913 |  8.128000 | blue   |     3 | charming fermat    | 2020-04-10 14:23:34 | 2020-01-02 05:15:39 | 63.706913 |  8.128000 | blue   |
|     4 | hardcore murdock   | 2020-02-07 17:41:18 | 2020-02-29 22:33:41 | 85.608637 |  2.454000 | white  |     4 | hardcore murdock   | 2020-02-07 17:41:18 | 2020-02-29 22:33:41 | 85.608637 |  2.454000 | white  |
|     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |
|     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |
|     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |
|     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |
|     6 | objective torvalds | 2020-01-24 13:17:09 | 2020-02-18 03:56:08 |  58.31962 |  2.309000 | purple |     6 | objective torvalds | 2020-01-24 13:17:09 | 2020-02-18 03:56:08 |  58.31962 |  2.309000 | purple |
|     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |
|     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |
|     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |
|     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |
|     8 | ecstatic borg      | 2020-02-24 05:26:23 | 2020-01-29 12:26:30 | 79.420195 |  6.134000 | orange |     8 | ecstatic borg      | 2020-02-24 05:26:23 | 2020-01-29 12:26:30 | 79.420195 |  6.134000 | orange |
|     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |
|     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |
|     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |
|     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |
|    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |
|    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |     9 | busy chebyshev     | 2020-01-14 13:33:42 | 2020-06-06 19:51:46 | 70.315198 |  8.447000 | red    |
|    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |     7 | elegant ptolemy    | 2020-06-10 20:11:12 | 2020-01-02 12:38:42 |  1.360234 |  6.602000 | purple |
|    10 | recursing cori     | 2020-04-14 09:09:35 | 2020-04-18 03:11:11 | 10.990345 |  9.088000 | white  |     5 | festive lederberg  | 2020-04-21 21:38:13 | 2020-05-26 07:32:57 | 33.274556 |  4.544000 | red    |
+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+-------+--------------------+---------------------+---------------------+-----------+-----------+--------+
22 rows in set (0.00 sec)
[25 Jan 2022 9:20] MySQL Verification Team 
Hello yu zhao,

Thank you for the report and test case.

regards,
Umesh
[2 Feb 2022 14:10] Jon Stephens 
Documented fix as follows in the MySQL 8.0.29 changelog:

    When writing DECIMAL values to the hash join buffer, the values
    were first normalized. This normalization could in some cases
    unintentionally alter the values, which made the values fall
    into the wrong hash bucket and caused wrong results to be
    returned from the join.

    The problem was that the normalization removed leading zeros in
    a manner which caused some values to lose some of their least
    significant digits. We fix this by calculating the desired
    precision and passing the calculated value instead of changing
    the original value.

Closed.