MySQL Bugs: #9881: ALTER TABLE gives wrong error message with sql-mode TRADITIONAL

Bug #9881	ALTER TABLE gives wrong error message with sql-mode TRADITIONAL
Submitted:	13 Apr 2005 22:04	Modified:	17 Jul 2005 0:20
Reporter:	Hakan Küçükyılmaz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.9	OS:	Linux (SuSE 9.2)
Assigned to:	Ramil Kalimullin	CPU Architecture:	Any

Description:
ALTER TABLE gives wrong error message with sql-mode TRADITIONAL

How to repeat:
5.0.5-beta-log
[00:03] root@test>SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| Variable_name | Value                                                                |
+---------------+-------------------------------------------------------------------------------------------------------------------------------+
| sql_mode      | 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)

5.0.5-beta-log
[00:03] root@test>DROP TABLE IF EXISTS t1;
Query OK, 0 rows affected (0.00 sec)

5.0.5-beta-log
[00:04] root@test>CREATE TABLE t1 (a int);
Query OK, 0 rows affected (0.26 sec)

5.0.5-beta-log
[00:04] root@test>ALTER TABLE t1 ADD b DATE NOT NULL;
Query OK, 0 rows affected (0.21 sec)
Records: 0  Duplicates: 0  Warnings: 0

5.0.5-beta-log
[00:04] root@test>ALTER TABLE t1 MODIFY a BIGINT UNSIGNED NOT NULL;
ERROR 1067 (42000): Invalid default value for 'b'
5.0.5-beta-log
[00:04] root@test>

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/internals/27143

fixed in 5.0.10

Noted in 5.0.10 changelog.