MySQL Bugs: #118956: Incorrect result when condition use concat function

Bug #118956	Incorrect result when condition use concat function
Submitted:	8 Sep 11:41	Modified:	9 Sep 23:48
Reporter:	jianhua sun	Email Updates:
Status:	Need Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	8.0.32+	OS:	Any
Assigned to:	MySQL Verification Team	CPU Architecture:	Any
Tags:	concat, incorrect, INT, join, string

Description:
In MySQL versions 8.0.32 and later, when performing an equi-join between two tables involving the CONCAT function, inconsistent results are returned depending on whether string or integer comparison is used in the WHERE clause. Specifically, when comparing a VARCHAR column with an integer value (rather than a string literal), the query returns incorrect results despite the values being numerically equivalent.

The issue occurs when:

A JOIN condition uses CONCAT to form a value that should match a BIGINT UNSIGNED column
A WHERE clause filters a VARCHAR column using either string or integer comparison
The integer comparison produces wrong results while the equivalent string comparison works correctly
This appears to be a type conversion/casting issue where MySQL fails to properly handle the implicit conversion between VARCHAR and numeric types during the JOIN execution phase. The problem is particularly concerning because it leads to data inconsistency where logically equivalent queries (using string vs integer comparison) return different results, potentially causing application-level data errors that are difficult to detect.

The issue is reproducible on MySQL 8.0.32+ (tested on 8.0.32, 8.0.43) and appears to be related to how the optimizer handles type conversions in JOIN conditions combined with WHERE clause filters.

How to repeat:
drop table t1;
CREATE TABLE `t1` (
  `c1` bigint unsigned NOT NULL,
  `c2` bigint NOT NULL,
  `c3` bigint NOT NULL,
  PRIMARY KEY (`c1`),
  KEY `key_c2` (`c2`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t1(c1, c2, c3) values (10009700001000000, 100097, 100097);
insert into t1(c1, c2, c3) values (10009700001000001, 100097, 100097);
insert into t1(c1, c2, c3) values (10009700001000002, 100097, 100097);
insert into t1(c1, c2, c3) values (10009700001000003, 100097, 100097);
insert into t1(c1, c2, c3) values (10009700001000004, 100097, 100097);

drop table t2;
CREATE TABLE `t2` (
  `c1` varchar(40) NOT NULL,
  `c2` bigint NOT NULL,
  `c3` bigint unsigned NOT NULL,
  PRIMARY KEY (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000000);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000001);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000002);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000003);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000004);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000005);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000006);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000007);
insert into t2(c1, c2, c3) values (20240820003914360, 437817, 1000008);

/*Q1 t1.c3 without index, t2.c1 equal to string, result corrent */
SELECT t1.*, t2.*
FROM t1
JOIN t2
    ON t1.c1 = concat(100097, '0000', t2.c3)
WHERE t1.c3 = 100097
        AND t2.c1 = '20240820003914360'
        AND t2.c2 = 437817; 

/*Q2 t1.c3 without index, t2.c1 equal to integer, result wrong */
SELECT t1.*, t2.*
FROM t1
JOIN t2
    ON t1.c1 = concat(100097, '0000', t2.c3)
WHERE t1.c3 = 100097
        AND t2.c1 = 20240820003914360
        AND t2.c2 = 437817; 

mysql> /*Q1 t1.c3 without index, t2.c1 equal to string, result wrong*/
mysql> SELECT t1.*, t2.*
    -> FROM t1
    -> JOIN t2
    ->     ON t1.c1 = concat(100097, '0000', t2.c3)
    -> WHERE t1.c3 = 100097
    ->         AND t2.c1 = '20240820003914360'
    ->         AND t2.c2 = 437817;
+-------------------+--------+--------+-------------------+--------+---------+
| c1                | c2     | c3     | c1                | c2     | c3      |
+-------------------+--------+--------+-------------------+--------+---------+
| 10009700001000000 | 100097 | 100097 | 20240820003914360 | 437817 | 1000000 |
| 10009700001000000 | 100097 | 100097 | 20240820003914360 | 437817 | 1000001 |
| 10009700001000001 | 100097 | 100097 | 20240820003914360 | 437817 | 1000000 |
| 10009700001000001 | 100097 | 100097 | 20240820003914360 | 437817 | 1000001 |
| 10009700001000002 | 100097 | 100097 | 20240820003914360 | 437817 | 1000002 |
| 10009700001000003 | 100097 | 100097 | 20240820003914360 | 437817 | 1000003 |
| 10009700001000003 | 100097 | 100097 | 20240820003914360 | 437817 | 1000004 |
| 10009700001000003 | 100097 | 100097 | 20240820003914360 | 437817 | 1000005 |
| 10009700001000004 | 100097 | 100097 | 20240820003914360 | 437817 | 1000003 |
| 10009700001000004 | 100097 | 100097 | 20240820003914360 | 437817 | 1000004 |
| 10009700001000004 | 100097 | 100097 | 20240820003914360 | 437817 | 1000005 |
| 10009700001000005 | 100097 | 100097 | 20240820003914360 | 437817 | 1000003 |
| 10009700001000005 | 100097 | 100097 | 20240820003914360 | 437817 | 1000004 |
| 10009700001000005 | 100097 | 100097 | 20240820003914360 | 437817 | 1000005 |
+-------------------+--------+--------+-------------------+--------+---------+
14 rows in set (0.00 sec)

mysql>
mysql> /*Q2 t1.c3 without index, t2.c1 equal to integer, result corrent*/
mysql> SELECT t1.*, t2.*
    -> FROM t1
    -> JOIN t2
    ->     ON t1.c1 = concat(100097, '0000', t2.c3)
    -> WHERE t1.c3 = 100097
    ->         AND t2.c1 = 20240820003914360
    ->         AND t2.c2 = 437817;
+-------------------+--------+--------+-------------------+--------+---------+
| c1                | c2     | c3     | c1                | c2     | c3      |
+-------------------+--------+--------+-------------------+--------+---------+
| 10009700001000000 | 100097 | 100097 | 20240820003914360 | 437817 | 1000000 |
| 10009700001000001 | 100097 | 100097 | 20240820003914360 | 437817 | 1000001 |
| 10009700001000002 | 100097 | 100097 | 20240820003914360 | 437817 | 1000002 |
| 10009700001000003 | 100097 | 100097 | 20240820003914360 | 437817 | 1000003 |
| 10009700001000004 | 100097 | 100097 | 20240820003914360 | 437817 | 1000004 |
| 10009700001000005 | 100097 | 100097 | 20240820003914360 | 437817 | 1000005 |
+-------------------+--------+--------+-------------------+--------+---------+
6 rows in set (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                            |
+-----------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION |
+-----------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Hi,

> t1.c1 = concat(100097, '0000', t2.c3)

This is undefined behavior.
What do you get if you use

t1.c1 = cast(concat(100097, '0000', t2.c3), signed)

Thanks