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

ALTER COLUMN DROP DEFAULT makes SHOW CREATE TABLE diverge from the actual structure

    XMLWordPrintable

Details

    Description

      MariaDB KB doesn't describe the exact semantics of DROP DEFAULT (or I couldn't find it). But at least according to MySQL manual,

      If the old default is removed and the column can be NULL, the new default is NULL.

      This is not what in fact happens (neither in MariaDB nor in MySQL).

      CREATE TABLE t1 (a INT);
      SHOW CREATE TABLE t1;
      ALTER TABLE t1 ALTER a DROP DEFAULT;
      SHOW CREATE TABLE t1;
       
      INSERT INTO t1 () VALUES ();
       
      DROP TABLE t1;
      

      10.3 e34f8781

      CREATE TABLE t1 (a INT);
      SHOW CREATE TABLE t1;
      Table	Create Table
      t1	CREATE TABLE `t1` (
        `a` int(11) DEFAULT NULL
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      ALTER TABLE t1 ALTER a DROP DEFAULT;
      SHOW CREATE TABLE t1;
      Table	Create Table
      t1	CREATE TABLE `t1` (
        `a` int(11)
      ) ENGINE=MyISAM DEFAULT CHARSET=latin1
      INSERT INTO t1 () VALUES ();
       
      mysqltest: At line 6: query 'INSERT INTO t1 () VALUES ()' failed: 1364: Field 'a' doesn't have a default value
      

      So, the column loses NULL as a default value and doesn't get a new one.
      What seems worse, SHOW CREATE TABLE returns SQL which, if executed, will create a table with a default NULL value.

      Since there is no way to indicate in SHOW CREATE TABLE that a null-able column should not have a default value, I suppose the behavior should be adjusted to what MySQL manual describes.

      MySQL (5.6/5.7/8.0) behaves the same way. There is an open (accepted) bug report about it in MySQL bug system.

      Attachments

        Activity

          People

            midenok Aleksey Midenkov
            elenst Elena Stepanova
            Votes:
            0 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.