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

Explicit CAST(CHAR(N)) erroneously escalates warnings to errors in STRICT_ALL_TABLES

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL)
    • 10.3.3
    • Data types
    • None

    Description

      According to the SQL standard,

      • if CAST from numeric data types to [VAR]CHAR truncates data, an error is issue.
      • however, when CAST from [VAR]CHAR to [VAR]CHAR truncates the data, a warning (non an error) is issued !

      11) If TD is fixed-length character string, then let LTD be the length in characters of TD.

      a) If SD is exact numeric, then:
      2) If the length in characters LY of Y is equal to LTD, then TV is Y.
      3) If the length in characters LY of Y is less than LTD, then TV is Y extended on the right by
      LTD–LY <space>s.
      4) Otherwise, an exception condition is raised: data exception — string data, right truncation
      d) If SD is fixed-length character string, variable-length character string, or large object character string, then
      ii) If the length in characters of SV is larger than LTD, then TV is the first LTD characters of SV.
      If any of the remaining characters of SV are non-<space> characters, then a completion condition
      is raised: warning — string data, right truncation.

      12) If TD is variable-length character string or large object character string, then let MLTD be the maximum length in characters of TD.

      a) If SD is exact numeric, then:
      2) If the length in characters LY of Y is less than or equal to MLTD, then TV is Y.
      3) Otherwise, an exception condition is raised: data exception — string data, right truncation.

      d) If SD is fixed-length character string, variable-length character string, or large object character string, then
      ii) If the length in characters of SV is larger than MLTD, then TV is the first MLTD characters of
      SV. If any of the remaining characters of SV are non-<space> characters, then a completion
      condition is raised: warning — string data, right truncation.

      In sql_mode=STRICT_ALL_TABLES this is not always true.

      This script correctly returns a warning as expected:

      SET sql_mode=STRICT_ALL_TABLES;
      SELECT CAST('xxx' AS CHAR(1));
      

      However, this script rejects the INSERT and returns an error instead of the warning:

      SET sql_mode=STRICT_ALL_TABLES;
      CREATE OR REPLACE TABLE t1 (a VARCHAR(1));
      INSERT INTO t1 VALUES (CAST('abc' AS CHAR(1)));
      

      ERROR 1292 (22007): Truncated incorrect CHAR(1) value: 'abc'
      

      The same error is returned on UPDATE:

      SET sql_mode=STRICT_ALL_TABLES;
      CREATE OR REPLACE TABLE t1 (a VARCHAR(3));
      INSERT INTO t1 VALUES ('xxx');
      UPDATE t1 SET a=CAST(a AS CHAR(1));
      

      This script also rejects the assignment and returns the same error:

      SET sql_mode=STRICT_ALL_TABLES;
      DELIMITER $$
      BEGIN NOT ATOMIC
        DECLARE a VARCHAR(30);
        SET a=CAST('xxx' AS CHAR(1));
      END;
      $$
      DELIMITER ;
      

      ERROR 1292 (22007): Truncated incorrect CHAR(1) value: 'xxx'
      

      The problem happens because in sql_mode=STRICT_ALL_TABLES the server automatically escalates warnings to errors on INSERT and UPDATE, as well as on assignments to SP variables.

      There should not be errors on INSERT, UPDATE, SP assignments in the above examples.
      The explicit CAST from [VAR]CHAR to [VAR]CHAR should only issue warnings on truncation (without escalation to errors) and then send good data further to the destination (e.g. INSERT, UPDATE, assignments).

      Note, escalating from warnings to errors for CAST from numeric or temporal to [VAR]CHAR is OK.

      Attachments

        Activity

          People

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