diff options
| author | Sergei Golubchik <serg@mariadb.org> | 2021-04-13 19:52:40 +0200 |
|---|---|---|
| committer | Sergei Golubchik <serg@mariadb.org> | 2021-04-15 09:42:37 +0200 |
| commit | 499e617182c3139b36978faafe186934bd218f54 (patch) | |
| tree | aaee6385a1f2af46cad6e693919f377b99aedf0c | |
| parent | 3ebd6cd3ad7c4f7584837d37452f3a456dd48085 (diff) | |
| download | mariadb-git-499e617182c3139b36978faafe186934bd218f54.tar.gz | |
MDEV-25403 ALTER TABLE wrongly checks for field's default value if AFTER is used
When a column is added to an non-empty table, existing rows will have
a column's default value for existing rows. Or a "zero value" if the
column has no default.
But this check should be skipped when an existing column is altered.
| -rw-r--r-- | mysql-test/r/alter_table.result | 17 | ||||
| -rw-r--r-- | mysql-test/t/alter_table.test | 12 | ||||
| -rw-r--r-- | sql/sql_table.cc | 2 |
3 files changed, 30 insertions, 1 deletions
diff --git a/mysql-test/r/alter_table.result b/mysql-test/r/alter_table.result index 69322bb9028..1fcb8c6a33d 100644 --- a/mysql-test/r/alter_table.result +++ b/mysql-test/r/alter_table.result @@ -2517,5 +2517,22 @@ ALTER TABLE t1 ALTER COLUMN k1 SET DEFAULT (SELECT 1 FROM t2 limit 1); ERROR HY000: Function or expression 'select ...' cannot be used in the DEFAULT clause of `k1` DROP TABLE t1,t2; # +# MDEV-25403 ALTER TABLE wrongly checks for field's default value if AFTER is used +# +create table t1(t int, d date not null); +insert into t1 values (1,'2001-1-1'); +set sql_mode = "no_zero_date"; +alter table t1 change d d date not null after t, add i int; +show create table t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `t` int(11) DEFAULT NULL, + `d` date NOT NULL, + `i` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 +alter table t1 add x date not null; +ERROR 22007: Incorrect date value: '0000-00-00' for column `test`.`t1`.`x` at row 1 +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/t/alter_table.test b/mysql-test/t/alter_table.test index 2f1a40cbacd..7b03ead3739 100644 --- a/mysql-test/t/alter_table.test +++ b/mysql-test/t/alter_table.test @@ -2045,5 +2045,17 @@ ALTER TABLE t1 ALTER COLUMN k1 SET DEFAULT (SELECT 1 FROM t2 limit 1); DROP TABLE t1,t2; --echo # +--echo # MDEV-25403 ALTER TABLE wrongly checks for field's default value if AFTER is used +--echo # +create table t1(t int, d date not null); +insert into t1 values (1,'2001-1-1'); +set sql_mode = "no_zero_date"; +alter table t1 change d d date not null after t, add i int; +show create table t1; +--error ER_TRUNCATED_WRONG_VALUE +alter table t1 add x date not null; +drop table t1; + +--echo # --echo # End of 10.2 tests --echo # diff --git a/sql/sql_table.cc b/sql/sql_table.cc index cb28c6adcec..d472e2332f2 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -7906,7 +7906,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table, def->sql_type == MYSQL_TYPE_NEWDATE || def->sql_type == MYSQL_TYPE_DATETIME || def->sql_type == MYSQL_TYPE_DATETIME2) && - !alter_ctx->datetime_field && + !alter_ctx->datetime_field && !def->field && !(~def->flags & (NO_DEFAULT_VALUE_FLAG | NOT_NULL_FLAG)) && thd->variables.sql_mode & MODE_NO_ZERO_DATE) { |