Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5(EOL), 10.0(EOL), 10.1(EOL), 10.2(EOL), 10.3(EOL), 10.4(EOL)
-
None
Description
SPACE
CREATE OR REPLACE TABLE t1 (i BIGINT); |
INSERT INTO t1 VALUES (42); |
CREATE OR REPLACE TABLE t2 AS SELECT |
SPACE(0) AS c0, |
SPACE(-1) AS c1 |
FROM t1; |
SHOW CREATE TABLE t2; |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c0` char(0) CHARACTER SET utf8 DEFAULT NULL,
|
`c1` longtext CHARACTER SET utf8 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
The data type for c1 is wrong. It should be char(0), like for c0.
RPAD
The same problem is repeatable with RPAD:
CREATE OR REPLACE TABLE t1 (i BIGINT); |
INSERT INTO t1 VALUES (42); |
CREATE OR REPLACE TABLE t2 AS SELECT |
RPAD(i,0,'a') AS c0, |
RPAD(i,-1,'a') AS c1 |
FROM t1; |
SHOW CREATE TABLE t2; |
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c0` char(0) CHARACTER SET utf8 DEFAULT NULL,
|
`c1` longtext CHARACTER SET utf8 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
|
LEFT
A similar problem is repeatable with LEFT(). It creates a too small column in case of a huge positive argument, which causes an error or data loss (depending in strict mode):
CREATE OR REPLACE TABLE t1 (i BIGINT); |
INSERT INTO t1 VALUES (42); |
CREATE OR REPLACE TABLE t2 AS SELECT |
LEFT(i,0) AS c0, |
LEFT(i,18446744073709551615) AS c1 |
FROM t1; |
ERROR 1406 (22001): Data too long for column 'c1' at row 1
|
The error is not expected.
Let's recreate the table using LIMIT 0 to avoid the error:
CREATE OR REPLACE TABLE t2 AS SELECT |
LEFT(i,0) AS c0, |
LEFT(i,18446744073709551615) AS c1 |
FROM t1 LIMIT 0; |
SHOW CREATE TABLE t2; |
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
|
| t2 | CREATE TABLE `t2` (
|
`c0` char(0) CHARACTER SET utf8 DEFAULT NULL,
|
`c1` char(0) CHARACTER SET utf8 DEFAULT NULL
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+-----------------------------------------------------------------------------------------------------------------------------------------------------------+
|
Notice, the data type for c1 is wrong. It should be approximately char(20).
Attachments
Issue Links
- relates to
-
-
- Closed
-