Below is the list of changes that have just been committed into a local
5.1 repository of anozdrin. When anozdrin does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html
ChangeSet@stripped, 2008-02-14 18:13:40+03:00, anozdrin@quad. +3 -0
A patch for Bug#18834: ALTER TABLE ADD INDEX on table with
two timestamp fields.
The actual problem here was that CREATE TABLE allowed zero
date as a default value for a TIMESTAMP column in NO_ZERO_DATE mode.
The thing is that for TIMESTAMP date type specific rule is applied:
column_name TIMESTAMP == column_name TIMESTAMP DEFAULT 0
whever for any other date data type
column_name TYPE == column_name TYPE DEFAULT NULL
The fix is to raise an error when we're in NO_ZERO_DATE mode and
there is TIMESTAMP column w/o default value.
mysql-test/r/create.result@stripped, 2008-02-14 18:13:38+03:00, anozdrin@quad. +44 -0
Update result file.
mysql-test/t/create.test@stripped, 2008-02-14 18:13:38+03:00, anozdrin@quad. +64 -0
Test case for Bug#18834: ALTER TABLE ADD INDEX on table with
two timestamp fields.
sql/sql_table.cc@stripped, 2008-02-14 18:13:38+03:00, anozdrin@quad. +31 -0
Report an error if NO_ZERO_MODE is set and we have zero date
as a default.
diff -Nrup a/mysql-test/r/create.result b/mysql-test/r/create.result
--- a/mysql-test/r/create.result 2008-02-08 20:06:04 +03:00
+++ b/mysql-test/r/create.result 2008-02-14 18:13:38 +03:00
@@ -1789,4 +1789,48 @@ DROP TABLE t2;
# -- End of test case for Bug#21380.
+# --
+# -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields
+# --
+
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+
+CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
+
+SET sql_mode = NO_ZERO_DATE;
+
+CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
+ERROR 42000: Invalid default value for 'c2'
+
+CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
+ERROR 42000: Invalid default value for 'c2'
+
+# -- Check that NULL column still can be created.
+CREATE TABLE t2(c1 TIMESTAMP NULL);
+
+# -- Check ALTER TABLE.
+ALTER TABLE t1 ADD INDEX(c1);
+ERROR 42000: Invalid default value for 'c2'
+
+# -- Check DATETIME.
+SET sql_mode = '';
+
+CREATE TABLE t3(c1 DATETIME NOT NULL);
+INSERT INTO t3 VALUES (0);
+
+SET sql_mode = TRADITIONAL;
+
+ALTER TABLE t3 ADD INDEX(c1);
+ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'c1' at row 1
+
+# -- Cleanup.
+SET sql_mode = '';
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+# -- End of Bug#18834.
+
End of 5.1 tests
diff -Nrup a/mysql-test/t/create.test b/mysql-test/t/create.test
--- a/mysql-test/t/create.test 2008-02-08 20:06:04 +03:00
+++ b/mysql-test/t/create.test 2008-02-14 18:13:38 +03:00
@@ -1385,4 +1385,68 @@ DROP TABLE t2;
--echo # -- End of test case for Bug#21380.
--echo
+--echo # --
+--echo # -- Bug#18834: ALTER TABLE ADD INDEX on table with two timestamp fields
+--echo # --
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+DROP TABLE IF EXISTS t3;
+--enable_warnings
+
+--echo
+
+CREATE TABLE t1(c1 TIMESTAMP, c2 TIMESTAMP);
+
+--echo
+
+SET sql_mode = NO_ZERO_DATE;
+
+--echo
+--error ER_INVALID_DEFAULT
+CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP DEFAULT 0);
+
+--echo
+--error ER_INVALID_DEFAULT
+CREATE TABLE t2(c1 TIMESTAMP, c2 TIMESTAMP);
+
+--echo
+--echo # -- Check that NULL column still can be created.
+CREATE TABLE t2(c1 TIMESTAMP NULL);
+
+--echo
+--echo # -- Check ALTER TABLE.
+--error ER_INVALID_DEFAULT
+ALTER TABLE t1 ADD INDEX(c1);
+
+--echo
+--echo # -- Check DATETIME.
+SET sql_mode = '';
+
+--echo
+
+CREATE TABLE t3(c1 DATETIME NOT NULL);
+INSERT INTO t3 VALUES (0);
+
+--echo
+SET sql_mode = TRADITIONAL;
+
+--echo
+--error ER_TRUNCATED_WRONG_VALUE
+ALTER TABLE t3 ADD INDEX(c1);
+
+--echo
+--echo # -- Cleanup.
+
+SET sql_mode = '';
+DROP TABLE t1;
+DROP TABLE t2;
+DROP TABLE t3;
+
+--echo
+--echo # -- End of Bug#18834.
+--echo
+
--echo End of 5.1 tests
diff -Nrup a/sql/sql_table.cc b/sql/sql_table.cc
--- a/sql/sql_table.cc 2008-02-01 13:50:18 +03:00
+++ b/sql/sql_table.cc 2008-02-14 18:13:38 +03:00
@@ -3001,6 +3001,37 @@ mysql_prepare_create_table(THD *thd, HA_
(qsort_cmp) sort_keys);
create_info->null_bits= null_fields;
+ /* Check fields. */
+ it.rewind();
+ while ((sql_field=it++))
+ {
+ Field::utype type= (Field::utype) MTYP_TYPENR(sql_field->unireg_check);
+
+ if (thd->variables.sql_mode & MODE_NO_ZERO_DATE &&
+ !sql_field->def &&
+ sql_field->sql_type == MYSQL_TYPE_TIMESTAMP &&
+ (sql_field->flags & NOT_NULL_FLAG) &&
+ (type == Field::NONE || type == Field::TIMESTAMP_UN_FIELD))
+ {
+ /*
+ An error should be reported if:
+ - NO_ZERO_DATE SQL mode is active;
+ - there is no explicit DEFAULT clause (default column value);
+ - this is a TIMESTAMP column;
+ - the column is not NULL;
+ - this is not the DEFAULT CURRENT_TIMESTAMP column.
+
+ In other words, an error should be reported if
+ - NO_ZERO_DATE SQL mode is active;
+ - the column definition is equivalent to
+ 'column_name TIMESTAMP DEFAULT 0'.
+ */
+
+ my_error(ER_INVALID_DEFAULT, MYF(0), sql_field->field_name);
+ DBUG_RETURN(TRUE);
+ }
+ }
+
DBUG_RETURN(FALSE);
}
