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

Unexpected "Data too long" when doing CREATE..SELECT with stored functions

    Details

      Description

      I create a table with an INT(3) column, and a stored function returning an INT(3)) result:

      CREATE OR REPLACE TABLE t1 (a INT(3));
      INSERT INTO t1 VALUES (-999);
      CREATE OR REPLACE FUNCTION f1(a INT(3)) RETURNS INT(3) RETURN a;
      

      Now I try to create a new table in a strict mode:

      SET sql_mode=STRICT_ALL_TABLES;
      CREATE OR REPLACE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(COALESCE(a)) AS c2, CONCAT(f1(a)) AS c3 FROM t1;
      

      ERROR 1406 (22001): Data too long for column 'c3' at row 1
      

      Oops. It failed. Retrying with a lose mode:

      SET sql_mode='';
      CREATE OR REPLACE TABLE t2 AS SELECT CONCAT(a) AS c1, CONCAT(COALESCE(a)) AS c2, CONCAT(f1(a)) AS c3 FROM t1;
      

      Query OK, 1 row affected, 1 warning (0.02 sec)
      Records: 1  Duplicates: 0  Warnings: 1
      

      It created the table, with a warning.

      Checking the structure:

      DESC t2;
      

      +-------+-------------+------+-----+---------+-------+
      | Field | Type        | Null | Key | Default | Extra |
      +-------+-------------+------+-----+---------+-------+
      | c1    | varchar(11) | YES  |     | NULL    |       |
      | c2    | varchar(11) | YES  |     | NULL    |       |
      | c3    | varchar(3)  | YES  |     | NULL    |       |
      +-------+-------------+------+-----+---------+-------+
      

      Column types are different. This is not correct. All data types were INT(3) in the above script. There are no any reasons why the data type for c3 should be shorter.

      It seems CREATE TABLE erroneously used the user-specified display width (which is 3) when creating the column corresponding to CONCAT(f1(a)), while it correctly used the maximum possible width (which is 11) for CONCAT(a) and CONCAT(COALESCE(a)).

      It should create varchar(11) for all three columns. The manual says:

      The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.

      The same problem is repeatable with stored aggregate functions:

      CREATE OR REPLACE TABLE t1 (a INT);
      INSERT INTO t1 VALUES (1),(2),(3);
       
      DELIMITER $$
      CREATE OR REPLACE AGGREGATE FUNCTION f1(x INT) RETURNS INT(3)
      BEGIN
        DECLARE res INT DEFAULT 0;
        DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN res-200;
        LOOP
          FETCH GROUP NEXT ROW;
          SET res= res + x;
        END LOOP;
        RETURN res;
      END;
      $$
      DELIMITER ;
      

      Now if I try to create a table from the same query, it fails:

      SET sql_mode=STRICT_ALL_TABLES;
      CREATE OR REPLACE TABLE t2 AS SELECT CONCAT(f1(a)) FROM t1;
      

      ERROR 1406 (22001): Data too long for column 'CONCAT(f1(a))' at row 4
      

      Retrying with a loose sql_mode:

      SET sql_mode='';
      CREATE OR REPLACE TABLE t2 AS SELECT CONCAT(f1(a)) FROM t1;
      DESC t2;
      

      +---------------+------------+------+-----+---------+-------+
      | Field         | Type       | Null | Key | Default | Extra |
      +---------------+------------+------+-----+---------+-------+
      | CONCAT(f1(a)) | varchar(3) | YES  |     | NULL    |       |
      +---------------+------------+------+-----+---------+-------+
      

      The expected data type is varchar(11).

        Attachments

          Issue Links

            Activity

              People

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

                Dates

                • Created:
                  Updated:
                  Resolved: