Support for TIMESTAMP columns holding NULL values. Unlike all other

column types TIMESTAMP is NOT NULL by default, so in order to have 
TIMESTAMP column holding NULL valaues you have to specify NULL as
one of its attributes (this needed for backward compatibility).

Main changes:
Replaced TABLE::timestamp_default_now/on_update_now members with
TABLE::timestamp_auto_set_type flag which is used everywhere
for determining if we should auto-set value of TIMESTAMP field 
during this operation or not. We are also use Field_timestamp::set_time()
instead of handler::update_timestamp() in handlers.


mysql-test/r/type_timestamp.result:
  Added test for TIMESTAMP columns which are able to store NULL values.
mysql-test/t/type_timestamp.test:
  Added test for TIMESTAMP columns which are able to store NULL values.
sql/field.cc:
  Added support for TIMESTAMP fields holding NULL values.
  We don't need Field_timestamp::set_timestamp_offsets() anymore.
  Instead we need Field_timestamp::get_auto_set_type() function
  which will convert TIMESTAMP auto-set type stored in Field in 
  unireg_check to value from timestamp_auto_set_type_enum.
  (We can't replace this function with additional Field_timestamp member
  and some code in constructor because then we will have troubles
  with Field::new_field() method).
  We should also set field to not null in Field_timestamp::set_time() now.
sql/field.h:
  Added support for TIMESTAMP fields holding NULL values.
  We don't need Field_timestamp::set_timestamp_offsets() anymore.
  Instead we need Field_timestamp::get_auto_set_type() function,
  which will convert TIMESTAMP auto-set type stored in Field in 
  unireg_check to value from timestamp_auto_set_type_enum.
  We also have to support NULL values in Field_timestamp::get_timestamp()
  function.
sql/field_conv.cc:
  Added comment clarifying behavior in case of TIMESTAMP fields which are
  able to store NULL values.
sql/ha_berkeley.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_heap.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_innodb.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_isam.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_isammrg.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_myisam.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_myisammrg.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/ha_ndbcluster.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now for determining 
  if we should auto-set value of TIMESTAMP field during this operation.
  We are also use Field_timestamp::set_time() instead of 
  handler::update_timestamp().
sql/handler.cc:
  handler::update_timestamp() is no longer needed since now we use
  Field_timestamp::set_time() instead.
  (we can't use handler::update_timestamp() anyway since field position
  only is not enough for TIMESTAMP fields which are able to store NULLs)
sql/handler.h:
  handler::update_timestamp() is no longer needed since now we use
  Field_timestamp::set_time() instead.
sql/item_timefunc.cc:
  Since now TIMESTAMP fields can hold NULL values we should take this into
  account.
sql/sql_base.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now. 
  (Here we use Field_timestamp::get_auto_set_type() to setup its value
   before further statement execution).
sql/sql_insert.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now.
sql/sql_load.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now.
sql/sql_parse.cc:
  Added support for TIMESTAMP fields holding NULL values.
  We should distinguish NULL default values and non-specified default
  values for such fields (because latter could mean DEFAULT NOW()
  ON UPDATE NOW() in some cases).
sql/sql_show.cc:
  Added support for TIMESTAMP fields holding NULL values.
  Unlike all other fields these are NOT NULL by default
  so we have to specify NULL attribute explicitly for them.
sql/sql_table.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now.
sql/sql_update.cc:
  Now we use TABLE::timestamp_field_type instead of 
  TABLE::timestamp_default_now/on_update_now.
sql/sql_yacc.yy:
  Added support for TIMESTAMP fields holding NULL values.
  Unlike all other fields these are NOT NULL by default
  (so we have to set NOT_NULL_FLAG properly for them).
sql/table.h:
  Added timestamp_auto_set_type enum which values are used for indicating
  during which operations we should automatically set TIMESTAPM field
  value to current timestamp.
  TABLE: Replaced timestamp_default_now/on_update_now members with
  timestamp_auto_set_type flag (Now when TIMESTAMP field are able to 
  store NULL values, single position of field in record is not enough 
  for updating this field anyway).

25 files changed, 193 insertions, 109 deletions

diff --git a/mysql-test/r/type_timestamp.result b/mysql-test/r/type_timestamp.result
index 425e4a05586..a823049634f 100644
--- a/mysql-test/r/type_timestamp.result
+++ b/mysql-test/r/type_timestamp.result
@@ -365,6 +365,35 @@ select * from t1;
 t1	i
 2004-04-01 00:00:00	10
 drop table t1;
+create table t1 (a timestamp null, b timestamp null);
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
+  `b` timestamp NULL default NULL
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values (NULL, NULL);
+SET TIMESTAMP=1000000017;
+insert into t1 values ();
+select * from t1;
+a	b
+NULL	NULL
+2001-09-09 04:46:57	NULL
+drop table t1;
+create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
+show create table t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `a` timestamp NULL default NULL,
+  `b` timestamp NULL default '2003-01-01 00:00:00'
+) ENGINE=MyISAM DEFAULT CHARSET=latin1
+insert into t1 values (NULL, NULL);
+insert into t1 values (DEFAULT, DEFAULT);
+select * from t1;
+a	b
+NULL	NULL
+NULL	2003-01-01 00:00:00
+drop table t1;
 create table t1 (ts timestamp(19));
 show create table t1;
 Table	Create Table
diff --git a/mysql-test/t/type_timestamp.test b/mysql-test/t/type_timestamp.test
index a644197f757..7c1258785b0 100644
--- a/mysql-test/t/type_timestamp.test
+++ b/mysql-test/t/type_timestamp.test
@@ -234,7 +234,27 @@ alter table t1 add i int default 10;
 select * from t1;
 drop table t1;
 
+#
+# Test for TIMESTAMP columns which are able to store NULLs
+# (Auto-set property should work for them and NULL values
+#  should be OK as default values)
+#
+create table t1 (a timestamp null, b timestamp null);
+show create table t1;
+insert into t1 values (NULL, NULL);
+SET TIMESTAMP=1000000017;
+insert into t1 values ();
+select * from t1;
+drop table t1;
 
+create table t1 (a timestamp null default null, b timestamp null default '2003-01-01 00:00:00');
+show create table t1;
+insert into t1 values (NULL, NULL);
+insert into t1 values (DEFAULT, DEFAULT);
+select * from t1;
+drop table t1;
+
+#
 # Test for bug #4491, TIMESTAMP(19) should be possible to create and not
 # only read in 4.0
 #
diff --git a/sql/field.cc b/sql/field.cc
index 17a89ad02c1..a508b378544 100644
--- a/sql/field.cc
+++ b/sql/field.cc
@@ -2902,11 +2902,12 @@ void Field_double::sql_type(String &res) const
  */
 
 Field_timestamp::Field_timestamp(char *ptr_arg, uint32 len_arg,
+                                 uchar *null_ptr_arg, uchar null_bit_arg,
 				 enum utype unireg_check_arg,
 				 const char *field_name_arg,
 				 struct st_table *table_arg,
 				 CHARSET_INFO *cs)
-  :Field_str(ptr_arg, 19, (uchar*) 0,0,
+  :Field_str(ptr_arg, 19, null_ptr_arg, null_bit_arg,
 	     unireg_check_arg, field_name_arg, table_arg, cs)
 {
   /* For 4.0 MYD and 4.0 InnoDB compatibility */
@@ -2922,23 +2923,33 @@ Field_timestamp::Field_timestamp(char *ptr_arg, uint32 len_arg,
 
 
 /*
-    Sets TABLE::timestamp_default_now and TABLE::timestamp_on_update_now 
-    members according to unireg type of this TIMESTAMP field.
-  
+  Get auto-set type for TIMESTAMP field.
+
   SYNOPSIS
-    Field_timestamp::set_timestamp_offsets()
-  
+    get_auto_set_type()
+
+  DESCRIPTION
+    Returns value indicating during which operations this TIMESTAMP field
+    should be auto-set to current timestamp.
 */
-void Field_timestamp::set_timestamp_offsets()
+timestamp_auto_set_type Field_timestamp::get_auto_set_type() const
 {
-  ulong timestamp= (ulong) (ptr - (char*) table->record[0]) + 1;
-  
-  DBUG_ASSERT(table->timestamp_field == this && unireg_check != NONE);
-
-  table->timestamp_default_now= 
-    (unireg_check == TIMESTAMP_UN_FIELD)? 0 : timestamp;
-  table->timestamp_on_update_now= 
-    (unireg_check == TIMESTAMP_DN_FIELD)? 0 : timestamp;
+  switch (unireg_check)
+  {
+  case TIMESTAMP_DN_FIELD:
+    return TIMESTAMP_AUTO_SET_ON_INSERT;
+  case TIMESTAMP_UN_FIELD:
+    return TIMESTAMP_AUTO_SET_ON_UPDATE;
+  case TIMESTAMP_DNUN_FIELD:
+    return TIMESTAMP_AUTO_SET_ON_BOTH;
+  default:
+    /*
+      Normally this function should not be called for TIMESTAMPs without
+      auto-set property.
+    */
+    DBUG_ASSERT(0);
+    return TIMESTAMP_NO_AUTO_SET;
+  }
 }
 
 
@@ -3237,6 +3248,7 @@ void Field_timestamp::sql_type(String &res) const
 void Field_timestamp::set_time()
 {
   long tmp= (long) table->in_use->query_start();
+  set_notnull();
 #ifdef WORDS_BIGENDIAN
   if (table->db_low_byte_first)
   {
@@ -5917,8 +5929,9 @@ Field *make_field(char *ptr, uint32 field_length,
 			      f_is_zerofill(pack_flag) != 0,
 			      f_is_dec(pack_flag) == 0);
   case FIELD_TYPE_TIMESTAMP:
-    return new Field_timestamp(ptr,field_length,
-			       unireg_check, field_name, table, field_charset);
+    return new Field_timestamp(ptr,field_length, null_pos, null_bit,
+                               unireg_check, field_name, table,
+                               field_charset);
   case FIELD_TYPE_YEAR:
     return new Field_year(ptr,field_length,null_pos,null_bit,
 			  unireg_check, field_name, table);
diff --git a/sql/field.h b/sql/field.h
index e12dd60c13b..8432ce02e5e 100644
--- a/sql/field.h
+++ b/sql/field.h
@@ -676,6 +676,7 @@ public:
 class Field_timestamp :public Field_str {
 public:
   Field_timestamp(char *ptr_arg, uint32 len_arg,
+                  uchar *null_ptr_arg, uchar null_bit_arg,
 		  enum utype unireg_check_arg, const char *field_name_arg,
 		  struct st_table *table_arg,
 		  CHARSET_INFO *cs);
@@ -705,8 +706,11 @@ public:
     else
       Field::set_default();
   }
-  inline long get_timestamp()
+  /* Get TIMESTAMP field value as seconds since begging of Unix Epoch */
+  inline long get_timestamp(my_bool *null_value)
   {
+    if ((*null_value= is_null()))
+      return 0;
 #ifdef WORDS_BIGENDIAN
     if (table->db_low_byte_first)
       return sint4korr(ptr);
@@ -718,7 +722,7 @@ public:
   bool get_date(TIME *ltime,uint fuzzydate);
   bool get_time(TIME *ltime);
   field_cast_enum field_cast_type() { return FIELD_CAST_TIMESTAMP; }
-  void set_timestamp_offsets();
+  timestamp_auto_set_type get_auto_set_type() const;
 };
 
 
diff --git a/sql/field_conv.cc b/sql/field_conv.cc
index d7993939092..c9b21b5f96f 100644
--- a/sql/field_conv.cc
+++ b/sql/field_conv.cc
@@ -164,7 +164,8 @@ set_field_to_null_with_conversions(Field *field, bool no_conversions)
 
   /*
     Check if this is a special type, which will get a special walue
-    when set to NULL
+    when set to NULL (TIMESTAMP fields which allow setting to NULL
+    are handled by first check).
   */
   if (field->type() == FIELD_TYPE_TIMESTAMP)
   {
diff --git a/sql/ha_berkeley.cc b/sql/ha_berkeley.cc
index 32f623b86c9..c688f3c3597 100644
--- a/sql/ha_berkeley.cc
+++ b/sql/ha_berkeley.cc
@@ -856,8 +856,8 @@ int ha_berkeley::write_row(byte * record)
   DBUG_ENTER("write_row");
 
   statistic_increment(ha_write_count,&LOCK_status);
-  if (table->timestamp_default_now)
-    update_timestamp(record+table->timestamp_default_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+    table->timestamp_field->set_time();
   if (table->next_number_field && record == table->record[0])
     update_auto_increment();
   if ((error=pack_row(&row, record,1)))
@@ -1103,8 +1103,8 @@ int ha_berkeley::update_row(const byte * old_row, byte * new_row)
   LINT_INIT(error);
 
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_row+table->timestamp_on_update_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
 
   if (hidden_primary_key)
   {
diff --git a/sql/ha_heap.cc b/sql/ha_heap.cc
index d7327362286..5be51ec8494 100644
--- a/sql/ha_heap.cc
+++ b/sql/ha_heap.cc
@@ -87,8 +87,8 @@ void ha_heap::set_keys_for_scanning(void)
 int ha_heap::write_row(byte * buf)
 {
   statistic_increment(ha_write_count,&LOCK_status);
-  if (table->timestamp_default_now)
-    update_timestamp(buf+table->timestamp_default_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+    table->timestamp_field->set_time();
   if (table->next_number_field && buf == table->record[0])
     update_auto_increment();
   return heap_write(file,buf);
@@ -97,8 +97,8 @@ int ha_heap::write_row(byte * buf)
 int ha_heap::update_row(const byte * old_data, byte * new_data)
 {
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_data+table->timestamp_on_update_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
   return heap_update(file,old_data,new_data);
 }
 
diff --git a/sql/ha_innodb.cc b/sql/ha_innodb.cc
index bf1be6f5d7e..15112f71d23 100644
--- a/sql/ha_innodb.cc
+++ b/sql/ha_innodb.cc
@@ -2218,8 +2218,8 @@ ha_innobase::write_row(
 
   	statistic_increment(ha_write_count, &LOCK_status);
 
-        if (table->timestamp_default_now)
-                update_timestamp(record + table->timestamp_default_now - 1);
+        if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+                table->timestamp_field->set_time();
 
 	if (last_query_id != user_thd->query_id) {
 	        prebuilt->sql_stat_start = TRUE;
@@ -2590,8 +2590,8 @@ ha_innobase::update_row(
 	ut_ad(prebuilt->trx ==
 		(trx_t*) current_thd->transaction.all.innobase_tid);
 
-        if (table->timestamp_on_update_now)
-                update_timestamp(new_row + table->timestamp_on_update_now - 1);
+        if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+                table->timestamp_field->set_time();
 
 	if (last_query_id != user_thd->query_id) {
 	        prebuilt->sql_stat_start = TRUE;
diff --git a/sql/ha_isam.cc b/sql/ha_isam.cc
index 85ab25a31d9..9de532fa7b0 100644
--- a/sql/ha_isam.cc
+++ b/sql/ha_isam.cc
@@ -70,8 +70,8 @@ uint ha_isam::min_record_length(uint options) const
 int ha_isam::write_row(byte * buf)
 {
   statistic_increment(ha_write_count,&LOCK_status);
-  if (table->timestamp_default_now)
-    update_timestamp(buf+table->timestamp_default_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+    table->timestamp_field->set_time();
   if (table->next_number_field && buf == table->record[0])
     update_auto_increment();
   return !nisam_write(file,buf) ? 0 : my_errno ? my_errno : -1;
@@ -80,8 +80,8 @@ int ha_isam::write_row(byte * buf)
 int ha_isam::update_row(const byte * old_data, byte * new_data)
 {
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_data+table->timestamp_on_update_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
   return !nisam_update(file,old_data,new_data) ? 0 : my_errno ? my_errno : -1;
 }
 
diff --git a/sql/ha_isammrg.cc b/sql/ha_isammrg.cc
index 20e2b4db423..367607eef19 100644
--- a/sql/ha_isammrg.cc
+++ b/sql/ha_isammrg.cc
@@ -78,8 +78,8 @@ int ha_isammrg::write_row(byte * buf)
 int ha_isammrg::update_row(const byte * old_data, byte * new_data)
 {
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_data+table->timestamp_on_update_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
   return !mrg_update(file,old_data,new_data) ? 0 : my_errno ? my_errno : -1;
 }
 
diff --git a/sql/ha_myisam.cc b/sql/ha_myisam.cc
index 95a294764d3..729ec4c27eb 100644
--- a/sql/ha_myisam.cc
+++ b/sql/ha_myisam.cc
@@ -251,8 +251,8 @@ int ha_myisam::write_row(byte * buf)
   statistic_increment(ha_write_count,&LOCK_status);
 
   /* If we have a timestamp column, update it to the current time */
-  if (table->timestamp_default_now)
-    update_timestamp(buf+table->timestamp_default_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+    table->timestamp_field->set_time();
 
   /*
     If we have an auto_increment column and we are writing a changed row
@@ -1070,8 +1070,8 @@ bool ha_myisam::is_crashed() const
 int ha_myisam::update_row(const byte * old_data, byte * new_data)
 {
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_data+table->timestamp_on_update_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
   return mi_update(file,old_data,new_data);
 }
 
diff --git a/sql/ha_myisammrg.cc b/sql/ha_myisammrg.cc
index 9aa6d039efb..bf4c2a36ffd 100644
--- a/sql/ha_myisammrg.cc
+++ b/sql/ha_myisammrg.cc
@@ -82,8 +82,8 @@ int ha_myisammrg::close(void)
 int ha_myisammrg::write_row(byte * buf)
 {
   statistic_increment(ha_write_count,&LOCK_status);
-  if (table->timestamp_default_now)
-    update_timestamp(buf+table->timestamp_default_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+    table->timestamp_field->set_time();
   if (table->next_number_field && buf == table->record[0])
       update_auto_increment();
   return myrg_write(file,buf);
@@ -92,8 +92,8 @@ int ha_myisammrg::write_row(byte * buf)
 int ha_myisammrg::update_row(const byte * old_data, byte * new_data)
 {
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_data+table->timestamp_on_update_now);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
   return myrg_update(file,old_data,new_data);
 }
 
diff --git a/sql/ha_ndbcluster.cc b/sql/ha_ndbcluster.cc
index 4e474568671..a3ee03f4ace 100644
--- a/sql/ha_ndbcluster.cc
+++ b/sql/ha_ndbcluster.cc
@@ -1533,8 +1533,8 @@ int ha_ndbcluster::write_row(byte *record)
   }
   
   statistic_increment(ha_write_count,&LOCK_status);
-  if (table->timestamp_default_now)
-    update_timestamp(record+table->timestamp_default_now-1);
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_INSERT)
+    table->timestamp_field->set_time();
   has_auto_increment= (table->next_number_field && record == table->record[0]);
 
   if (!(op= trans->getNdbOperation((const NDBTAB *) m_table)))
@@ -1683,9 +1683,9 @@ int ha_ndbcluster::update_row(const byte *old_data, byte *new_data)
   DBUG_ENTER("update_row");
   
   statistic_increment(ha_update_count,&LOCK_status);
-  if (table->timestamp_on_update_now)
-    update_timestamp(new_data+table->timestamp_on_update_now-1);
-  
+  if (table->timestamp_field_type & TIMESTAMP_AUTO_SET_ON_UPDATE)
+    table->timestamp_field->set_time();
+
   /* Check for update of primary key for special handling */  
   if ((table->primary_key != MAX_KEY) &&
       (key_cmp(table->primary_key, old_data, new_data)))
diff --git a/sql/handler.cc b/sql/handler.cc
index 859c7124566..da911ad9172 100644
--- a/sql/handler.cc
+++ b/sql/handler.cc
@@ -942,22 +942,6 @@ int handler::read_first_row(byte * buf, uint primary_key)
 }
 
 
-/* Set a timestamp in record */
-
-void handler::update_timestamp(byte *record)
-{
-  long skr= (long) current_thd->query_start();
-#ifdef WORDS_BIGENDIAN
-  if (table->db_low_byte_first)
-  {
-    int4store(record,skr);
-  }
-  else
-#endif
-  longstore(record,skr);
-  return;
-}
-
 /*
   Updates field with field_type NEXT_NUMBER according to following:
   if field = 0 change field to the next free key in database.
diff --git a/sql/handler.h b/sql/handler.h
index 0b7e9c04381..443b43a5ea3 100644
--- a/sql/handler.h
+++ b/sql/handler.h
@@ -287,7 +287,6 @@ public:
     {}
   virtual ~handler(void) { /* TODO: DBUG_ASSERT(inited == NONE); */ }
   int ha_open(const char *name, int mode, int test_if_locked);
-  void update_timestamp(byte *record);
   void update_auto_increment();
   virtual void print_error(int error, myf errflag);
   virtual bool get_error_message(int error, String *buf);
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 8f09fe82c1b..863b041044e 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -989,7 +989,7 @@ longlong Item_func_unix_timestamp::val_int()
   {						// Optimize timestamp field
     Field *field=((Item_field*) args[0])->field;
     if (field->type() == FIELD_TYPE_TIMESTAMP)
-      return ((Field_timestamp*) field)->get_timestamp();
+      return ((Field_timestamp*) field)->get_timestamp(&null_value);
   }
   
   if (get_arg0_date(&ltime, 0))
diff --git a/sql/sql_base.cc b/sql/sql_base.cc
index 75eb5753e1e..c2a77f9e45e 100644
--- a/sql/sql_base.cc
+++ b/sql/sql_base.cc
@@ -945,7 +945,7 @@ TABLE *open_table(THD *thd,const char *db,const char *table_name,
   table->keys_in_use_for_query= table->keys_in_use;
   table->used_keys= table->keys_for_keyread;
   if (table->timestamp_field)
-    table->timestamp_field->set_timestamp_offsets();
+    table->timestamp_field_type= table->timestamp_field->get_auto_set_type();
   DBUG_ASSERT(table->key_read == 0);
   DBUG_RETURN(table);
 }
diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc
index 4cbd11c6a15..a0496a04bb2 100644
--- a/sql/sql_insert.cc
+++ b/sql/sql_insert.cc
@@ -45,8 +45,8 @@ static void unlink_blobs(register TABLE *table);
 
 /*
   Check if insert fields are correct.
-  Sets table->timestamp_default_now/on_update_now to 0 o leaves it to point
-  to timestamp field, depending on if timestamp should be updated or not.
+  Sets table->timestamp_field_type to TIMESTAMP_NO_AUTO_SET or leaves it
+  as is, depending on if timestamp should be updated or not.
 */
 
 int
@@ -67,7 +67,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
 	check_grant_all_columns(thd,INSERT_ACL,table))
       return -1;
 #endif
-    table->timestamp_default_now= table->timestamp_on_update_now= 0;
+    table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
   }
   else
   {						// Part field list
@@ -97,7 +97,7 @@ check_insert_fields(THD *thd,TABLE *table,List<Item> &fields,
     }
     if (table->timestamp_field &&	// Don't set timestamp if used
 	table->timestamp_field->query_id == thd->query_id)
-      table->timestamp_default_now= table->timestamp_on_update_now= 0;
+      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
   }
   // For the values we need select_priv
 #ifndef NO_EMBEDDED_ACCESS_CHECKS
@@ -569,7 +569,8 @@ int write_record(TABLE *table,COPY_INFO *info)
 	*/
 	if (last_uniq_key(table,key_nr) &&
 	    !table->file->referenced_by_foreign_key() &&
-            table->timestamp_default_now == table->timestamp_on_update_now)
+            (table->timestamp_field_type == TIMESTAMP_NO_AUTO_SET ||
+             table->timestamp_field_type == TIMESTAMP_AUTO_SET_ON_BOTH))
         {
           if ((error=table->file->update_row(table->record[1],
 					     table->record[0])))
@@ -645,8 +646,7 @@ public:
   bool query_start_used,last_insert_id_used,insert_id_used;
   int log_query;
   ulonglong last_insert_id;
-  ulong timestamp_default_now;
-  ulong timestamp_on_update_now;
+  timestamp_auto_set_type timestamp_field_type;
   uint query_length;
 
   delayed_row(enum_duplicates dup_arg, int log_query_arg)
@@ -940,7 +940,7 @@ TABLE *delayed_insert::get_local_table(THD* client_thd)
     copy->timestamp_field=
       (Field_timestamp*) copy->field[table->timestamp_field_offset];
     copy->timestamp_field->unireg_check= table->timestamp_field->unireg_check;
-    copy->timestamp_field->set_timestamp_offsets();
+    copy->timestamp_field_type= copy->timestamp_field->get_auto_set_type();
   }
 
   /* _rowid is not used with delayed insert */
@@ -995,8 +995,7 @@ static int write_delayed(THD *thd,TABLE *table,enum_duplicates duplic,
   row->last_insert_id_used=	thd->last_insert_id_used;
   row->insert_id_used=		thd->insert_id_used;
   row->last_insert_id=		thd->last_insert_id;
-  row->timestamp_default_now=	table->timestamp_default_now;
-  row->timestamp_on_update_now=	table->timestamp_on_update_now;
+  row->timestamp_field_type=    table->timestamp_field_type;
 
   di->rows.push_back(row);
   di->stacked_inserts++;
@@ -1335,8 +1334,7 @@ bool delayed_insert::handle_inserts(void)
     thd.last_insert_id=row->last_insert_id;
     thd.last_insert_id_used=row->last_insert_id_used;
     thd.insert_id_used=row->insert_id_used;
-    table->timestamp_default_now= row->timestamp_default_now;
-    table->timestamp_on_update_now= row->timestamp_on_update_now;
+    table->timestamp_field_type= row->timestamp_field_type;
 
     info.handle_duplicates= row->dup;
     if (info.handle_duplicates == DUP_IGNORE ||
@@ -1631,7 +1629,7 @@ select_create::prepare(List<Item> &values, SELECT_LEX_UNIT *u)
   field=table->field+table->fields - values.elements;
 
   /* Don't set timestamp if used */
-  table->timestamp_default_now= table->timestamp_on_update_now= 0;
+  table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
 
   table->next_number_field=table->found_next_number_field;
 
diff --git a/sql/sql_load.cc b/sql/sql_load.cc
index 78d89ef7aa9..17ab472c87b 100644
--- a/sql/sql_load.cc
+++ b/sql/sql_load.cc
@@ -264,7 +264,7 @@ int mysql_load(THD *thd,sql_exchange *ex,TABLE_LIST *table_list,
   if (!(error=test(read_info.error)))
   {
     if (use_timestamp)
-      table->timestamp_default_now= table->timestamp_on_update_now= 0;
+      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
 
     table->next_number_field=table->found_next_number_field;
     if (handle_duplicates == DUP_IGNORE ||
diff --git a/sql/sql_parse.cc b/sql/sql_parse.cc
index 2c5ec34b867..e8441c05609 100644
--- a/sql/sql_parse.cc
+++ b/sql/sql_parse.cc
@@ -4142,7 +4142,12 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type,
     }
     else if (default_value->type() == Item::NULL_ITEM)
     {
-      default_value=0;
+      /*
+        TIMESTAMP type should be able to distingush non-specified default
+        value and default value NULL later.
+      */
+      if (type != FIELD_TYPE_TIMESTAMP)
+        default_value= 0;
       if ((type_modifier & (NOT_NULL_FLAG | AUTO_INCREMENT_FLAG)) ==
 	  NOT_NULL_FLAG)
       {
@@ -4334,7 +4339,7 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type,
       new_field->length=((new_field->length+1)/2)*2; /* purecov: inspected */
       new_field->length= min(new_field->length,14); /* purecov: inspected */
     }
-    new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG | NOT_NULL_FLAG;
+    new_field->flags|= ZEROFILL_FLAG | UNSIGNED_FLAG;
     if (default_value)
     {
       /* Grammar allows only NOW() value for ON UPDATE clause */
@@ -4352,6 +4357,9 @@ bool add_field_to_list(THD *thd, char *field_name, enum_field_types type,
       else
         new_field->unireg_check= (on_update_value?Field::TIMESTAMP_UN_FIELD:
                                                   Field::NONE);
+
+      if (default_value->type() == Item::NULL_ITEM)
+        new_field->def= 0;
     }
     else
     {
diff --git a/sql/sql_show.cc b/sql/sql_show.cc
index fbb45ce2484..7bef2106f11 100644
--- a/sql/sql_show.cc
+++ b/sql/sql_show.cc
@@ -710,10 +710,11 @@ mysqld_show_fields(THD *thd, TABLE_LIST *table_list,const char *wild,
 	  protocol->store(field->has_charset() ? field->charset()->name : "NULL",
 			system_charset_info);
         /*
-          Altough TIMESTAMP fields can't contain NULL as its value they
+          Even if TIMESTAMP field can't contain NULL as its value it
           will accept NULL if you will try to insert such value and will
-          convert it to current TIMESTAMP. So YES here means that NULL 
-          is allowed for assignment but can't be returned.
+          convert NULL value to current TIMESTAMP. So YES here means
+          that NULL is allowed for assignment (but may be won't be
+          returned).
         */
         pos=(byte*) ((flags & NOT_NULL_FLAG) &&
                      field->type() != FIELD_TYPE_TIMESTAMP ?
@@ -1291,7 +1292,14 @@ store_create_info(THD *thd, TABLE *table, String *packet)
 
     if (flags & NOT_NULL_FLAG)
       packet->append(" NOT NULL", 9);
-
+    else if (field->type() == FIELD_TYPE_TIMESTAMP)
+    {
+      /*
+        TIMESTAMP field require explicit NULL flag, because unlike
+        all other fields they are treated as NOT NULL by default.
+      */
+      packet->append(" NULL", 5);
+    }
 
     /* 
       Again we are using CURRENT_TIMESTAMP instead of NOW because it is
diff --git a/sql/sql_table.cc b/sql/sql_table.cc
index 65e26346834..191351820b5 100644
--- a/sql/sql_table.cc
+++ b/sql/sql_table.cc
@@ -3053,12 +3053,8 @@ int mysql_alter_table(THD *thd,char *new_db, char *new_name,
   }
 
 
-  /*
-    We don't want update TIMESTAMP fields during ALTER TABLE
-    and copy_data_between_tables uses only write_row() for new_table so
-    don't need to set up timestamp_on_update_now member.
-  */
-  new_table->timestamp_default_now= 0;
+  /* We don't want update TIMESTAMP fields during ALTER TABLE. */
+  new_table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
   new_table->next_number_field=new_table->found_next_number_field;
   thd->count_cuted_fields= CHECK_FIELD_WARN;	// calc cuted fields
   thd->cuted_fields=0L;
diff --git a/sql/sql_update.cc b/sql/sql_update.cc
index b6cd0d967e9..c6fb3d6e415 100644
--- a/sql/sql_update.cc
+++ b/sql/sql_update.cc
@@ -116,7 +116,7 @@ int mysql_update(THD *thd,
   {
     // Don't set timestamp column if this is modified
     if (table->timestamp_field->query_id == thd->query_id)
-      table->timestamp_on_update_now= 0;
+      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
     else
       table->timestamp_field->query_id=timestamp_query_id;
   }
@@ -526,7 +526,7 @@ int mysql_multi_update(THD *thd,
     // Only set timestamp column if this is not modified
     if (table->timestamp_field &&
         table->timestamp_field->query_id == thd->query_id)
-      table->timestamp_on_update_now= 0;
+      table->timestamp_field_type= TIMESTAMP_NO_AUTO_SET;
 
     /* if table will be updated then check that it is unique */
     if (table->map & item_tables)
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index 5bf5140d0d8..0c81c172cf7 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -1415,10 +1415,21 @@ type:
 	    if (YYTHD->variables.sql_mode & MODE_MAXDB)
 	      $$=FIELD_TYPE_DATETIME;
 	    else
+            {
+              /* 
+                Unlike other types TIMESTAMP fields are NOT NULL by default.
+              */
+              Lex->type|= NOT_NULL_FLAG;
 	      $$=FIELD_TYPE_TIMESTAMP;
+            }
 	   }
-	| TIMESTAMP '(' NUM ')'		{ Lex->length=$3.str;
-					  $$=FIELD_TYPE_TIMESTAMP; }
+	| TIMESTAMP '(' NUM ')'
+          { 
+            LEX *lex= Lex;
+            lex->length= $3.str;
+            lex->type|= NOT_NULL_FLAG;
+            $$= FIELD_TYPE_TIMESTAMP;
+          }
 	| DATETIME			{ $$=FIELD_TYPE_DATETIME; }
 	| TINYBLOB			{ Lex->charset=&my_charset_bin;
 					  $$=FIELD_TYPE_TINY_BLOB; }
diff --git a/sql/table.h b/sql/table.h
index f111377bc85..904038ad029 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -57,6 +57,16 @@ typedef struct st_filesort_info
 } FILESORT_INFO;
 
 
+/*
+  Values in this enum are used to indicate during which operations value
+  of TIMESTAMP field should be set to current timestamp.
+*/
+enum timestamp_auto_set_type
+{
+  TIMESTAMP_NO_AUTO_SET= 0, TIMESTAMP_AUTO_SET_ON_INSERT= 1,
+  TIMESTAMP_AUTO_SET_ON_UPDATE= 2, TIMESTAMP_AUTO_SET_ON_BOTH= 3
+};
+
 /* Table cache entry struct */
 
 class Field_timestamp;
@@ -99,16 +109,19 @@ struct st_table {
   uint status;				/* Used by postfix.. */
   uint system;				/* Set if system record */
 
-  /* 
-    These two members hold offset in record + 1 for TIMESTAMP field
-    with NOW() as default value or/and with ON UPDATE NOW() option. 
-    If 0 then such field is absent in this table or auto-set for default
-    or/and on update should be temporaly disabled for some reason.
-    These values is setup to offset value for each statement in open_table()
-    and turned off in statement processing code (see mysql_update as example).
+  /*
+    If this table has TIMESTAMP field with auto-set property (pointed by
+    timestamp_field member) then this variable indicates during which
+    operations (insert only/on update/in both cases) we should set this
+    field to current timestamp. If there are no such field in this table
+    or we should not automatically set its value during execution of current
+    statement then the variable contains TIMESTAMP_NO_AUTO_SET (i.e. 0).
+
+    Value of this variable is set for each statement in open_table() and
+    if needed cleared later in statement processing code (see mysql_update()
+    as example).
   */
-  ulong timestamp_default_now;
-  ulong timestamp_on_update_now;
+  timestamp_auto_set_type timestamp_field_type;
   /* Index of auto-updated TIMESTAMP field in field array */
   uint timestamp_field_offset;