MDEV-16973 Application-time periods: DELETE

* inject portion of time updates into mysql_delete main loop
* triggered case emits delete+insert, no updates
* PORTION OF `SYSTEM_TIME` is forbidden
* `DELETE HISTORY .. FOR PORTION OF ...` is forbidden as well

1 files changed, 115 insertions, 9 deletions

diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc
index 1630d335559..a9109ce67c8 100644
--- a/sql/sql_delete.cc
+++ b/sql/sql_delete.cc
@@ -245,6 +245,50 @@ static bool record_should_be_deleted(THD *thd, TABLE *table, SQL_SELECT *sel,
   return false;
 }
 
+static
+int update_portion_of_time(THD *thd, TABLE *table,
+                           const vers_select_conds_t &period_conds,
+                           bool *inside_period)
+{
+  bool lcond= period_conds.field_start->val_datetime_packed(thd)
+              < period_conds.start.item->val_datetime_packed(thd);
+  bool rcond= period_conds.field_end->val_datetime_packed(thd)
+              > period_conds.end.item->val_datetime_packed(thd);
+
+  *inside_period= !lcond && !rcond;
+  if (*inside_period)
+    return 0;
+
+  DBUG_ASSERT(!table->triggers
+              || !table->triggers->has_triggers(TRG_EVENT_INSERT,
+                                                TRG_ACTION_BEFORE));
+
+  int res= 0;
+  Item *src= lcond ? period_conds.start.item : period_conds.end.item;
+  uint dst_fieldno= lcond ? table->s->period.end_fieldno
+                          : table->s->period.start_fieldno;
+
+  store_record(table, record[1]);
+  if (likely(!res))
+    res= src->save_in_field(table->field[dst_fieldno], true);
+
+  if (likely(!res))
+    res= table->update_generated_fields();
+
+  if(likely(!res))
+    res= table->file->ha_update_row(table->record[1], table->record[0]);
+
+  if (likely(!res) && table->triggers)
+    res= table->triggers->process_triggers(thd, TRG_EVENT_INSERT,
+                                           TRG_ACTION_AFTER, true);
+  restore_record(table, record[1]);
+
+  if (likely(!res) && lcond && rcond)
+    res= table->period_make_insert(period_conds.end.item,
+                                   table->field[table->s->period.start_fieldno]);
+
+  return res;
+}
 
 inline
 int TABLE::delete_row()
@@ -287,7 +331,7 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   bool		return_error= 0;
   ha_rows	deleted= 0;
   bool          reverse= FALSE;
-  bool          has_triggers;
+  bool          has_triggers= false;
   ORDER *order= (ORDER *) ((order_list && order_list->elements) ?
                            order_list->first : NULL);
   SELECT_LEX   *select_lex= thd->lex->first_select_lex();
@@ -298,7 +342,9 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   Explain_delete *explain;
   Delete_plan query_plan(thd->mem_root);
   Unique * deltempfile= NULL;
-  bool delete_record, delete_while_scanning;
+  bool delete_record= false;
+  bool delete_while_scanning;
+  bool portion_of_time_through_update;
   DBUG_ENTER("mysql_delete");
 
   query_plan.index= MAX_KEY;
@@ -313,6 +359,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   bool truncate_history= table_list->vers_conditions.is_set();
   if (truncate_history)
   {
+    DBUG_ASSERT(!table_list->period_conditions.is_set());
+
     if (table_list->is_view_or_derived())
     {
       my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str);
@@ -332,6 +380,18 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       table_list->on_expr= NULL;
     }
   }
+  if (table_list->has_period())
+  {
+    if (table_list->is_view_or_derived())
+    {
+      my_error(ER_IT_IS_A_VIEW, MYF(0), table_list->table_name.str);
+      DBUG_RETURN(true);
+    }
+
+    conds= select_lex->period_setup_conds(thd, table_list, conds);
+    if (!conds)
+      DBUG_RETURN(true);
+  }
 
   if (mysql_handle_list_of_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT))
     DBUG_RETURN(TRUE);
@@ -423,12 +483,12 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
       - there should be no delete triggers associated with the table.
   */
 
-  has_triggers= (table->triggers &&
-                 table->triggers->has_delete_triggers());
+  has_triggers= table->triggers && table->triggers->has_delete_triggers();
+
   if (!with_select && !using_limit && const_cond_result &&
       (!thd->is_current_stmt_binlog_format_row() &&
        !has_triggers)
-      && !table->versioned(VERS_TIMESTAMP))
+      && !table->versioned(VERS_TIMESTAMP) && !table_list->has_period())
   {
     /* Update the table->file->stats.records number */
     table->file->info(HA_STATUS_VARIABLE | HA_STATUS_NO_LOCK);
@@ -598,7 +658,8 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   */
 
   if ((table->file->ha_table_flags() & HA_CAN_DIRECT_UPDATE_AND_DELETE) &&
-      !has_triggers && !binlog_is_row && !with_select)
+      !has_triggers && !binlog_is_row && !with_select &&
+      !table_list->has_period())
   {
     table->mark_columns_needed_for_delete();
     if (!table->check_virtual_columns_marked_for_read())
@@ -668,7 +729,10 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
   if (unlikely(init_ftfuncs(thd, select_lex, 1)))
     goto got_error;
 
-  table->mark_columns_needed_for_delete();
+  if (table_list->has_period())
+    table->use_all_columns();
+  else
+    table->mark_columns_needed_for_delete();
 
   if ((table->file->ha_table_flags() & HA_CAN_FORCE_BULK_DELETE) &&
       !table->prepare_triggers_for_delete_stmt_or_event())
@@ -725,6 +789,24 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
     delete_record= true;
   }
 
+  /*
+    From SQL2016, Part 2, 15.7 <Effect of deleting rows from base table>,
+    General Rules, 8), we can conclude that DELETE FOR PORTTION OF time performs
+    0-2 INSERTS + DELETE. We can substitute INSERT+DELETE with one UPDATE, with
+    a condition of no side effects. The side effect is possible if there is a
+    BEFORE INSERT trigger, since it is the only one splitting DELETE and INSERT
+    operations.
+    Another possible side effect is related to tables of non-transactional
+    engines, since UPDATE is anyway atomic, and DELETE+INSERT is not.
+
+    This optimization is not possible for system-versioned table.
+  */
+  portion_of_time_through_update=
+          !(table->triggers && table->triggers->has_triggers(TRG_EVENT_INSERT,
+                                                             TRG_ACTION_BEFORE))
+          && !table->versioned()
+          && table->file->has_transactions();
+
   THD_STAGE_INFO(thd, stage_updating);
   while (likely(!(error=info.read_record())) && likely(!thd->killed) &&
          likely(!thd->is_error()))
@@ -748,7 +830,23 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds,
         break;
       }
 
-      error= table->delete_row();
+      if (table_list->has_period() && portion_of_time_through_update)
+      {
+        bool need_delete= true;
+        error= update_portion_of_time(thd, table, table_list->period_conditions,
+                                      &need_delete);
+        if (likely(!error) && need_delete)
+          error= table->delete_row();
+      }
+      else
+      {
+        error= table->delete_row();
+
+        if (likely(!error) && table_list->has_period()
+            && !portion_of_time_through_update)
+          error= table->insert_portion_of_time(thd, table_list->period_conditions);
+      }
+
       if (likely(!error))
       {
 	deleted++;
@@ -798,6 +896,8 @@ terminate_delete:
   }
   THD_STAGE_INFO(thd, stage_end);
   end_read_record(&info);
+  if (table_list->has_period())
+    table->file->ha_release_auto_increment();
   if (options & OPTION_QUICK)
     (void) table->file->extra(HA_EXTRA_NORMAL);
   ANALYZE_STOP_TRACKING(&explain->command_tracker);
@@ -967,7 +1067,13 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list,
     DBUG_RETURN(TRUE);
   }
 
-  if (unique_table(thd, table_list, table_list->next_global, 0))
+  /*
+      Application-time periods: if FOR PORTION OF ... syntax used, DELETE
+      statement could issue delete_row's mixed with write_row's. This causes
+      problems for myisam and corrupts table, if deleting while scanning.
+   */
+  if (table_list->has_period()
+      || unique_table(thd, table_list, table_list->next_global, 0))
     *delete_while_scanning= false;
 
   if (select_lex->inner_refs_list.elements &&