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

Inconsistent data truncation handling with simple UNION vs CTE

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Open (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
    • Fix Version/s: 10.3
    • Component/s: None
    • Labels:
      None

      Description

      CTE respects sql_mode

      After the patch for MDEV-12325, queries with CTE report truncation as follows:

      • With sql_mode=DEFAULT an error is returned and the query aborts
      • With sql_mode='' only warnings are reported, and the query continues

      SET sql_mode=DEFAULT;
      WITH RECURSIVE temp (c1, c2) AS 
      (SELECT 1, 'init'
        UNION ALL 
       SELECT c1+1, REPEAT('a',c1) FROM temp WHERE c1 < 7)
      SELECT * FROM temp;
      

      ERROR 1406 (22001): Data too long for column 'c2' at row 6
      

      SET sql_mode='';
      WITH RECURSIVE temp (c1, c2) AS 
      (SELECT 1, 'init'
        UNION ALL 
       SELECT c1+1, REPEAT('a',c1) FROM temp WHERE c1 < 7)
      SELECT * FROM temp;
      

      +------+------+
      | c1   | c2   |
      +------+------+
      |    1 | init |
      |    2 | a    |
      |    3 | aa   |
      |    4 | aaa  |
      |    5 | aaaa |
      |    6 | aaaa | <-- truncation
      |    7 | aaaa | <-- truncation
      +------+------+
      7 rows in set, 2 warnings (0.003 sec)
      

      UNION does not respect sql_mode

      However, queries with simple UNION do not respect sql_mode. These two scripts:

      SET sql_mode=DEFAULT;
      SELECT 'a' UNION ALL SELECT REPEAT('b',seq) FROM seq_16777214_to_16777216;
      

      SET sql_mode='';
      SELECT 'a' UNION ALL SELECT REPEAT('b',seq) FROM seq_16777214_to_16777216;
      

      silently truncate the data on the third row (which has the MEDIUMTEXT data type), and return a result set, with neither errors nor warnings.

      This behavior has two problems:

      1. It looks inconsistent - CTE and simple UNION work differently.
      2. Silent truncation in case of simple UNION does not look nice.

      Note, the last two scripts may need settings:

      [client]
      max-allowed-packet=100M
       
      [mysqld]
      max-allowed-packet=100M
      

      to avoid max-allowed-packet failures.

      Possible desired behavior

      Simple UNION could probably be fixed in the same way with CTE to:

      • raise an error in case of strict mode
      • issue warnings in case of loose mode

      Another option is to raise the error for all sql_mode values in both CTE and simple UNION, as there are no real tables involved in these statements. There are only temporary tables involved internally.

      In any cases, silent truncation should not happen for sure.

        Attachments

          Activity

            People

            Assignee:
            sanja Oleksandr Byelkin
            Reporter:
            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.