Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
12.3, 13.0, 13.1
-
Can result in unexpected behaviour
Description
Cursors on prepared statements: OPEN ... USING does not normalize parameter collation, causing "Illegal mix of collations" errors
SET NAMES utf8mb4;
|
|
|
SET @v1 = _latin1 0xE9;
|
SET @v2 = _koi8r 0xE9;
|
|
|
PREPARE stmt2 FROM 'SELECT (? = ?) AS cmp_result';
|
EXECUTE stmt2 USING @v1, @v2;
|
-- succeeds when executing PS directly, both values converted to connection charset first
|
|
|
DELIMITER $$;
|
CREATE PROCEDURE p3()
|
BEGIN
|
DECLARE v_cmp INT;
|
DECLARE c CURSOR FOR stmt2;
|
OPEN c USING @v1, @v2;
|
FETCH c INTO v_cmp;
|
CLOSE c;
|
SELECT v_cmp;
|
END;
|
$$
|
DELIMITER ;$$
|
|
|
CALL p3;
|
-- ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (koi8r_general_ci,COERCIBLE) for operation '='
|
-- Expected: should succeed identically to the EXECUTE ... USING
|
CLI Output
12.3.3-opt>SET NAMES utf8mb4; |
Query OK, 0 rows affected (0.000 sec) |
|
|
12.3.3-opt>SET @v1 = _latin1 0xE9; |
Query OK, 0 rows affected (0.000 sec) |
|
|
12.3.3-opt>SET @v2 = _koi8r 0xE9; |
Query OK, 0 rows affected (0.000 sec) |
|
|
12.3.3-opt>PREPARE stmt2 FROM 'SELECT (? = ?) AS cmp_result'; |
Query OK, 0 rows affected (0.000 sec) |
Statement prepared
|
|
|
12.3.3-opt>EXECUTE stmt2 USING @v1, @v2; -- succeeds: cmp_result = 0 |
+------------+ |
| cmp_result |
|
+------------+ |
| 0 |
|
+------------+ |
1 row in set (0.000 sec) |
|
|
12.3.3-opt>
|
12.3.3-opt>DELIMITER $$
|
12.3.3-opt>CREATE PROCEDURE p3() |
-> BEGIN |
-> DECLARE v_cmp INT; |
-> DECLARE c CURSOR FOR stmt2; |
-> OPEN c USING @v1, @v2; |
-> FETCH c INTO v_cmp; |
-> CLOSE c; |
-> SELECT v_cmp; |
-> END; |
-> $$
|
Query OK, 0 rows affected (0.002 sec) |
|
|
12.3.3-opt>DELIMITER ;
|
12.3.3-opt>CALL p3;
|
ERROR 1267 (HY000): Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (koi8r_general_ci,COERCIBLE) for operation '=' |
12.3.3-opt>
|
Attachments
Issue Links
- is caused by
-
MDEV-33830 Support for cursors on prepared statements
-
- Closed
-