Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-31610

Column CHECK referring invisible column prevents CREATE .. SELECT

    XMLWordPrintable

Details

    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
      

      Attachments

        Activity

          People

            sanja Oleksandr Byelkin
            elenst Elena Stepanova
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.