[MDEV-25061] CREATE .. SELECT attempts to copy column CHECK constraints from selected columns (and fails sometimes) Created: 2021-03-04  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.4, 10.5

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Sergei Golubchik
Resolution: Unresolved Votes: 0
Labels: 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.

 Comments   
Comment by Elena Stepanova [ 2021-03-25 ]

Here is another example (maybe more realistic) of how CREATE .. SELECT fails because of CHECK constraint on a source table.

MariaDB [test]> create table t1 (a int check(a>0));
 
Query OK, 0 rows affected (0.038 sec)
 
 
 
MariaDB [test]> create table t2 as select a as b from t1;
 
ERROR 1054 (42S22): Unknown column 'a' in 'CHECK'
 
MariaDB [test]> show warnings;
 
+-------+------+---------------------------------------------------+
 
| Level | Code | Message                                           |
 
+-------+------+---------------------------------------------------+
 
| Error | 1054 | Unknown column 'a' in 'CHECK'                     |
 
| Error | 4027 | Got an error evaluating stored expression `a` > 0 |
 
+-------+------+---------------------------------------------------+
 
2 rows in set (0.000 sec)

So, upon CREATE .. SELECT we give a new name to the selected column, but the check constraint is copied as is, with the old name, thus causing the problem.

Generated at Thu Feb 08 09:34:50 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.