Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
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
-
MDEV-4912 Data type plugin API version 1
-
- Closed
-
- relates to
-
-
- Closed
-