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

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

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
    • 10.3.7
    • Data types, Stored routines
    • None

    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

          blocks

          Epic - Created by Jira Software - do not edit or delete. Issue type for a big user story that needs to be broken down. MDEV-4912 Data type plugin API version 1

          • Minor - Minor loss of function, or other problem where easy workaround is present.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-13232 Assertion `(&(&share->intern_lock)->m_mutex)->count > 0 && pthread_equal(pthread_self(), (&(&share->intern_lock)->m_mutex)->thread)' failed in _ma_state_info_write

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

          Activity

            Ascending order - Click to sort in descending order
            bar Alexander Barkov added a comment - - edited

            A similar problem happens with TEXT variants:

            CREATE OR REPLACE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8 RETURN '';
            		 
            CREATE OR REPLACE TABLE t1 AS SELECT f1() AS c1, CONCAT(f1()) AS c2;
            		 
            DESC t1;


            +-------+-------------+------+-----+---------+-------+
            		 
            | Field | Type        | Null | Key | Default | Extra |
            		 
            +-------+-------------+------+-----+---------+-------+
            		 
            | c1    | longtext    | YES  |     | NULL    |       |
            		 
            | c2    | varchar(85) | YES  |     | NULL    |       |
            		 
            +-------+-------------+------+-----+---------+-------+

            The data type for c1 is too long.
            The data type for c2 is too short.

            bar Alexander Barkov added a comment - - edited A similar problem happens with TEXT variants: CREATE OR REPLACE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8 RETURN '' ; CREATE OR REPLACE TABLE t1 AS SELECT f1() AS c1, CONCAT(f1()) AS c2; DESC t1; +-------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+-------------+------+-----+---------+-------+ | c1 | longtext | YES | | NULL | | | c2 | varchar(85) | YES | | NULL | | +-------+-------------+------+-----+---------+-------+ The data type for c1 is too long. The data type for c2 is too short.
            bar Alexander Barkov added a comment -

            A similar problem happens with custom aggregate function returning TEXT variants:

            DELIMITER $$
            		 
            CREATE OR REPLACE AGGREGATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8
            		 
            BEGIN
            		 
              DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '';
            		 
              LOOP
            		 
                FETCH GROUP NEXT ROW;
            		 
              END LOOP;
            		 
              RETURN '';
            		 
            END;
            		 
            $$
            		 
            DELIMITER ;
            		 
            CREATE OR REPLACE TABLE t1 AS SELECT f1() AS c1;
            		 
            DESC t1;


            +-------+------+------+-----+---------+-------+
            		 
            | Field | Type | Null | Key | Default | Extra |
            		 
            +-------+------+------+-----+---------+-------+
            		 
            | c1    | text | YES  |     | NULL    |       |
            		 
            +-------+------+------+-----+---------+-------+

            The data type text is wrong. It should be tinytext.

            bar Alexander Barkov added a comment - A similar problem happens with custom aggregate function returning TEXT variants: DELIMITER $$ CREATE OR REPLACE AGGREGATE FUNCTION f1() RETURNS TINYTEXT CHARACTER SET utf8 BEGIN DECLARE CONTINUE HANDLER FOR NOT FOUND RETURN '' ; LOOP FETCH GROUP NEXT ROW; END LOOP; RETURN '' ; END ; $$ DELIMITER ; CREATE OR REPLACE TABLE t1 AS SELECT f1() AS c1; DESC t1; +-------+------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------+------+------+-----+---------+-------+ | c1 | text | YES | | NULL | | +-------+------+------+-----+---------+-------+ The data type text is wrong. It should be tinytext .

            People

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