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

• Submitted: 21 May 2025 8:44
• Modified: 7 Feb 19:11
• Reporter: Malt Chen
• Status: Not a Bug
• Category: MySQL Server: Charsets
• Severity: S3 (Non-critical)
• Version: 9.2
• OS: Any
• CPU Architecture: Any

[21 May 2025 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 ?

[7 Feb 19:11] Roy Lyseng

Thank you for the bug report.
The behavior may seem inconsistent, but there is logic behind
the decisions.

The BINARY qualifier has two purposes:
It sets character set as BINARY, and when used in an expression,
it forces BINARY collation in the operation.

But when BINARY is used in the variable assignment, only the former
purpose is carried over, since the BINARY qualifier is not part of
the expression.

When a prepared statement or a procedure or function is used,
the type of the dynamic parameter and argument is derived based on
the context, which is given by the column. Thus, the type and properties
of the variable do not affect how the collation is derived.
Instead, the variable is coerced into the character set and collation of
the column.

If you really want the comparison to use a binary character set,
you can rewrite the statements as follows with a prepared statement:

  prepare stmt from "select * from t1 where name = cast(? as binary)";

and inside a procedure or function:

  select * from t1 where name = cast(var as binary);