fix for bug#16537 (Events: mysql.event.starts is null)

- now when the event is created and STARTS is omitted then STARTS is implicitly
  CURRENT_TIMESTAMP
- This CS also fixed incorrect presentation of STARTS/ENDS in I_S.EVENTS
(incorporated review changes)


mysql-test/r/events.result:
  results of new test cases
mysql-test/t/events.test:
  new test cases for bug #16537 (Events: mysql.event.starts is null)
sql/event.cc:
  - check whether event_timed::starts_null only in case
    event_timed::expression is set, so for recurring events only
  - disable binlogging of CREATE EVENT statement. It should not be
    replicated but the result of the execution. Still the replication is
    untouched topic.
sql/event.h:
  - add flags whether starts, ends and execute_at are null or not
sql/event_executor.cc:
  - check whether xxx_null instead of !xxxx.year
sql/event_timed.cc:
  - introduce xxx_null and change the usage of xxx.year to !xxx_null
sql/sql_show.cc:
  - don't show 0000-00-00 in I_S.EVENTS when the value is NULL
sql/sql_yacc.yy:
  - if STARTS is omitted default to current_timestamp

1 files changed, 78 insertions, 39 deletions

diff --git a/sql/event_timed.cc b/sql/event_timed.cc
index 28d21089b74..05be47cc005 100644
--- a/sql/event_timed.cc
+++ b/sql/event_timed.cc
@@ -41,6 +41,7 @@ event_timed::init()
   set_zero_time(&ends, MYSQL_TIMESTAMP_DATETIME);
   set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME);
   set_zero_time(&last_executed, MYSQL_TIMESTAMP_DATETIME);
+  starts_null= ends_null= execute_at_null= TRUE;
 
   definer_user.str= definer_host.str= 0;
   definer_user.length= definer_host.length= 0;
@@ -141,14 +142,18 @@ event_timed::init_execute_at(THD *thd, Item *expr)
 {
   my_bool not_used;
   TIME ltime;
-  my_time_t my_time_tmp;
 
   TIME time_tmp;
   DBUG_ENTER("event_timed::init_execute_at");
 
   if (expr->fix_fields(thd, &expr))
     DBUG_RETURN(EVEX_PARSE_ERROR);
-
+  
+  /* no starts and/or ends in case of execute_at */
+  DBUG_PRINT("info", ("starts_null && ends_null should be 1 is %d",
+              (starts_null && ends_null)))
+  DBUG_ASSERT(starts_null && ends_null);
+  
   // let's check whether time is in the past
   thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp, 
                               (my_time_t) thd->query_start()); 
@@ -161,14 +166,13 @@ event_timed::init_execute_at(THD *thd, Item *expr)
       TIME_to_ulonglong_datetime(&time_tmp))
     DBUG_RETURN(EVEX_BAD_PARAMS);
 
-
   /*
       This may result in a 1970-01-01 date if ltime is > 2037-xx-xx
       CONVERT_TZ has similar problem
   */
   my_tz_UTC->gmt_sec_to_TIME(&ltime, TIME_to_timestamp(thd,&ltime, &not_used));
 
-
+  execute_at_null= FALSE;
   execute_at= ltime;
   DBUG_RETURN(0);
 }
@@ -303,7 +307,7 @@ event_timed::init_starts(THD *thd, Item *new_starts)
   if ((not_used= new_starts->get_date(&ltime, TIME_NO_ZERO_DATE)))
     DBUG_RETURN(EVEX_BAD_PARAMS);
 
-  // let's check whether time is in the past
+  /* let's check whether time is in the past */
   thd->variables.time_zone->gmt_sec_to_TIME(&time_tmp, 
                               (my_time_t) thd->query_start()); 
 
@@ -320,6 +324,7 @@ event_timed::init_starts(THD *thd, Item *new_starts)
   my_tz_UTC->gmt_sec_to_TIME(&ltime, TIME_to_timestamp(thd, &ltime, &not_used));
 
   starts= ltime;
+  starts_null= FALSE;
   DBUG_RETURN(0);
 }
 
@@ -329,7 +334,7 @@ event_timed::init_starts(THD *thd, Item *new_starts)
 
  SYNOPSIS
    event_timed::init_ends()
-     thd      THD
+     thd       THD
      new_ends  when?
 
  NOTES
@@ -342,15 +347,14 @@ event_timed::init_starts(THD *thd, Item *new_starts)
 
  RETURNS
    0 - OK
-   EVEX_PARSE_ERROR - fix_fields failed
-   EVEX_BAD_PARAMS  - ENDS before STARTS
+   EVEX_PARSE_ERROR  fix_fields failed
+   EVEX_BAD_PARAMS   ENDS before STARTS
 */
 
 int 
 event_timed::init_ends(THD *thd, Item *new_ends)
 {
-  TIME ltime;
-  my_time_t my_time_tmp;
+  TIME ltime, ltime_now;
   my_bool not_used;
 
   DBUG_ENTER("event_timed::init_ends");
@@ -358,20 +362,34 @@ event_timed::init_ends(THD *thd, Item *new_ends)
   if (new_ends->fix_fields(thd, &new_ends))
     DBUG_RETURN(EVEX_PARSE_ERROR);
 
-  // the field was already fixed in init_ends
+  DBUG_PRINT("info", ("convert to TIME"));
   if ((not_used= new_ends->get_date(&ltime, TIME_NO_ZERO_DATE)))
     DBUG_RETURN(EVEX_BAD_PARAMS);
 
   /*
-    This may result in a 1970-01-01 date if ltime is > 2037-xx-xx
-    CONVERT_TZ has similar problem
+    This may result in a 1970-01-01 date if ltime is > 2037-xx-xx ?
+    CONVERT_TZ has similar problem ?
   */
+  DBUG_PRINT("info", ("get the UTC time"));
   my_tz_UTC->gmt_sec_to_TIME(&ltime, TIME_to_timestamp(thd, &ltime, &not_used));
- 
-  if (starts.year && my_time_compare(&starts, &ltime) != -1)
+
+  /* Check whether ends is after starts */
+  DBUG_PRINT("info", ("ENDS after STARTS?"));
+  if (!starts_null && my_time_compare(&starts, &ltime) != -1)
+    DBUG_RETURN(EVEX_BAD_PARAMS);
+
+  /*
+    The parser forces starts to be provided but one day STARTS could be
+    set before NOW() and in this case the following check should be done.
+    Check whether ENDS is not in the past.
+  */
+  DBUG_PRINT("info", ("ENDS after NOW?"));
+  my_tz_UTC->gmt_sec_to_TIME(&ltime_now, thd->query_start());  
+  if (my_time_compare(&ltime_now, &ltime) == 1)
     DBUG_RETURN(EVEX_BAD_PARAMS);
 
   ends= ltime;
+  ends_null= FALSE;
   DBUG_RETURN(0);
 }
 
@@ -391,7 +409,7 @@ event_timed::init_comment(THD *thd, LEX_STRING *set_comment)
   DBUG_ENTER("event_timed::init_comment");
 
   comment.str= strmake_root(thd->mem_root, set_comment->str,
-                              comment.length= set_comment->length);
+                            comment.length= set_comment->length);
 
   DBUG_VOID_RETURN;
 }
@@ -498,28 +516,37 @@ event_timed::load_from_row(MEM_ROOT *mem_root, TABLE *table)
   et->definer_host.str= strmake_root(mem_root, ptr + 1, len);//1: because of @
   et->definer_host.length= len;
   
-  res1= table->field[EVEX_FIELD_STARTS]->
-                     get_date(&et->starts, TIME_NO_ZERO_DATE);
+  et->starts_null= table->field[EVEX_FIELD_STARTS]->is_null();
+  res1= table->field[EVEX_FIELD_STARTS]->get_date(&et->starts,TIME_NO_ZERO_DATE);
 
-  res2= table->field[EVEX_FIELD_ENDS]->
-                     get_date(&et->ends, TIME_NO_ZERO_DATE);
+  et->ends_null= table->field[EVEX_FIELD_ENDS]->is_null();
+  res2= table->field[EVEX_FIELD_ENDS]->get_date(&et->ends, TIME_NO_ZERO_DATE);
   
-  et->expression= table->field[EVEX_FIELD_INTERVAL_EXPR]->val_int();
-
+  if (!table->field[EVEX_FIELD_INTERVAL_EXPR]->is_null())
+    et->expression= table->field[EVEX_FIELD_INTERVAL_EXPR]->val_int();
+  else
+    et->expression= 0;
   /*
     If res1 and res2 are true then both fields are empty.
 	Hence if EVEX_FIELD_EXECUTE_AT is empty there is an error.
   */
-  if (res1 && res2 && !et->expression && table->field[EVEX_FIELD_EXECUTE_AT]->
-                get_date(&et->execute_at, TIME_NO_ZERO_DATE))
+  et->execute_at_null= table->field[EVEX_FIELD_EXECUTE_AT]->is_null();
+  DBUG_ASSERT(!(et->starts_null && et->ends_null && !et->expression &&
+              et->execute_at_null));
+  if (!et->expression &&
+      table->field[EVEX_FIELD_EXECUTE_AT]->get_date(&et->execute_at,
+                                                     TIME_NO_ZERO_DATE))
     goto error;
 
   /*
     In DB the values start from 1 but enum interval_type starts
     from 0
   */
-  et->interval= (interval_type)
+  if (!table->field[EVEX_FIELD_TRANSIENT_INTERVAL]->is_null())
+    et->interval= (interval_type)
        ((ulonglong) table->field[EVEX_FIELD_TRANSIENT_INTERVAL]->val_int() - 1);
+  else
+    et->interval= (interval_type) 0;
 
   et->modified= table->field[EVEX_FIELD_CREATED]->val_int();
   et->created= table->field[EVEX_FIELD_MODIFIED]->val_int();
@@ -676,14 +703,11 @@ event_timed::compute_next_execution_time()
     //let's check whether it was executed
     if (last_executed.year)
     {
-      DBUG_PRINT("compute_next_execution_time",
-                ("One-time event %s was already executed", name.str));
-      if (on_completion == MYSQL_EVENT_ON_COMPLETION_DROP)
-      {
-        DBUG_PRINT("compute_next_execution_time",
-                          ("One-time event will be dropped."));
-        dropped= true;
-      }
+      DBUG_PRINT("info",("One-time event %s.%s of was already executed",
+                         dbname.str, name.str, definer.str));
+      dropped= (on_completion == MYSQL_EVENT_ON_COMPLETION_DROP);
+      DBUG_PRINT("info",("One-time event will be dropped=%d.", dropped));
+
       status= MYSQL_EVENT_DISABLED;
       status_changed= true;
     }
@@ -710,10 +734,11 @@ event_timed::compute_next_execution_time()
 #endif
 
   //if time_now is after ends don't execute anymore
-  if (ends.year && (tmp= my_time_compare(&ends, &time_now)) == -1)
+  if (!ends_null && (tmp= my_time_compare(&ends, &time_now)) == -1)
   {
     // time_now is after ends. don't execute anymore
     set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME);
+    execute_at_null= TRUE;
     if (on_completion == MYSQL_EVENT_ON_COMPLETION_DROP)
       dropped= true;
     status= MYSQL_EVENT_DISABLED;
@@ -727,7 +752,7 @@ event_timed::compute_next_execution_time()
      Let's check whether time_now is before starts.
      If so schedule for starts
   */
-  if (starts.year && (tmp= my_time_compare(&time_now, &starts)) < 1)
+  if (!starts_null && (tmp= my_time_compare(&time_now, &starts)) < 1)
   {
     if (tmp == 0 && my_time_compare(&starts, &last_executed) == 0)
     {
@@ -743,11 +768,12 @@ event_timed::compute_next_execution_time()
         time_now before starts. Scheduling for starts
       */
       execute_at= starts;
+      execute_at_null= FALSE;
       goto ret;
     }
   }
   
-  if (starts.year && ends.year)
+  if (!starts_null && !ends_null)
   {
     /* 
       Both starts and m_ends are set and time_now is between them (incl.)
@@ -756,7 +782,10 @@ event_timed::compute_next_execution_time()
       If not set then schedule for now.
     */
     if (!last_executed.year)
+    {
       execute_at= time_now;
+      execute_at_null= FALSE;
+    }
     else
     {
       TIME next_exec;
@@ -769,15 +798,19 @@ event_timed::compute_next_execution_time()
       {
         // Next execution after ends. No more executions
         set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME);
+        execute_at_null= TRUE;
         if (on_completion == MYSQL_EVENT_ON_COMPLETION_DROP)
           dropped= true;
       }
       else
+      {
         execute_at= next_exec;
+        execute_at_null= FALSE;
+      }
     }
     goto ret;
   }
-  else if (!starts.year && !ends.year)
+  else if (starts_null && ends_null)
   {
     // both starts and m_ends are not set, se we schedule for the next
     // based on last_executed
@@ -789,11 +822,12 @@ event_timed::compute_next_execution_time()
     else
        //last_executed not set. Schedule the event for now
       execute_at= time_now;
+    execute_at_null= FALSE;
   }
   else
   {
     //either starts or m_ends is set
-    if (starts.year)
+    if (!starts_null)
     {
       /*
         - starts is set.
@@ -808,6 +842,7 @@ event_timed::compute_next_execution_time()
       }
       else
         execute_at= starts;
+      execute_at_null= FALSE;
     }
     else
     {
@@ -830,11 +865,15 @@ event_timed::compute_next_execution_time()
         if (my_time_compare(&ends, &next_exec) == -1)
         {
           set_zero_time(&execute_at, MYSQL_TIMESTAMP_DATETIME);
+          execute_at_null= TRUE;
           if (on_completion == MYSQL_EVENT_ON_COMPLETION_DROP)
             dropped= true;
         }
         else
+        {
           execute_at= next_exec;
+          execute_at_null= FALSE;
+        }
       }
     }
     goto ret;
@@ -1025,7 +1064,7 @@ event_timed::execute(THD *thd, MEM_ROOT *mem_root)
   
   if (!sphead && (ret= compile(thd, mem_root)))
     goto done;
-  
+ 
   ret= sphead->execute_procedure(thd, &empty_item_list);
 
   VOID(pthread_mutex_lock(&this->LOCK_running));