Bug #118237 Inconsistent results when using prepared statement or session variables

• Submitted: 21 May 8:44
• Modified: 21 May 10:48
• Reporter: Malt Chen
• Status: Analyzing
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 9.2
• OS: Any
• Assigned to: MySQL Verification Team
• CPU Architecture: Any

[21 May 8:44] Malt Chen

Description:
The same SQL statement will get different results when 1. executed directly 2. using session variable 3. using prepared statement 4. using procedure. Item has different Derivation, which further leads to different collations when comparing conditions. Is this behavior reasonable or a bug?

How to repeat:
Run the following SQL statement to reproduce the behavior here:

set names utf8mb4;
create table t1(name char(20));
insert into t1 values ('john', 'John');

1. executed directly
select * from t1 where name = _binary 'john'; --get 2 rows because of DERIVATION_COERCIBLE
select * from t1 where name = binary 'john'; --get 1 row because of DERIVATION_IMPLICIT

2. using session variable
set @var1 = _binary 'john';
set @var2 = binary 'john';
select * from t1 where name = @var1; --get 1 row, inconsistent
select * from t1 where name = @var2; --get 1 row

3. using prepared statement
set @var1 = _binary 'john';
set @var2 = binary 'john';
prepare stmt from "select * from t1 where name = ?";
execute stmt using @var1; --get 2 rows
execute stmt using @var2; --get 2 rows, inconsistent

4. using procedure
delimiter //
create procedure test_proc(var char(20))
begin
select * from t1 where name = var;
end//
delimiter ;
call test_proc(_binary 'john'); --get 2 rows
call test_proc(binary 'john');  --get 2 rows, inconsistent
 

Suggested fix:
Should Item_func_get_user_var, Item_func_set_user_var, Item_param, Item_splocal set their collation from the source Item ?