MySQL Bugs: #59062: ALTER TABLE (empty table) fails

Bug #59062	ALTER TABLE (empty table) fails
Submitted:	20 Dec 2010 16:18	Modified:	20 Dec 2010 18:29
Reporter:	Peter Laursen (Basic Quality Contributor)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S3 (Non-critical)
Version:	5.1.54	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
Coming from here: http://bugs.mysql.com/bug.php?id=59050

I think for clarity this should be posted as a server report. So I do (then in the other report you can discuss if it is bug that WB sets a 'strict' mode).

Problem is that ALTER TABLE fails with error "Incorrect datetime value: '0000-00-00 00:00:00'" even when there are no such values.

How to repeat:
SET SQL_MODE = 'no_zero_date,strict_all_tables';
CREATE TABLE `test`.`dttest`(`id` INT NOT NULL AUTO_INCREMENT,`dt` DATETIME, PRIMARY KEY
(`id`));
ALTER TABLE `test`.`dttest` ADD COLUMN `txt` CHAR(5) NULL AFTER `dt`;
-- error: Incorrect datetime value: '0000-00-00 00:00:00' for column 'dt' at row 1

SET SQL_MODE = ''; 
ALTER TABLE `test`.`dttest` ADD COLUMN `txt` CHAR(5) NULL AFTER `dt`;
-- success.

Suggested fix:
There are no '0000-00-00' date values.  Actually table is empty.  The error does not make sense. I have no clue about what happens internally.

I can not repeat this on current mysql-5.1 tree from bzr:

macbook-pro:5.1 openxs$ 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.1.55-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET SQL_MODE = 'no_zero_date,strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test`.`dttest`(`id` INT NOT NULL AUTO_INCREMENT,`dt` DATETIME, PRIMARY KEY
    -> (`id`));
Query OK, 0 rows affected (0.10 sec)

mysql> ALTER TABLE `test`.`dttest` ADD COLUMN `txt` CHAR(5) NULL AFTER `dt`;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

No problems with 5.1.54 (official binaries) on Mac:

macbook-pro:mysql-5.1.54-osx10.5-x86_64 openxs$ bin/mysql -uroot testWelcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.54 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SET SQL_MODE = 'no_zero_date,strict_all_tables';
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE `test`.`dttest`(`id` INT NOT NULL AUTO_INCREMENT,`dt` DATETIME, PRIMARY KEY
    -> (`id`));
Query OK, 0 rows affected (0.06 sec)

mysql> ALTER TABLE `test`.`dttest` ADD COLUMN `txt` CHAR(5) NULL AFTER `dt`;
Query OK, 0 rows affected (0.09 sec)
Records: 0  Duplicates: 0  Warnings: 0

Will try on Windows tomorrow (if nobody else will do).

I cannot either with 5.1.54 now.  Seems that I really had such zero-date value in the table when executing ALTER ...