Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-40532

Cursors on prepared statements: OPEN ... USING does not normalize parameter collation

    XMLWordPrintable

Details

    • 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

          Activity

            People

              bar Alexander Barkov
              ramesh Ramesh Sivaraman
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

                Created:
                Updated:

                Time Tracking

                  Estimated:
                  Original Estimate - Not Specified
                  Not Specified
                  Remaining:
                  Remaining Estimate - 0d
                  0d
                  Logged:
                  Time Spent - 0.5h
                  0.5h

                  Git Integration

                    Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.