MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:eugene Date:May 21 2007 1:21pm
Subject:bk commit into 5.0 tree (evgen:1.2493) BUG#27507
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of evgen. When evgen 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, 2007-05-21 17:20:58+04:00, evgen@stripped +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.

  mysql-test/r/alter_table.result@stripped, 2007-05-21 16:39:24+04:00, evgen@stripped +19 -0
    Added a test case for the bug#27507: Wrong DATETIME value was allowed by
    ALTER TABLE in the NO_ZERO_DATE mode.

  mysql-test/t/alter_table.test@stripped, 2007-05-21 16:38:16+04:00, evgen@stripped +22 -0
    Added a test case for the bug#27507: Wrong DATETIME value was allowed by
    ALTER TABLE in the NO_ZERO_DATE mode.

  sql/sql_table.cc@stripped, 2007-05-21 17:19:27+04:00, evgen@stripped +69 -3
    Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE 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.

# This is a BitKeeper patch.  What follows are the unified diffs for the
# set of deltas contained in the patch.  The rest of the patch, the part
# that BitKeeper cares about, is below these diffs.
# User:	evgen
# Host:	moonbone.local
# Root:	/mnt/gentoo64/work/27507-bug-5.0-opt-mysql

--- 1.343/sql/sql_table.cc	2007-05-11 20:33:11.000000000 +0400
+++ 1.344/sql/sql_table.cc	2007-05-21 17:19:27.000000000 +0400
@@ -37,7 +37,8 @@
                                     List<create_field> &create, bool ignore,
 				    uint order_num, ORDER *order,
 				    ha_rows *copied,ha_rows *deleted,
-                                    enum enum_enable_or_disable keys_onoff);
+                                    enum enum_enable_or_disable keys_onoff,
+                                    bool error_if_not_empty);
 
 static bool prepare_blob_field(THD *thd, create_field *sql_field);
 static bool check_engine(THD *thd, const char *table_name,
@@ -3077,6 +3078,16 @@
   bool need_copy_table;
   bool no_table_reopen= FALSE, varchar= FALSE;
   frm_type_enum frm_type;
+  /*
+    Throw an error if the table to be altered isn't empty.
+    Used in DATE/DATETIME fields default value checking.
+  */
+  bool error_if_not_empty= FALSE;
+  /*
+    A field used for error reporting in DATE/DATETIME fields default
+    value checking.
+  */
+  create_field *new_datetime_field= 0;
   DBUG_ENTER("mysql_alter_table");
 
   thd->proc_info="init";
@@ -3445,6 +3456,22 @@
       my_error(ER_BAD_FIELD_ERROR, MYF(0), def->change, table_name);
       DBUG_RETURN(TRUE);
     }
+    /*
+      Check that the DATE/DATETIME not null field we are going to add is
+      either have a default value or the '0000-00-00' is allowed by the
+      current sql mode.
+      If the '0000-00-00' value isn't allowed then raise the error_if_not_empty
+      flag to allow ALTER TABLE only if the table to be altered is empty.
+    */
+    if ((def->sql_type == MYSQL_TYPE_DATE ||
+         def->sql_type == MYSQL_TYPE_NEWDATE ||
+         def->sql_type == MYSQL_TYPE_DATETIME) && !new_datetime_field &&
+         !(~def->flags & (NO_DEFAULT_VALUE_FLAG | NOT_NULL_FLAG)) &&
+         thd->variables.sql_mode & MODE_NO_ZERO_DATE)
+    {
+        new_datetime_field= def;
+        error_if_not_empty= TRUE;
+    }
     if (!def->after)
       new_info.create_list.push_back(def);
     else if (def->after == first_keyword)
@@ -3765,7 +3792,8 @@
     new_table->next_number_field=new_table->found_next_number_field;
     error= copy_data_between_tables(table, new_table, new_info.create_list,
                                     ignore, order_num, order,
-                                    &copied, &deleted, alter_info->keys_onoff);
+                                    &copied, &deleted, alter_info->keys_onoff,
+                                    error_if_not_empty);
   }
   else if (!new_table)
   {
@@ -3999,6 +4027,37 @@
   DBUG_RETURN(FALSE);
 
 err:
+  /*
+    No default value was provided for a DATE/DATETIME field, the
+    current sql_mode doesn't allow the '0000-00-00' value and
+    the table to be altered isn't empty.
+    Report error here.
+  */
+  if (error_if_not_empty && !copied && thd->row_count)
+  {
+    const char *f_val;
+    enum enum_mysql_timestamp_type t_type;
+    switch (new_datetime_field->sql_type)
+    {
+      case MYSQL_TYPE_DATE:
+      case MYSQL_TYPE_NEWDATE:
+        f_val= "0000-00-00";
+        t_type= MYSQL_TIMESTAMP_DATE;
+        break;
+      case MYSQL_TYPE_DATETIME:
+        f_val= "0000-00-00 00:00:00";
+        t_type= MYSQL_TIMESTAMP_DATETIME;
+        break;
+      default:
+        /* Shouldn't get here. */
+        DBUG_ASSERT(0);
+    }
+    bool save_abort_on_warning= thd->abort_on_warning;
+    thd->abort_on_warning= TRUE;
+    make_truncated_value_warning(thd, f_val, strlength(f_val), t_type,
+                                 new_datetime_field->field_name);
+    thd->abort_on_warning= save_abort_on_warning;
+  }
   DBUG_RETURN(TRUE);
 }
 
@@ -4010,7 +4069,8 @@
 			 uint order_num, ORDER *order,
 			 ha_rows *copied,
 			 ha_rows *deleted,
-                         enum enum_enable_or_disable keys_onoff)
+                         enum enum_enable_or_disable keys_onoff,
+                         bool error_if_not_empty)
 {
   int error;
   Copy_field *copy,*copy_end;
@@ -4125,6 +4185,12 @@
       break;
     }
     thd->row_count++;
+    /* Return error if source table isn't empty. */
+    if (error_if_not_empty)
+    {
+      error= 1;
+      break;
+    }
     if (to->next_number_field)
     {
       if (auto_increment_field_copied)

--- 1.65/mysql-test/r/alter_table.result	2007-05-01 14:40:37.000000000 +0400
+++ 1.66/mysql-test/r/alter_table.result	2007-05-21 16:39:24.000000000 +0400
@@ -884,3 +884,22 @@
 50
 51
 drop table t1;
+set @orig_sql_mode = @@sql_mode;
+set sql_mode="no_zero_date";
+create table t1(f1 int);
+alter table t1 add column f2 datetime not null, add column f21 date not null;
+insert into t1 values(1,'2000-01-01','2000-01-01');
+alter table t1 add column f3 datetime not null;
+ERROR 22007: Incorrect datetime value: '0000-00-00 00:00:00' for column 'f3' at row 1
+alter table t1 add column f3 date not null;
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'f3' at row 1
+alter table t1 add column f4 datetime not null default '2002-02-02',
+add column f41 date not null;
+ERROR 22007: Incorrect date value: '0000-00-00' for column 'f41' at row 1
+alter table t1 add column f4 datetime not null default '2002-02-02',
+add column f41 date not null default '2002-02-02';
+select * from t1;
+f1	f2	f21	f4	f41
+1	2000-01-01 00:00:00	2000-01-01	2002-02-02 00:00:00	2002-02-02
+drop table t1;
+set sql_mode= @orig_sql_mode;

--- 1.52/mysql-test/t/alter_table.test	2007-05-01 14:40:37.000000000 +0400
+++ 1.53/mysql-test/t/alter_table.test	2007-05-21 16:38:16.000000000 +0400
@@ -662,3 +662,25 @@
 select * from t1;
 
 drop table t1;
+
+#
+# Bug#27507: Wrong DATETIME value was allowed by ALTER TABLE in the
+#            NO_ZERO_DATE mode.
+#
+set @orig_sql_mode = @@sql_mode;
+set sql_mode="no_zero_date";
+create table t1(f1 int);
+alter table t1 add column f2 datetime not null, add column f21 date not null;
+insert into t1 values(1,'2000-01-01','2000-01-01');
+--error 1292
+alter table t1 add column f3 datetime not null;
+--error 1292
+alter table t1 add column f3 date not null;
+--error 1292
+alter table t1 add column f4 datetime not null default '2002-02-02',
+  add column f41 date not null;
+alter table t1 add column f4 datetime not null default '2002-02-02',
+  add column f41 date not null default '2002-02-02';
+select * from t1;
+drop table t1;
+set sql_mode= @orig_sql_mode;
Thread
bk commit into 5.0 tree (evgen:1.2493) BUG#27507eugene21 May