Bug #8217 Will not modify DATE NULL on ALTER TABLE

• Submitted: 31 Jan 2005 18:37
• Modified: 12 Apr 2005 9:10
• Reporter: Egor Egorov
• Status: Not a Bug
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.9
• OS: Linux (Linux)
• Assigned to: Dmitry Lenev
• CPU Architecture: Any

[31 Jan 2005 18:37] Egor Egorov

Description:
When a field type "DATE NOT NULL DEFAULT '0000-00-00'" exists, it won't modify with "ALTER TABLE name MODIFY fieldname DATE NULL". 

No errors, no warnings, table schema intact. 

How to repeat:
mysql> show create table quantities\G
*************************** 1. row ***************************
       Table: quantities
Create Table: CREATE TABLE `quantities` (
  `period` int(11) NOT NULL default '0',
  `quantityHoliday` int(11) NOT NULL default '0',
  `quantityWorkday` int(11) NOT NULL default '0',
  `startingDate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`period`,`startingDate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table quantities modify startingDate date null;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table quantities\G
*************************** 1. row ***************************
       Table: quantities
Create Table: CREATE TABLE `quantities` (
  `period` int(11) NOT NULL default '0',
  `quantityHoliday` int(11) NOT NULL default '0',
  `quantityWorkday` int(11) NOT NULL default '0',
  `startingDate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`period`,`startingDate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table quantities modify startingDate date null DEFAULT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table quantities\G
*************************** 1. row ***************************
       Table: quantities
Create Table: CREATE TABLE `quantities` (
  `period` int(11) NOT NULL default '0',
  `quantityHoliday` int(11) NOT NULL default '0',
  `quantityWorkday` int(11) NOT NULL default '0',
  `startingDate` date NOT NULL default '0000-00-00',
  PRIMARY KEY  (`period`,`startingDate`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

[12 Apr 2005 9:10] Dmitry Lenev

Hi, Egor!

This is not a bug. "starting_date" column is part of primary key so it is automatically
gets NOT NULL attribute as specified in chapter 13.2.5.1 "Silent Column Specification Changes" of MySQL manual:

Other silent column specification changes include changes to attribute or index specifications:
 ...
- Columns that are part of a PRIMARY KEY are made NOT NULL even if not declared that way. 
...

Both CREATE TABLE and ALTER TABLE are subject of those silent changes.