MySQL Bugs: #104989: illegal mix of collations

Bug #104989	illegal mix of collations
Submitted:	21 Sep 2021 2:39	Modified:	23 Sep 2021 7:55
Reporter:	A Chan	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Workbench: SQL Editor	Severity:	S2 (Serious)
Version:	6.3.10	OS:	Windows (Microsoft Windows 10 Enterprise LTSC 2019)
Assigned to:		CPU Architecture:	Any
Tags:	WBBugReporter

Description:
----[For better reports, please attach the log file after submitting. You can find it in C:\Users\ayfchan\AppData\Roaming\MySQL\Workbench\log\wb.log]

In MySQL Workbench 8.0
Execute SQL:
SET @code := '21CL-ST-06';
select * FROM table where code in (@code ) order by sDate;

Error: illegal mix of collations (utf8mb4_unicode_ci implicit) and (utf8mb4_general_ci implicit)

Need to change to SET @code := '21CL-ST-06' COLLATE utf8mb4_unicode_ci; and work. 

Go back to MySQL Workbench 6.3, 
"SET @code := '21CL-ST-06';" work, no need to add "COLLATE utf8mb4_unicode_ci". 

Column: `code` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL,

How to repeat:
In MySQL Workbench 8.0,
Execute SQL:
SET @xxx := '21CL-ST-06';
select * FROM table where xxx in (@xxx) order by sDate;
-> Error.

In MySQL Workbench 6.3, 
Execute SQL:
SET @xxx := '21CL-ST-06';
select * FROM table where xxx in (@xxx) order by sDate;
-> No Error.

Hello,

Thank you for the bug report.
I tried to reproduce your issue on windows 10 using workbench 8.0.26 but I am not seeing any issues at my end.

Regards,
Ashwini Patil

Possible to add default COLLATE utf8mb4_unicode_ci ?