MySQL Bugs: #117045: In function, binay cmp does not effect

Bug #117045	In function, binay cmp does not effect
Submitted:	25 Dec 2024 9:49	Modified:	2 Jan 9:07
Reporter:	ximin liang (OCA)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: DML	Severity:	S3 (Non-critical)
Version:	9.1.0, 8.0.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hello MySQL team:
  Here is a problem about implicit binary cmp about function, see detail below.

How to repeat:
Here is example: 

create table t1(c1 varchar(10)) CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into t1 values ('a');

For the reason that c1 is case insensitive, sql returns result:
select c1 from t1 where c1 = 'A';
+------+
| c1   |
+------+
| a    |
+------+

such sql triggers implicit bin cmp:

mysql> select c1 from t1 where c1 = binary 'A';
Empty set, 1 warning (0.00 sec)

in function:

DELIMITER //
CREATE FUNCTION f1(p1 VARCHAR(100))
RETURNS varchar(255) DETERMINISTIC
BEGIN
DECLARE c1_result varchar(100) default '';
SELECT c1 INTO c1_result FROM t1 WHERE c1 = p1;
RETURN c1_result;
END//
DELIMITER ;

select f1(binary 'A');
mysql> select f1(binary 'A');
+----------------+
| f1(binary 'A') |
+----------------+
| a              |
+----------------+

Is this a bug?

Hello ximin liang,

Thank you for the report and test case.

regards,
Umesh

Posted by developer:
 
This is not a bug.
The comparison is made based on the expression in the function.
An expression passed in a function evaluation are converted to
the declaration in the function, thus several properties
(like collation) may be ignored.