diff options
| -rw-r--r-- | mysql-test/r/type_timestamp.result | 29 | ||||
| -rw-r--r-- | mysql-test/t/type_timestamp.test | 20 | ||||
| -rw-r--r-- | sql/field.cc | 47 | ||||
| -rw-r--r-- | sql/field.h | 8 | ||||
| -rw-r--r-- | sql/field_conv.cc | 3 | ||||
| -rw-r--r-- | sql/ha_berkeley.cc | 8 | ||||
| -rw-r--r-- | sql/ha_heap.cc | 8 | ||||
| -rw-r--r-- | sql/ha_innodb.cc | 8 | ||||
| -rw-r--r-- | sql/ha_isam.cc | 8 | ||||
| -rw-r--r-- | sql/ha_isammrg.cc | 4 | ||||
| -rw-r--r-- | sql/ha_myisam.cc | 8 | ||||
| -rw-r--r-- | sql/ha_myisammrg.cc | 8 | ||||
| -rw-r--r-- | sql/ha_ndbcluster.cc | 10 | ||||
| -rw-r--r-- | sql/handler.cc | 16 | ||||
| -rw-r--r-- | sql/handler.h | 1 | ||||
| -rw-r--r-- | sql/item_timefunc.cc | 2 | ||||
| -rw-r--r-- | sql/sql_base.cc | 2 | ||||
| -rw-r--r-- | sql/sql_insert.cc | 24 | ||||
| -rw-r--r-- | sql/sql_load.cc | 2 | ||||
| -rw-r--r-- | sql/sql_parse.cc | 12 | ||||
| -rw-r--r-- | sql/sql_show.cc | 16 | ||||
| -rw-r--r-- | sql/sql_table.cc | 8 | ||||
| -rw-r--r-- | sql/sql_update.cc | 4 | ||||
| -rw-r--r-- | sql/sql_yacc.yy | 15 | ||||
| -rw-r--r-- | sql/table.h | 31 |
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(<ime, 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; |