Details
-
Bug
-
Status: Open (View Workflow)
-
Minor
-
Resolution: Unresolved
-
10.5, 10.6, 10.11, 11.1(EOL), 10.4(EOL), 10.9(EOL), 10.10(EOL), 11.0(EOL)
-
None
Description
CREATE TABLE t1 (a INT INVISIBLE, b INT CHECK (b = a)); |
CREATE TABLE t2 AS SELECT * FROM t1; |
|
10.4 f5dceafd |
mysqltest: At line 2: query 'CREATE TABLE t2 AS SELECT * FROM t1' failed: 1054: Unknown column 'a' in 'CHECK' |
The chain of "thought" is easy to guess – SELECT only selects column `b`, because column `a` is invisible, but CREATE tries to preserve the check constraint on column `b`, and it turns out it refers to a column which wasn't selected.
It is still strange however and does not look right.
Maybe the easiest would be not to try to preserve the CHECK at all? CREATE .. SELECT doesn't copy table checks from the original table, so maybe it doesn't have to copy column checks.
|
Column check |
CREATE TABLE t1 (a INT, b INT CHECK (b = a)); |
CREATE TABLE t2 AS SELECT * FROM t1; |
SHOW CREATE TABLE t2; |
Table Create Table |
t2 CREATE TABLE `t2` ( |
`a` int(11) DEFAULT NULL, |
`b` int(11) DEFAULT NULL CHECK (`b` = `a`) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |
|
Table check (note the comma in t1 definition) |
CREATE TABLE t1 (a INT, b INT, CHECK (b = a)); |
CREATE TABLE t2 AS SELECT * FROM t1; |
SHOW CREATE TABLE t2; |
Table Create Table |
t2 CREATE TABLE `t2` ( |
`a` int(11) DEFAULT NULL, |
`b` int(11) DEFAULT NULL |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci |