Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2, 10.3, 10.4, 10.5, 10.6
-
None
Description
I could not determine whether it is the expected behavior, neither from the standard nor from comparison with MySQL.
10.2 676987c4 |
CREATE TABLE t1 (a INT CHECK (a > 0)); |
CREATE TABLE t2 AS SELECT a FROM t1; |
SHOW CREATE TABLE t2; |
Table Create Table |
t2 CREATE TABLE `t2` ( |
`a` int(11) DEFAULT NULL CHECK (`a` > 0) |
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
So, CREATE .. SELECT has copied the CHECK constraint from the source table's column.
It seems unexpected, but innocent, until the advanced column constraint capabilities are used. Particularly, in MariaDB column constraints can refer to each other (in MySQL they can't):
CREATE TABLE t1 (a INT, b INT CHECK (b > a)); |
CREATE TABLE t2 AS SELECT b FROM t1; |
Again, CREATE .. SELECT attempts to copy the constraint on b column, only now it fails with a very confusing error:
MariaDB [test]> CREATE TABLE t1 (a INT, b INT CHECK (b > a)); |
Query OK, 0 rows affected (0.336 sec) |
 |
MariaDB [test]> CREATE TABLE t2 AS SELECT b FROM t1; |
ERROR 1054 (42S22): Unknown column 'a' in 'CHECK' |
If it is intended, it should probably be documented.