Bug #27507 Out of range value accepted for new NOT NULL datetime column in existing table

Description:
Adding a not null datetime column to an existing table with data will default the column to '0000-00-00 00:00:00' even if that datetime is not allowed by the sql_mode.

Subsequent alter tables will fail.

How to repeat:
mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.0.33    | 
+-----------+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @@sql_mode                                                                                                                                                          |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| PIPES_AS_CONCAT,NO_AUTO_VALUE_ON_ZERO,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,TRADITIONAL,NO_AUTO_CREATE_USER | 
+---------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table ed (id int unsigned) engine=innodb;
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ed set id = 1;
Query OK, 1 row affected (0.00 sec)

mysql> alter table ed add column blah datetime not null; 
Query OK, 1 row affected (0.04 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> alter table ed engine = myisam;
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'blah' at row 1

Suggested fix:
Throw error if altering a table to a state that forces invalid data (ie disallow the first alter).

Thank you for a bug report. Verified just as described:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.40 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> set sql_mode="no_zero_in_date,strict_all_tables";
Query OK, 0 rows affected (0.00 sec)

mysql> select @@sql_mode;
+-----------------------------------+
| @@sql_mode                        |
+-----------------------------------+
| STRICT_ALL_TABLES,NO_ZERO_IN_DATE |
+-----------------------------------+
1 row in set (0.01 sec)

mysql> create table ed (id int unsigned) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ed set id=1;
Query OK, 1 row affected (0.00 sec)

mysql> alter table ed add column blah datetime not null;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select * from ed;
+------+---------------------+
| id   | blah                |
+------+---------------------+
|    1 | 0000-00-00 00:00:00 |
+------+---------------------+
1 row in set (0.00 sec)

mysql> alter table ed engine=MyISAM;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

So, ALTER is not giving errors even with STRICT_ALL_TABLES in this case, but still, with NO_ZERO_IN_DATE first ALTER TABLE should NOT be allowed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27069

ChangeSet@1.2493, 2007-05-21 17:20:58+04:00, evgen@moonbone.local +3 -0
  Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the NO_ZERO_DATE
  mode.
  
  When new DATE/DATETIME field without default value is being added by the
  ALTER TABLE the '0000-00-00' value is used as the default one. But it wasn't
  checked whether such value is allowed by the current sql mode. Due to this
  '0000-00-00' values was allowed for DATE/DATETIME fields even in the
  NO_ZERO_DATE mode.
  
  Now the mysql_alter_table() function checks whether the '0000-00-00' value
  is allowed for DATE/DATETIME fields by the current sql mode.
  The new error_if_not_empty flag is used in the mysql_alter_table() function
  to indicate that it should abort if the table being altered isn't empty.
  The new new_datetime_field field is used in the mysql_alter_table() function
  for error throwing purposes. 
  The new error_if_not_empty parameter is added to the copy_data_between_tables()
  function to indicate the it should return error if the source table isn't empty.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/27100

ChangeSet@1.2496, 2007-05-22 00:22:53+04:00, evgen@moonbone.local +3 -0
  Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the NO_ZERO_DATE
  mode.
  
  When a new DATE/DATETIME field without default value is being added by the
  ALTER TABLE the '0000-00-00' value is used as the default one. But it wasn't
  checked whether such value was allowed by the set sql mode. Due to this
  '0000-00-00' values was allowed for DATE/DATETIME fields even in the
  NO_ZERO_DATE mode.
  
  Now the mysql_alter_table() function checks whether the '0000-00-00' value
  is allowed for DATE/DATETIME fields by the set sql mode.
  The new error_if_not_empty flag is used in the mysql_alter_table() function
  to indicate that it should abort if the table being altered isn't empty.
  The new new_datetime_field field is used in the mysql_alter_table() function
  for error throwing purposes. 
  The new error_if_not_empty parameter is added to the copy_data_between_tables()
  function to indicate the it should return error if the source table isn't empty.

Pushed into 5.1.20-beta

Pushed into 5.0.44

Noted in 5.0.44, 5.1.20 changelogs.

When ALTER TABLE was used to add a new DATE column with no explicit
default value, '0000-00-00' was used as the default even if the SQL
mode included the NO_ZERO_DATE mode to prohibit that value. A similar
problem occurred for DATETIME columns.