[MDEV-25403] ALTER TABLE wrongly checks for field's default value if AFTER is used Created: 2021-04-13  Updated: 2021-04-15  Resolved: 2021-04-15

Status: Closed
Project: MariaDB Server
Component/s: Data Definition - Alter Table
Affects Version/s: 5.5, 10.0, 10.1, 10.3.27, 10.2, 10.3, 10.4, 10.5
Fix Version/s: 10.2.38, 10.3.29, 10.4.19, 10.5.10

Type: Bug Priority: Major
Reporter: Marc Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Linux

Issue Links:
Relates
relates to MDEV-24691 sql_mode NO_ZERO_DATE conflicts with ... Open

 Description   

See the following test script:

CREATE TABLE test(
 
t text NOT NULL,
 
d date NOT NULL
 
);
 
 
 
insert into test values ("x",CURRENT_DATE());
 
 
 
SET SESSION sql_mode = "STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE";
 
 
 
ALTER TABLE test
 
CHANGE d d DATE  NOT NULL AFTER t,
 
 ADD i text NOT NULL AFTER d
 
;

Resulting in
Error (1292): Incorrect date value: '0000-00-00' for column `test`.`d` at row 1

As you can see this is totally wrong, there is no such value 0000-00-00 in this table, and the alter statement should succeed.

 Comments   
Comment by Alice Sherepa [ 2021-04-13 ]

Thanks! I repeated as described on 5.5-10.5 with Myisam table, while no error with InnoDB table on 5.5-10.2,
but error on 10.3-10.5 

 
--source include/have_innodb.inc
 
 
 
CREATE TABLE t1(t int, d date NOT NULL) engine=innodb;
 
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 ;


query 'ALTER TABLE test change d d DATE NOT NULL AFTER t, ADD i int ' failed: 1292: Incorrect date value: '0000-00-00' for column `test`.`t1`.`d` at row 1

Generated at Thu Feb 08 09:37:25 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.