summaryrefslogtreecommitdiff
diff options
context:
space:
mode:
-rw-r--r--mysql-test/r/func_time.result40
-rw-r--r--mysql-test/t/func_time.test52
-rw-r--r--sql/item_timefunc.cc68
-rw-r--r--sql/item_timefunc.h27
-rw-r--r--sql/lex.h2
-rw-r--r--sql/sql_yacc.yy5
6 files changed, 191 insertions, 3 deletions
diff --git a/mysql-test/r/func_time.result b/mysql-test/r/func_time.result
index e51dc113f97..87aa4b98d81 100644
--- a/mysql-test/r/func_time.result
+++ b/mysql-test/r/func_time.result
@@ -720,3 +720,43 @@ Warning 1292 Truncated incorrect datetime value: '2005-01-00'
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;
diff --git a/mysql-test/t/func_time.test b/mysql-test/t/func_time.test
index 0538e6111b9..3dd7f7276fb 100644
--- a/mysql-test/t/func_time.test
+++ b/mysql-test/t/func_time.test
@@ -353,3 +353,55 @@ select last_day('2005-01-00');
# 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
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index dfbfca3b078..0d9e23ff0a1 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -1476,9 +1476,9 @@ void Item_func_now_utc::store_now_in_TIME(TIME *now_time)
bool Item_func_now::get_date(TIME *res,
- uint fuzzy_date __attribute__((unused)))
+ uint fuzzy_date __attribute__((unused)))
{
- *res=ltime;
+ *res= ltime;
return 0;
}
@@ -1491,6 +1491,70 @@ int Item_func_now::save_in_field(Field *to, bool no_conversions)
}
+/*
+ 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;
+}
+
+
String *Item_func_sec_to_time::val_str(String *str)
{
DBUG_ASSERT(fixed == 1);
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index d9300451fe7..4602088a5f5 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -446,6 +446,7 @@ public:
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;
@@ -485,6 +486,32 @@ public:
};
+/*
+ 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;
+ }
+};
+
+
class Item_func_from_days :public Item_date
{
public:
diff --git a/sql/lex.h b/sql/lex.h
index 7b6d86e327e..4bf3671e5be 100644
--- a/sql/lex.h
+++ b/sql/lex.h
@@ -751,7 +751,7 @@ static SYMBOL sql_functions[] = {
{ "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)},
diff --git a/sql/sql_yacc.yy b/sql/sql_yacc.yy
index b446a06ded1..6b447396dec 100644
--- a/sql/sql_yacc.yy
+++ b/sql/sql_yacc.yy
@@ -580,6 +580,7 @@ bool my_yyoverflow(short **a, YYSTYPE **b, ulong *yystacksize);
%token SUM_SYM
%token SUPER_SYM
%token SUSPEND_SYM
+%token SYSDATE
%token TABLES
%token TABLESPACE
%token TABLE_SYM
@@ -4683,6 +4684,10 @@ simple_expr:
{ $$= 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 ')'