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

Redundant Item_func_conv_charset on WHERE utf8mb4_field=utf8mb3_field

Details

    • Bug
    • Status: Open (View Workflow)
    • Minor
    • Resolution: Unresolved
    • 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL)
    • 10.11
    • Character Sets
    • None

    Description

      DROP TABLE IF EXISTS t1;
      		 
      CREATE TABLE t1 (a TEXT CHARACTER SET utf8mb4, b TEXT CHARACTER SET utf8mb3);
      		 
      INSERT INTO t1 VALUES ('a','a');
      		 
      EXPLAIN EXTENDED SELECT * FROM t1 WHERE a=b;
      		 
      SHOW WARNINGS;


      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Level | Code | Message                                                                                                                              |
      		 
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------+
      		 
      | Note  | 1003 | select `test`.`t1`.`a` AS `a`,`test`.`t1`.`b` AS `b` from `test`.`t1` where `test`.`t1`.`a` = convert(`test`.`t1`.`b` using utf8mb4) |
      		 
      +-------+------+--------------------------------------------------------------------------------------------------------------------------------------+

      The Item_func_conv_charset behind CONVERT() is redundant here. utf8mb3 can be reinterpreted to utf8mb4 without conversion.

      Note, we even allow online alter when converting an utf8mb3 column to utf8mb4 column with the same data type and size. The same optimization can be applied here.

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-15564 Avoid table rebuild in ALTER TABLE on collation or charset changes

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19284 INSTANT ALTER with ucs2-to-utf16 conversion produces bad data

          • Major - Major loss of function.
          • Closed

          Bug - A problem which impairs or prevents the functions of the product. MDEV-19285 INSTANT ALTER from ascii_general_ci to latin1_general_ci produces corrupt data

          • Major - Major loss of function.
          • Closed

          Activity

            There are no comments yet on this issue.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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