MySQL Bugs: #116050: The query results are incorrect when using CTE, CONCAT, and IN.

Bug #116050	The query results are incorrect when using CTE, CONCAT, and IN.
Submitted:	10 Sep 2024 6:14	Modified:	10 Sep 2024 8:39
Reporter:	heng yue	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	8.0.39, 8.4.2	OS:	Linux (centos7)
Assigned to:		CPU Architecture:	x86

Description:
When using CTE queries, if CONCAT is used to concatenate results and then the concatenated result is used as a condition for subsequent queries, the results may be incorrect.

How to repeat:
1、git clone https://github.com/yhld456/mysqltestdata.git
2、source test1.sql
3、source test2.sql
4、Execute the following statement to perform the query; the result is incorrect.

with tmp1 as(
  select 
  CONCAT('["',t1.id,'"]') as id
  from test1 t1 where t1.role_code='588408423353684352'
)
select * from test2 t1 where t1.ensure_role in(select id from tmp1);

5、Create the index by executing the following statement, and then the query results will be correct.
CREATE INDEX idx_ensure_role ON test2 (ensure_role);

sql

Attachment: test1.sql (application/octet-stream, text), 1.70 KiB.

sql

Attachment: test2.sql (application/octet-stream, text), 69.39 KiB.

Hello heng yue,

Thank you for the report and test case.

regards,
Umesh