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

Illegal mix of collations with a view comparing a field to a binary constant

Details

    Description

      This script:

      SET NAMES latin1;
      		 
      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a TEXT CHARACTER SET gbk);
      		 
      INSERT INTO t1 VALUES (0xEE5D);
      		 
      SELECT a<> 0xEE5D AS a FROM t1;

      correctly returns one row:

      +------+
      		 
      | a    |
      		 
      +------+
      		 
      |    0 |
      		 
      +------+

      Now if I put the same SELECT into a VIEW and query it:

      DROP VIEW IF EXISTS v1;
      		 
      CREATE VIEW v1 AS SELECT a<> 0xEE5D AS a FROM t1;
      		 
      SELECT * FROM v1;

      it returns an error:

      ERROR 1267 (HY000): Illegal mix of collations (gbk_chinese_ci,IMPLICIT) and (latin1_swedish_ci,COERCIBLE) for operation '<>'

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          bar Alexander Barkov added a comment -

          The problem happens because the view query is written to the frm file as:

          query=select `test`.`t1`.`a` <> \'î]\' AS `a` from `test`.`t1`

          Notice, the hex hybrid literal 0xEE5D was replaced to a quoted character string literal, so it's misinterpreted during the view open time on SELECT.

          bar Alexander Barkov added a comment - The problem happens because the view query is written to the frm file as: query= select `test`.`t1`.`a` <> \'î]\' AS `a` from `test`.`t1` Notice, the hex hybrid literal 0xEE5D was replaced to a quoted character string literal, so it's misinterpreted during the view open time on SELECT.
          bar Alexander Barkov added a comment -

          Not repeatable in 5.5, as it uses a slightly different code in agg_item_set_converter to detect if conversion is needed:
          String::needs_conversion() vs Item::safe_charset_converter().

          bar Alexander Barkov added a comment - Not repeatable in 5.5, as it uses a slightly different code in agg_item_set_converter to detect if conversion is needed: String::needs_conversion() vs Item::safe_charset_converter().

          People

            bar Alexander Barkov
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            2 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.