[MDEV-31610] Column CHECK referring invisible column prevents CREATE .. SELECT Created: 2023-07-03  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Data Definition - Create Table
Affects Version/s: 10.4, 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: 10.4, 10.5, 10.6, 10.11, 11.0, 11.1

Type: Bug Priority: Minor
Reporter: Elena Stepanova Assignee: Oleksandr Byelkin
Resolution: Unresolved Votes: 1
Labels: 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



 Comments   
Comment by Sergei Golubchik [ 2023-07-03 ]

Generally, in CREATE TABLE ... SELECT, the SELECT part produces a result set and CREATE TABLE should create a table based on that. Default column values, indexes, constraints — they aren't part of the result set, so shouldn't be transferred over.

Historically, some of that was transferred over, though, and some — rather inconsistently — wasn't.

Generated at Thu Feb 08 10:25:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.