List:Internals« Previous MessageNext Message »
From:Jim Winstead Date:August 24 2005 10:51pm
Subject:bk commit into 5.0 tree (jimw:1.1909) BUG#12562
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of jimw. When jimw 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
  1.1909 05/08/24 15:50:58 jimw@stripped +6 -0
  Make SYSDATE() behave as in Oracle: always the current datetime, not the
  datetime of when the current statement began. This also makes SYSDATE()
  not safe in replication. (Bug #12562)

  sql/sql_yacc.yy
    1.417 05/08/24 15:50:54 jimw@stripped +5 -0
    Handle SYSDATE()

  sql/lex.h
    1.140 05/08/24 15:50:54 jimw@stripped +1 -1
    SYSDATE() is no longer an alias for NOW().

  sql/item_timefunc.h
    1.58 05/08/24 15:50:54 jimw@stripped +27 -0
    Add Item_func_sysdate_local, so SYSDATE() can behave differently
    than NOW().

  sql/item_timefunc.cc
    1.93 05/08/24 15:50:54 jimw@stripped +66 -2
    Add Item_func_sysdate_local implementation

  mysql-test/t/func_time.test
    1.37 05/08/24 15:50:54 jimw@stripped +52 -0
    Add tests for new SYSDATE() behavior

  mysql-test/r/func_time.result
    1.45 05/08/24 15:50:54 jimw@stripped +40 -0
    Add new results

# 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:	jimw
# Host:	rama.(none)
# Root:	/home/jimw/my/mysql-5.0-12562

--- 1.92/sql/item_timefunc.cc	2005-08-02 15:28:05 -07:00
+++ 1.93/sql/item_timefunc.cc	2005-08-24 15:50:54 -07:00
@@ -1476,15 +1476,79 @@
 
 
 bool Item_func_now::get_date(TIME *res,
-			     uint fuzzy_date __attribute__((unused)))
+                             uint fuzzy_date __attribute__((unused)))
 {
-  *res=ltime;
+  *res= ltime;
   return 0;
 }
 
 
 int Item_func_now::save_in_field(Field *to, bool no_conversions)
 {
+  to->set_notnull();
+  to->store_time(&ltime, MYSQL_TIMESTAMP_DATETIME);
+  return 0;
+}
+
+
+/*
+    Converts current time in my_time_t to TIME represenatation for local
+    time zone. Defines time zone (local) used for whole SYSDATE function.
+*/
+void Item_func_sysdate_local::store_now_in_TIME(TIME *now_time)
+{
+  THD *thd= current_thd;
+  thd->variables.time_zone->gmt_sec_to_TIME(now_time, time(NULL));
+  thd->time_zone_used= 1;
+}
+
+
+String *Item_func_sysdate_local::val_str(String *str)
+{
+  DBUG_ASSERT(fixed == 1);
+  store_now_in_TIME(&ltime);
+  buff_length= (uint) my_datetime_to_str(&ltime, buff);
+  str_value.set(buff, buff_length, &my_charset_bin);
+  return &str_value;
+}
+
+
+longlong Item_func_sysdate_local::val_int()
+{
+  DBUG_ASSERT(fixed == 1);
+  store_now_in_TIME(&ltime);
+  return (longlong) TIME_to_ulonglong_datetime(&ltime);
+}
+
+
+double Item_func_sysdate_local::val_real()
+{
+  DBUG_ASSERT(fixed == 1);
+  store_now_in_TIME(&ltime);
+  return (longlong) TIME_to_ulonglong_datetime(&ltime);
+}
+
+
+void Item_func_sysdate_local::fix_length_and_dec()
+{
+  decimals= 0;
+  collation.set(&my_charset_bin);
+  max_length= MAX_DATETIME_WIDTH*MY_CHARSET_BIN_MB_MAXLEN;
+}
+
+
+bool Item_func_sysdate_local::get_date(TIME *res,
+                                       uint fuzzy_date __attribute__((unused)))
+{
+  store_now_in_TIME(&ltime);
+  *res= ltime;
+  return 0;
+}
+
+
+int Item_func_sysdate_local::save_in_field(Field *to, bool no_conversions)
+{
+  store_now_in_TIME(&ltime);
   to->set_notnull();
   to->store_time(&ltime, MYSQL_TIMESTAMP_DATETIME);
   return 0;

--- 1.57/sql/item_timefunc.h	2005-08-12 11:07:57 -07:00
+++ 1.58/sql/item_timefunc.h	2005-08-24 15:50:54 -07:00
@@ -446,6 +446,7 @@
 
 class Item_func_now :public Item_date_func
 {
+protected:
   longlong value;
   char buff[20*2+32];	// +32 to make my_snprintf_{8bit|ucs2} happy
   uint buff_length;
@@ -482,6 +483,32 @@
   Item_func_now_utc(Item *a) :Item_func_now(a) {}
   const char *func_name() const { return "utc_timestamp"; }
   virtual void store_now_in_TIME(TIME *now_time);
+};
+
+
+/*
+  This is like NOW(), but always uses the real current time, not the
+  query_start(). This matches the Oracle behavior.
+*/
+class Item_func_sysdate_local :public Item_func_now
+{
+public:
+  Item_func_sysdate_local() :Item_func_now() {}
+  Item_func_sysdate_local(Item *a) :Item_func_now(a) {}
+  bool const_item() const { return 0; }
+  const char *func_name() const { return "sysdate"; }
+  void store_now_in_TIME(TIME *now_time);
+  double val_real();
+  longlong val_int();
+  int save_in_field(Field *to, bool no_conversions);
+  String *val_str(String *str);
+  void fix_length_and_dec();
+  bool get_date(TIME *res, uint fuzzy_date);
+  void update_used_tables()
+  {
+    Item_func_now::update_used_tables();
+    used_tables_cache|= RAND_TABLE_BIT;
+  }
 };
 
 

--- 1.139/sql/lex.h	2005-08-11 18:58:18 -07:00
+++ 1.140/sql/lex.h	2005-08-24 15:50:54 -07:00
@@ -751,7 +751,7 @@
   { "SUBSTRING_INDEX",	SYM(SUBSTRING_INDEX)},
   { "SUBTIME",          F_SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_subtime)},
   { "SUM",		SYM(SUM_SYM)},
-  { "SYSDATE",		SYM(NOW_SYM)},
+  { "SYSDATE",		SYM(SYSDATE)},
   { "SYSTEM_USER",	SYM(USER)},
   { "TAN",		F_SYM(FUNC_ARG1),0,CREATE_FUNC(create_func_tan)},
   { "TIME_FORMAT",	F_SYM(FUNC_ARG2),0,CREATE_FUNC(create_func_time_format)},

--- 1.416/sql/sql_yacc.yy	2005-08-20 03:38:07 -07:00
+++ 1.417/sql/sql_yacc.yy	2005-08-24 15:50:54 -07:00
@@ -580,6 +580,7 @@
 %token  SUM_SYM
 %token  SUPER_SYM
 %token  SUSPEND_SYM
+%token  SYSDATE
 %token  TABLES
 %token  TABLESPACE
 %token  TABLE_SYM
@@ -4683,6 +4684,10 @@
 	  { $$= new Item_func_substr($3,$5); }
 	| SUBSTRING_INDEX '(' expr ',' expr ',' expr ')'
 	  { $$= new Item_func_substr_index($3,$5,$7); }
+	| SYSDATE optional_braces
+	  { $$= new Item_func_sysdate_local(); Lex->safe_to_cache_query=0;}
+	| SYSDATE '(' expr ')'
+	  { $$= new Item_func_sysdate_local($3); Lex->safe_to_cache_query=0;}
 	| TIME_SYM '(' expr ')'
 	  { $$= new Item_time_typecast($3); }
 	| TIMESTAMP '(' expr ')'

--- 1.44/mysql-test/r/func_time.result	2005-08-15 14:19:53 -07:00
+++ 1.45/mysql-test/r/func_time.result	2005-08-24 15:50:54 -07:00
@@ -720,3 +720,43 @@
 select time_format('100:00:00', '%H %k %h %I %l');
 time_format('100:00:00', '%H %k %h %I %l')
 100 100 04 04 4
+create table t1 (a timestamp default '2005-05-05 01:01:01',
+b timestamp default '2005-05-05 01:01:01');
+create function t_slow_sysdate() returns timestamp
+begin
+do sleep(2);
+return sysdate();
+end;
+//
+insert into t1 set a = sysdate(), b = t_slow_sysdate();//
+create trigger t_before before insert on t1
+for each row begin
+set new.b = t_slow_sysdate();
+end
+//
+insert into t1 set a = sysdate();
+select a != b from t1;
+a != b
+1
+1
+drop trigger t_before;
+drop function t_slow_sysdate;
+drop table t1;
+create table t1 (a datetime, i int, b datetime);
+insert into t1 select sysdate(), sleep(1), sysdate() from dual;
+select a != b from t1;
+a != b
+1
+drop table t1;
+create procedure t_sysdate()
+begin
+select sysdate() into @a;
+do sleep(2);
+select sysdate() into @b;
+select @a != @b;
+end;
+//
+call t_sysdate();
+@a != @b
+1
+drop procedure t_sysdate;

--- 1.36/mysql-test/t/func_time.test	2005-07-28 07:09:48 -07:00
+++ 1.37/mysql-test/t/func_time.test	2005-08-24 15:50:54 -07:00
@@ -353,3 +353,55 @@
 # the 0-11 range
 #
 select time_format('100:00:00', '%H %k %h %I %l');
+
+#
+# Bug #12562: Make SYSDATE behave like it does in Oracle: always the current
+#             time, regardless of magic to make NOW() always the same for the
+#             entirety of a statement.
+create table t1 (a timestamp default '2005-05-05 01:01:01',
+                 b timestamp default '2005-05-05 01:01:01');
+delimiter //;
+create function t_slow_sysdate() returns timestamp
+begin
+  do sleep(2);
+  return sysdate();
+end;
+//
+
+insert into t1 set a = sysdate(), b = t_slow_sysdate();//
+
+create trigger t_before before insert on t1
+for each row begin
+  set new.b = t_slow_sysdate();
+end
+//
+
+delimiter ;//
+
+insert into t1 set a = sysdate();
+
+select a != b from t1;
+
+drop trigger t_before;
+drop function t_slow_sysdate;
+drop table t1;
+
+create table t1 (a datetime, i int, b datetime);
+insert into t1 select sysdate(), sleep(1), sysdate() from dual;
+select a != b from t1;
+drop table t1;
+
+delimiter //;
+create procedure t_sysdate()
+begin
+  select sysdate() into @a;
+  do sleep(2);
+  select sysdate() into @b;
+  select @a != @b;
+end;
+//
+delimiter ;//
+call t_sysdate();
+drop procedure t_sysdate;
+
+# End of 5.0 tests
Thread
bk commit into 5.0 tree (jimw:1.1909) BUG#12562Jim Winstead25 Aug