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

ALTER TABLE does not exit on error with InnoDB + bad default function

    XMLWordPrintable

    Details

      Description

      This script with a bad literal in DEFAULT correctly returns the "Out of range" error:

      create or replace table t1 (a int not null default 0) engine=MyISAM;
      alter table t1 add b tinyint not null default 1000;
      

      ERROR 1264 (22003): Out of range value for column 'b' at row 1
      

      With ENGINE=InnoDB and the same literal it also return the same error:

      create or replace table t1 (a int not null default 0) engine=InnoDB;
      alter table t1 add b tinyint not null default 1000;
      

      ERROR 1264 (22003): Out of range value for column 'b' at row 1
      

      With ENGINE=MyISAM and a bad function in DEFAULT, it also returns the same error:

      set timestamp=default;
      create or replace table t1 (a int not null default 0) engine=myisam;
      insert into t1 values (10);
      alter table t1 add b tinyint not null default if(unix_timestamp()>1,1000,0);
      

      ERROR 1264 (22003): Out of range value for column 'b' at row 1
      

      So far so good.

      With ENGINE=InnoDB and a bad function in DEFAULT, it does not return any errors:

      set timestamp=default;
      create or replace table t1 (a int not null default 0) engine=innodb;
      insert into t1 values (10);
      alter table t1 add b tinyint not null default if(unix_timestamp()>1,1000,0);
      

      This looks wrong.

      DESCRIBE reports that the ALTER did actually happen:

      DESCRIBE t1;
      

      +-------+------------+------+-----+---------------------------------+-------+
      | Field | Type       | Null | Key | Default                         | Extra |
      +-------+------------+------+-----+---------------------------------+-------+
      | a     | int(11)    | NO   |     | 0                               |       |
      | b     | tinyint(4) | NO   |     | if(unix_timestamp() > 1,1000,0) |       |
      +-------+------------+------+-----+---------------------------------+-------+
      

      And this query tells that 1000 was silently changed to 127:

      SELECT * FROM t1;
      

      +----+-----+
      | a  | b   |
      +----+-----+
      | 10 | 127 |
      +----+-----+
      

      InnoDB should be fixed to return the error, like MyISAM does, and like InnoDB does with literals.

        Attachments

          Activity

            People

            Assignee:
            marko Marko Mäkelä
            Reporter:
            bar Alexander Barkov
            Votes:
            0 Vote for this issue
            Watchers:
            4 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved: