[MDEV-15957] Unexpected "Data too long" when doing CREATE..SELECT with stored functions Created: 2018-04-21  Updated: 2018-04-23  Resolved: 2018-04-23

Status: Closed
Project: MariaDB Server
Component/s: Data types, Stored routines
Affects Version/s: 10.0, 10.1, 10.2, 10.3, 10.4
Fix Version/s: 10.3.7

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Blocks
blocks MDEV-4912 Data type plugin API version 1 Closed
Relates
relates to MDEV-13232 Assertion `(&(&share->intern_lock)->m... Closed

 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).

 Comments   
Comment by Alexander Barkov [ 2018-04-22 ]

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.

Comment by Alexander Barkov [ 2018-04-22 ]

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.

Generated at Thu Feb 08 08:25:17 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.