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