MDEV-14645: AS OF TIMESTAMP is misused as TRX_ID

Remove 1668efb722d that introduced a special magic
behavior for UNIX_TIMESTAMP() in the AS OF context

11 files changed, 10 insertions, 119 deletions

diff --git a/mysql-test/suite/versioning/r/derived.result b/mysql-test/suite/versioning/r/derived.result
index cd6cec7e725..0792093bbc4 100644
--- a/mysql-test/suite/versioning/r/derived.result
+++ b/mysql-test/suite/versioning/r/derived.result
@@ -211,12 +211,12 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
 Query A:
-Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > <cache>(cast(current_timestamp(6) as datetime(6))) and `test`.`t1`.`row_start` <= <cache>(cast(current_timestamp(6) as datetime(6))) and `test`.`t2`.`row_end` > <cache>(cast(current_timestamp(6) as datetime(6))) and `test`.`t2`.`row_start` <= <cache>(cast(current_timestamp(6) as datetime(6)))
+Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > <cache>(current_timestamp(6)) and `test`.`t1`.`row_start` <= <cache>(current_timestamp(6)) and `test`.`t2`.`row_end` > <cache>(current_timestamp(6)) and `test`.`t2`.`row_start` <= <cache>(current_timestamp(6))
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
 Query B:
-Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > <cache>(cast(current_timestamp(6) as datetime(6))) and `test`.`t1`.`row_start` <= <cache>(cast(current_timestamp(6) as datetime(6))) and `test`.`t2`.`row_end` > <cache>(cast(current_timestamp(6) as datetime(6))) and `test`.`t2`.`row_start` <= <cache>(cast(current_timestamp(6) as datetime(6)))
+Note	1003	select `test`.`t1`.`x` AS `x`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > <cache>(current_timestamp(6)) and `test`.`t1`.`row_start` <= <cache>(current_timestamp(6)) and `test`.`t2`.`row_end` > <cache>(current_timestamp(6)) and `test`.`t2`.`row_start` <= <cache>(current_timestamp(6))
 Fine result: queries A and B are equal.
 ## LEFT JOIN: t1, t2 versioned
 select * from (
diff --git a/mysql-test/suite/versioning/r/select.result b/mysql-test/suite/versioning/r/select.result
index 88ad054058b..e1de47ff9fc 100644
--- a/mysql-test/suite/versioning/r/select.result
+++ b/mysql-test/suite/versioning/r/select.result
@@ -153,21 +153,21 @@ id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
 Warnings:
-Note	1003	select `test`.`t1`.`x` AS `IJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t1`.`row_start` <= <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`row_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`row_start` <= <cache>(cast(@`t0` as datetime(6)))
+Note	1003	select `test`.`t1`.`x` AS `IJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL join `test`.`t2` FOR SYSTEM_TIME ALL where `test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0`
 explain extended select * from (select t1.x as LJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 left join t2 on t1.x = t2.x)
 for system_time as of timestamp @t0 as t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	Using where; Using join buffer (flat, BNL join)
 Warnings:
-Note	1003	select `test`.`t1`.`x` AS `LJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL left join `test`.`t2` FOR SYSTEM_TIME ALL on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t1`.`row_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t1`.`row_start` <= <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`row_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`row_start` <= <cache>(cast(@`t0` as datetime(6)))) where 1
+Note	1003	select `test`.`t1`.`x` AS `LJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t1` FOR SYSTEM_TIME ALL left join `test`.`t2` FOR SYSTEM_TIME ALL on(`test`.`t2`.`x` = `test`.`t1`.`x` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0`) where 1
 explain extended select * from (select t1.x as RJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 right join t2 on t1.x = t2.x)
 for system_time as of timestamp @t0 as t;
 id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
 1	SIMPLE	t2	ALL	NULL	NULL	NULL	NULL	3	100.00	
 1	SIMPLE	t1	ALL	NULL	NULL	NULL	NULL	5	100.00	Using where; Using join buffer (flat, BNL join)
 Warnings:
-Note	1003	select `test`.`t1`.`x` AS `RJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t2` FOR SYSTEM_TIME ALL left join `test`.`t1` FOR SYSTEM_TIME ALL on(`test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t1`.`row_start` <= <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`row_end` > <cache>(cast(@`t0` as datetime(6))) and `test`.`t2`.`row_start` <= <cache>(cast(@`t0` as datetime(6)))) where 1
+Note	1003	select `test`.`t1`.`x` AS `RJ2_x1`,`test`.`t1`.`y` AS `y1`,`test`.`t2`.`x` AS `x2`,`test`.`t2`.`y` AS `y2` from `test`.`t2` FOR SYSTEM_TIME ALL left join `test`.`t1` FOR SYSTEM_TIME ALL on(`test`.`t1`.`x` = `test`.`t2`.`x` and `test`.`t1`.`row_end` > @`t0` and `test`.`t1`.`row_start` <= @`t0` and `test`.`t2`.`row_end` > @`t0` and `test`.`t2`.`row_start` <= @`t0`) where 1
 select * from (select t1.x as IJ2_x1, t1.y as y1, t2.x as x2, t2.y as y2 from t1 inner join t2 on t1.x = t2.x)
 for system_time as of timestamp @t0 as t;
 IJ2_x1	y1	x2	y2
diff --git a/mysql-test/suite/versioning/t/select.test b/mysql-test/suite/versioning/t/select.test
index 8f3e0a401f3..2f24f7d8fc0 100644
--- a/mysql-test/suite/versioning/t/select.test
+++ b/mysql-test/suite/versioning/t/select.test
@@ -226,7 +226,6 @@ select x from t1 for system_time as of transaction @trx_start;
 select x from t1 for system_time as of @ts;
 select x from t1 for system_time as of @trx_start;
 
-
 --echo ### Issue #365, bug 4 (related to #226, optimized fields)
 create or replace table t1 (i int, b int) with system versioning;
 insert into t1 values (0, 0), (0, 0);
diff --git a/sql/item_func.h b/sql/item_func.h
index 1b3f28c41be..38a40e71544 100644
--- a/sql/item_func.h
+++ b/sql/item_func.h
@@ -1005,31 +1005,6 @@ public:
 };
 
 
-class Item_longlong_typecast :public Item_int_func
-{
-public:
-  Item_longlong_typecast(THD *thd, Item *a): Item_int_func(thd, a)
-  {
-  }
-  const char *func_name() const { return "cast_as_longlong"; }
-  const char *cast_type() const { return "longlong"; }
-  const Type_handler *type_handler() const { return &type_handler_longlong; }
-  longlong val_int()
-  {
-    return args[0]->val_int();
-  }
-  void fix_length_and_dec_generic() {}
-  void fix_length_and_dec()
-  {
-    args[0]->type_handler()->Item_longlong_typecast_fix_length_and_dec(this);
-  }
-  bool need_parentheses_in_default() { return true; }
-  Item *get_copy(THD *thd)
-  { return get_item_copy<Item_longlong_typecast>(thd, this); }
-};
-
-
-
 class Item_func_additive_op :public Item_num_op
 {
 public:
diff --git a/sql/item_timefunc.cc b/sql/item_timefunc.cc
index 06c3aaecd54..4ddeb83b163 100644
--- a/sql/item_timefunc.cc
+++ b/sql/item_timefunc.cc
@@ -2642,27 +2642,6 @@ bool Item_datetime_typecast::get_date(MYSQL_TIME *ltime, ulonglong fuzzy_date)
 }
 
 
-void Item_datetime_from_unixtime_typecast::fix_length_and_dec()
-{
-  Item_datetime_typecast::fix_length_and_dec();
-
-  switch (args[0]->result_type())
-  {
-  case INT_RESULT:
-  case REAL_RESULT:
-  case DECIMAL_RESULT:
-  {
-    Query_arena_stmt on_stmt_arena(thd);
-    Item_func_from_unixtime *a= new (thd->mem_root) Item_func_from_unixtime(thd, args[0]);
-    a->fix_length_and_dec();
-    args[0]= a;
-    break;
-  }
-  default:;
-  }
-}
-
-
 /**
   MAKEDATE(a,b) is a date function that creates a date value 
   from a year and day value.
diff --git a/sql/item_timefunc.h b/sql/item_timefunc.h
index 761f619def8..c3bda9d85ba 100644
--- a/sql/item_timefunc.h
+++ b/sql/item_timefunc.h
@@ -1192,20 +1192,6 @@ public:
 };
 
 
-class Item_datetime_from_unixtime_typecast :public Item_datetime_typecast
-{
-  THD *thd;
-public:
-  Item_datetime_from_unixtime_typecast(THD *_thd, Item *a, uint dec_arg):
-    Item_datetime_typecast(_thd, a, dec_arg), thd(_thd) {}
-  const char *func_name() const { return "cast_as_datetime_from_unixtime"; }
-  const char *cast_type() const { return "datetime"; }
-  void fix_length_and_dec();
-  Item *get_copy(THD *thd)
-  { return get_item_copy<Item_datetime_from_unixtime_typecast>(thd, this); }
-};
-
-
 class Item_func_makedate :public Item_datefunc
 {
   bool check_arguments() const
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index 3e8c2d41813..0fb3275ceea 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -867,28 +867,14 @@ int SELECT_LEX::vers_setup_conds(THD *thd, TABLE_LIST *tables, COND **where_expr
     TABLE *t= table->table;
     if (t->versioned(VERS_TIMESTAMP))
     {
-      if (vers_conditions)
-      {
-        vers_conditions.start.add_typecast(thd, t->s->versioned);
-        vers_conditions.end.add_typecast(thd, t->s->versioned);
-      }
+      MYSQL_TIME max_time;
       switch (vers_conditions.type)
       {
       case SYSTEM_TIME_UNSPECIFIED:
-        if (t->vers_start_field()->real_type() != MYSQL_TYPE_LONGLONG)
-        {
-          MYSQL_TIME max_time;
-          thd->variables.time_zone->gmt_sec_to_TIME(&max_time, TIMESTAMP_MAX_VALUE);
-          max_time.second_part= TIME_MAX_SECOND_PART;
-          curr= newx Item_datetime_literal(thd, &max_time,
-                                            TIME_SECOND_PART_DIGITS);
-          cond1= newx Item_func_eq(thd, row_end, curr);
-        }
-        else
-        {
-          curr= newx Item_int(thd, ULONGLONG_MAX);
-          cond1= newx Item_func_eq(thd, row_end, curr);
-        }
+        thd->variables.time_zone->gmt_sec_to_TIME(&max_time, TIMESTAMP_MAX_VALUE);
+        max_time.second_part= TIME_MAX_SECOND_PART;
+        curr= newx Item_datetime_literal(thd, &max_time, TIME_SECOND_PART_DIGITS);
+        cond1= newx Item_func_eq(thd, row_end, curr);
         cond1= or_items(thd, cond1, newx Item_func_isnull(thd, row_end));
         break;
       case SYSTEM_TIME_AS_OF:
diff --git a/sql/sql_type.cc b/sql/sql_type.cc
index a11b5a7978f..09daca0806e 100644
--- a/sql/sql_type.cc
+++ b/sql/sql_type.cc
@@ -4590,14 +4590,6 @@ bool Type_handler::
 }
 
 
-bool Type_handler::
-       Item_longlong_typecast_fix_length_and_dec(Item_longlong_typecast *item) const
-{
-  item->fix_length_and_dec_generic();
-  return false;
-}
-
-
 #ifdef HAVE_SPATIAL
 
 bool Type_handler_geometry::
diff --git a/sql/sql_type.h b/sql/sql_type.h
index 0738ef40ce8..db03b77d48f 100644
--- a/sql/sql_type.h
+++ b/sql/sql_type.h
@@ -55,7 +55,6 @@ class Item_char_typecast;
 class Item_time_typecast;
 class Item_date_typecast;
 class Item_datetime_typecast;
-class Item_longlong_typecast;
 class Item_func_plus;
 class Item_func_minus;
 class Item_func_mul;
@@ -1332,8 +1331,6 @@ public:
   Item_date_typecast_fix_length_and_dec(Item_date_typecast *item) const;
   virtual bool
   Item_datetime_typecast_fix_length_and_dec(Item_datetime_typecast *item) const;
-  virtual bool
-  Item_longlong_typecast_fix_length_and_dec(Item_longlong_typecast *item) const;
 
   virtual bool
   Item_func_plus_fix_length_and_dec(Item_func_plus *func) const= 0;
diff --git a/sql/table.cc b/sql/table.cc
index 4bc8cce7e31..7d3d7412ba5 100644
--- a/sql/table.cc
+++ b/sql/table.cc
@@ -8910,28 +8910,6 @@ void Vers_history_point::fix_item()
     item->decimals= 6;
 }
 
-void Vers_history_point::add_typecast(THD *thd, enum vers_sys_type_t defunit)
-{
-  if (item)
-  {
-    if (!unit)
-      unit= defunit;
-    switch (unit)
-    {
-    case VERS_TIMESTAMP:
-      item= new (thd->mem_root) Item_datetime_from_unixtime_typecast(
-                                    thd, item, MAX_DATETIME_PRECISION);
-      break;
-    case VERS_TRX_ID:
-      item= new (thd->mem_root) Item_longlong_typecast(thd, item);
-      break;
-    default:
-      DBUG_ASSERT(0);
-      break;
-    }
-  }
-}
-
 void Vers_history_point::print(String *str, enum_query_type query_type,
                                const char *prefix, size_t plen)
 {
diff --git a/sql/table.h b/sql/table.h
index f759ea221f5..b149bfc0f51 100644
--- a/sql/table.h
+++ b/sql/table.h
@@ -1840,7 +1840,6 @@ public:
   void empty() { unit= VERS_UNDEFINED; item= NULL; }
   void print(String *str, enum_query_type, const char *prefix, size_t plen);
   void resolve_unit(bool timestamps_only);
-  void add_typecast(THD *thd, enum vers_sys_type_t defunit);
 };
 
 struct vers_select_conds_t