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

CREATE .. SELECT attempts to copy column CHECK constraints from selected columns (and fails sometimes)

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            serg Sergei Golubchik
            Reporter:
            elenst Elena Stepanova
            Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

              Dates

              Created:
              Updated:

                Git Integration