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

Selecting INT column with COLLATE utf8mb4_general_ci throws an error

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Critical
    • Resolution: Fixed
    • 5.5, 10.1, 10.5.4, 10.5.6, 10.2, 10.3, 10.4, 10.5, 10.6
    • 10.5.14, 10.6.6, 10.7.2
    • Character Sets
    • None
    • MariaDB Community 10.5 Docker Container

    Description

      Review below walk-through for how to reproduce the issue and the error messages involved.

      Note it is not necessary for this to succeed- there should not be a reason to issue a COLLATE against an integer column like this. However, this behavior does not seem to be documented, so at a minimum if this is intended behavior, this should be more apparent.

      If this can be made to work rather than error, this will ease how much code refactoring some users must perform when switching from MySQL/Oracle to MariaDB.

      MariaDB [(none)]> SELECT VARIABLE_NAME, SESSION_VALUE
          -> FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE
          -> VARIABLE_NAME LIKE 'character_set_c%' OR
          -> VARIABLE_NAME LIKE 'character_set_re%' OR
          -> VARIABLE_NAME LIKE 'collation_c%';
      +--------------------------+-------------------+
      | VARIABLE_NAME            | SESSION_VALUE     |
      +--------------------------+-------------------+
      | CHARACTER_SET_RESULTS    | latin1            |
      | CHARACTER_SET_CONNECTION | latin1            |
      | CHARACTER_SET_CLIENT     | latin1            |
      | COLLATION_CONNECTION     | latin1_swedish_ci |
      +--------------------------+-------------------+
      4 rows in set (0.001 sec)
       
      MariaDB [(none)]> select 1 collate utf8mb4_general_ci;
      ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'
      MariaDB [(none)]> set names utf8mb4;
      Query OK, 0 rows affected (0.000 sec)
       
      MariaDB [(none)]> SELECT VARIABLE_NAME, SESSION_VALUE FROM INFORMATION_SCHEMA.SYSTEM_VARIABLES WHERE VARIABLE_NAME LIKE 'character_set_c%' OR VARIABLE_NAME LIKE 'character_set_re%' OR VARIABLE_NAME LIKE 'collation_c%';
      +--------------------------+--------------------+
      | VARIABLE_NAME            | SESSION_VALUE      |
      +--------------------------+--------------------+
      | CHARACTER_SET_RESULTS    | utf8mb4            |
      | CHARACTER_SET_CONNECTION | utf8mb4            |
      | CHARACTER_SET_CLIENT     | utf8mb4            |
      | COLLATION_CONNECTION     | utf8mb4_general_ci |
      +--------------------------+--------------------+
      4 rows in set (0.001 sec)
       
      MariaDB [(none)]> select 1 collate utf8mb4_general_ci;
      ERROR 1253 (42000): COLLATION 'utf8mb4_general_ci' is not valid for CHARACTER SET 'latin1'
      MariaDB [(none)]> select 'TEST' collate utf8mb4_general_ci;
      +-----------------------------------+
      | 'TEST' collate utf8mb4_general_ci |
      +-----------------------------------+
      | TEST                              |
      +-----------------------------------+
      1 row in set (0.000 sec)

      Attachments

        Issue Links

          Activity

            People

              bar Alexander Barkov
              rob.schwyzer@mariadb.com Rob Schwyzer
              Votes:
              0 Vote for this issue
              Watchers:
              6 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.