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

SELECT produces more warnings in non-strict mode than in strict mode

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Minor
    • Resolution: Unresolved
    • Affects Version/s: 10.1, 10.2, 10.3, 10.4, 10.5
    • Fix Version/s: 10.5
    • Component/s: Character Sets
    • Labels:
      None

      Description

      The test case below (data creation and query in question) was extracted from ctype_big5.test.

      The query executed with empty sql_mode produces more warnings than the same query executed with STRICT_TRANS_TABLES. It seems strange.

      The warnings appeared in 10.1, and the same oddity with the number of warnings can be observed there. Earlier versions of MariaDB don't produce warnings at all, and neither does MySQL 5.7.

      SET NAMES utf8;
      SET collation_connection=big5_chinese_ci;
      CREATE TABLE t1 (b VARCHAR(2));
      INSERT INTO t1 VALUES ('0'),('1'),('2'),('3'),('4'),('5'),('6'),('7');
      INSERT INTO t1 VALUES ('8'),('9'),('A'),('B'),('C'),('D'),('E'),('F');
      CREATE TEMPORARY TABLE head AS SELECT concat(b1.b, b2.b) AS head FROM t1 b1, t1 b2;
      CREATE TEMPORARY TABLE tail AS SELECT concat(b1.b, b2.b) AS tail FROM t1 b1, t1 b2;
      DROP TABLE t1;
      CREATE TABLE t1 AS
      SELECT concat(head, tail) AS code, ' ' AS a
      FROM head, tail
      WHERE (head BETWEEN '80' AND 'FF') AND (tail BETWEEN '20' AND 'FF')
      ORDER BY head, tail;
      DROP TEMPORARY TABLE head, tail;
       
      UPDATE IGNORE t1 SET a=unhex(code) ORDER BY code;
      

      set sql_mode='';
      SELECT * FROM t1
      WHERE HEX(CAST(LOWER(a) AS CHAR CHARACTER SET utf8)) <>
      HEX(LOWER(CAST(a AS CHAR CHARACTER SET utf8))) ORDER BY code;
      

      Results on 10.2

      +------+-----+
      | code | a   |
      +------+-----+
      | A2B9 | Ⅰ   |
      | A2BA | Ⅱ   |
      | A2BB | Ⅲ   |
      | A2BC | Ⅳ   |
      | A2BD | Ⅴ   |
      | A2BE | Ⅵ   |
      | A2BF | Ⅶ   |
      | A2C0 | Ⅷ   |
      | A2C1 | Ⅸ   |
      | A2C2 | Ⅹ   |
      +------+-----+
      10 rows in set, 512 warnings (0.45 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+--------------------------------------------------+
      | Level   | Code | Message                                          |
      +---------+------+--------------------------------------------------+
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C0 to 'utf8' |
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C0 to 'utf8' |
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C1 to 'utf8' |
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C1 to 'utf8' |
      ...
      

      set sql_mode='STRICT_TRANS_TABLES';
      SELECT * FROM t1
      WHERE HEX(CAST(LOWER(a) AS CHAR CHARACTER SET utf8)) <>
      HEX(LOWER(CAST(a AS CHAR CHARACTER SET utf8))) ORDER BY code;
      

      Results on 10.2

      +------+-----+
      | code | a   |
      +------+-----+
      | A2B9 | Ⅰ   |
      | A2BA | Ⅱ   |
      | A2BB | Ⅲ   |
      | A2BC | Ⅳ   |
      | A2BD | Ⅴ   |
      | A2BE | Ⅵ   |
      | A2BF | Ⅶ   |
      | A2C0 | Ⅷ   |
      | A2C1 | Ⅸ   |
      | A2C2 | Ⅹ   |
      +------+-----+
      10 rows in set, 256 warnings (0.38 sec)
       
      MariaDB [test]> SHOW WARNINGS;
      +---------+------+--------------------------------------------------+
      | Level   | Code | Message                                          |
      +---------+------+--------------------------------------------------+
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C0 to 'utf8' |
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C1 to 'utf8' |
      | Warning | 1977 | Cannot convert 'big5' character 0xA3C2 to 'utf8' |
      ...
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                bar Alexander Barkov
                Reporter:
                elenst Elena Stepanova
              • Votes:
                0 Vote for this issue
                Watchers:
                1 Start watching this issue

                Dates

                • Created:
                  Updated: