Bug #104189 Wrong result for row constructor expression and in clause

• Submitted: 2 Jul 2021 8:10
• Modified: 2 Jul 2021 8:45
• Reporter: Shenghui Wu
• Status: Verified
• Category: MySQL Server: Optimizer
• Severity: S3 (Non-critical)
• Version: 5.7.32, 8.0.22, 5.7.34, 8.0.25
• OS: Any
• CPU Architecture: Any

[2 Jul 2021 8:10] Shenghui Wu

Description:
Wrong result for row constructor expression and in clause

How to repeat:
drop table if exists tbl_2;
create table tbl_2 (col_11 int, col_13 mediumint);
insert into tbl_2 values (13, 14);
select col_11,col_13 from tbl_2 where (col_11, col_13) in ((45, 46), (13.1, 14));
+--------+--------+
| col_11 | col_13 |
+--------+--------+
|     13 |     14 |
+--------+--------+
1 row in set (0.000 sec)

MySQL [test]> create table tbl_2 (col_11 int, col_13 mediumint);
Query OK, 0 rows affected (0.010 sec)

MySQL [test]> insert into tbl_2 values (13, 14);
Query OK, 1 row affected (0.002 sec)

[2 Jul 2021 8:24] Shenghui Wu

Sorry, I paste the wrong sql by mistakes. 
The last two sqls should be following:
MySQL [test]> select col_11,col_13 from tbl_2 where (col_11, col_13)  = (45,46);
Empty set (0.001 sec)

MySQL [test]> select col_11,col_13 from tbl_2 where (col_11, col_13)  = (13.1,14);
Empty set (0.000 sec)

[2 Jul 2021 8:45] MySQL Verification Team

Hello Shenghui Wu,

Thank you for the report and test case.

regards,
Umesh