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

JSON_TABLE: Illegal mix of collations upon executing PS once, or SP/function twice

    XMLWordPrintable

Details

    Description

      SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
       
      PREPARE stmt FROM "
      SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
      ";
      EXECUTE stmt;
      

      The plain SELECT works and returns a value:

      MariaDB [test]> SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
      +------+
      | a    |
      +------+
      | 2    |
      +------+
      1 row in set (0.001 sec)
      

      The prepared statement fails:

      bb-10.6-mdev17399-hf 160bd1691

      MariaDB [test]> PREPARE stmt FROM "
          "> SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
          "> ";
      Query OK, 0 rows affected (0.001 sec)
      Statement prepared
       
      MariaDB [test]> EXECUTE stmt;
      ERROR 1267 (HY000): Illegal mix of collations (koi8u_general_ci,IMPLICIT) and (eucjpms_japanese_ci,IMPLICIT) for operation '='
      

      On MySQL both work.

      Also reproducible with EXECUTE IMMEDIATE and upon the 2nd execution of a stored procedure/function:

      SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
       
      CREATE OR REPLACE PROCEDURE pr()
      SELECT * FROM JSON_TABLE (CONVERT('[1,2]' USING koi8u), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt1 NATURAL JOIN JSON_TABLE (CONVERT('[2,3]' USING eucjpms), '$[*]' COLUMNS(a CHAR(8) PATH '$')) AS jt2;
       
      CALL pr();
      CALL pr();
      

      MariaDB [test]> CALL pr();
      +------+
      | a    |
      +------+
      | 2    |
      +------+
      1 row in set (0.002 sec)
       
      Query OK, 0 rows affected (0.002 sec)
       
      MariaDB [test]> CALL pr();
      ERROR 1267 (HY000): Illegal mix of collations (koi8u_general_ci,IMPLICIT) and (eucjpms_japanese_ci,IMPLICIT) for operation '='
      

      (same with a function)

      Attachments

        Issue Links

          Activity

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

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