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

Illegal mix of collation, when comparing column with CASE expression

Details

    • Bug
    • Status: Closed (View Workflow)
    • Minor
    • Resolution: Fixed
    • 5.5(EOL)
    • 5.5.49
    • Character Sets
    • None

    Description

      An unexpected 'Illegal mix of collations' pops up when comparing result of CASE expression with a field (CASE expression returns either 'Y' or NULL). According to Bar, this
      should not happen.

      To reproduce :

      MariaDB [test]> CREATE TABLE `datatypetestm` (CCHAR1 char(1));
      Query OK, 0 rows affected (0.00 sec)

      MariaDB [test]> select CCHAR1 from datatypetestm where CASE CCHAR1 WHEN 'aaaa' THEN 'Y' WHEN 'aaaa' THEN 'Y' ELSE NULL END <> CCHAR1;
      ERROR 1267 (HY000): Illegal mix of collations (cp850_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation '<>'

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          wlad Vladislav Vaintroub created issue -
          bar Alexander Barkov added a comment -

          The full script which demonstrates the problem:

          SET NAMES cp850;
          		 
          CREATE TABLE t1 (a CHAR(1) CHARACTER SET latin1);
          		 
          SELECT a FROM t1 WHERE CASE a WHEN 'aaaa' THEN 'Y' WHEN 'aaaa' THEN 'Y' ELSE NULL END <> a;

          bar Alexander Barkov added a comment - The full script which demonstrates the problem: SET NAMES cp850; CREATE TABLE t1 (a CHAR (1) CHARACTER SET latin1); SELECT a FROM t1 WHERE CASE a WHEN 'aaaa' THEN 'Y' WHEN 'aaaa' THEN 'Y' ELSE NULL END <> a;
          bar Alexander Barkov made changes -
          Field Original Value New Value
          Status Open [ 1 ] In Progress [ 3 ]
          bar Alexander Barkov made changes -
          Component/s Character Sets [ 10801 ]
          Fix Version/s 5.5.49 [ 21600 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          elenst Elena Stepanova made changes -
          Affects Version/s 5.5 [ 15800 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 74742 ] MariaDB v4 [ 150294 ]

          People

            bar Alexander Barkov
            wlad Vladislav Vaintroub
            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.