MDEV-27744 InnoDB: Failing assertion: !cursor->index->is_committed() in row0ins.cc (from row_ins_sec_index_entry_by_modify) | Assertion `0' failed in row_upd_sec_index_entry (debug) | Corruptionbb-10.3-bar

The crash happened with an indexed virtual column whose
value is evaluated using a function that has a different meaning
in sql_mode='' vs sql_mode=ORACLE:

- DECODE()
- LTRIM()
- RTRIM()
- LPAD()
- RPAD()
- REPLACE()
- SUBSTR()

For example:

CREATE TABLE t1 (
  b VARCHAR(1),
  g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL,
  KEY g(g)
);

So far we had replacement XXX_ORACLE() functions for all mentioned function,
e.g. SUBSTR_ORACLE() for SUBSTR(). So it was possible to correctly re-parse
SUBSTR_ORACLE() even in sql_mode=''.

But it was not possible to re-parse the MariaDB version of SUBSTR()
after switching to sql_mode=ORACLE. It was erroneously mis-interpreted
as SUBSTR_ORACLE().

As a result, this combination worked fine:

SET sql_mode=ORACLE;
CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...;
INSERT ...
FLUSH TABLES;
SET sql_mode='';
INSERT ...

But the other way around it crashed:

SET sql_mode='';
CREATE TABLE t1 ... g CHAR(1) GENERATED ALWAYS AS (SUBSTR(b,0,0)) VIRTUAL, ...;
INSERT ...
FLUSH TABLES;
SET sql_mode=ORACLE;
INSERT ...

At CREATE time, SUBSTR was instantiated as Item_func_substr and printed
in the FRM file as substr(). At re-open time with sql_mode=ORACLE, "substr()"
was erroneously instantiated as Item_func_substr_oracle.

Fix:

The fix proposes a symmetric solution. It provides a way to re-parse reliably
all sql_mode dependent functions to their original CREATE TABLE time meaning,
no matter what the open-time sql_mode is.

We take advantage of the same idea we previously used to resolve sql_mode
dependent data types.

Now all sql_mode dependent functions are printed by SHOW using a schema
qualifier when the current sql_mode differs from the function sql_mode:

SET sql_mode='';
CREATE TABLE t1 ... SUBSTR(a,b,c) ..;
SET sql_mode=ORACLE;
SHOW CREATE TABLE t1;   ->   mariadb_schema.substr(a,b,c)

SET sql_mode=ORACLE;
CREATE TABLE t2 ... SUBSTR(a,b,c) ..;
SET sql_mode='';
SHOW CREATE TABLE t1;   ->   oracle_schema.substr(a,b,c)

Old replacement names like substr_oracle() are still understood for
backward compatibility and used in FRM files (for downgrade compatibility),
but they are not printed by SHOW any more.

1 files changed, 78 insertions, 3 deletions

diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
index 75abd9906cd..6bf54cb4c84 100644
--- a/sql/item_strfunc.cc
+++ b/sql/item_strfunc.cc
@@ -574,6 +574,27 @@ String *Item_func_decode_histogram::val_str(String *str)
 
 ///////////////////////////////////////////////////////////////////////////////
 
+Item_func_concat *
+Item_func_concat::create(THD *thd, const LEX_CSTRING &name,
+                         List<Item> *item_list)
+{
+  if (create_check_args_ge(name, item_list, 1))
+    return NULL;
+  return new (thd->mem_root) Item_func_concat(thd, *item_list);
+}
+
+
+Item_func_concat_operator_oracle *
+Item_func_concat_operator_oracle::create(THD *thd,
+                                         const LEX_CSTRING &name,
+                                         List<Item> *item_list)
+{
+  if (create_check_args_ge(name, item_list, 1))
+    return NULL;
+  return new (thd->mem_root) Item_func_concat_operator_oracle(thd, *item_list);
+}
+
+
 /*
   Realloc the result buffer.
   NOTE: We should be prudent in the initial allocation unit -- the
@@ -2161,11 +2182,10 @@ void Item_func_trim::print(String *str, enum_query_type query_type)
 {
   if (arg_count == 1)
   {
-    Item_func::print(str, query_type);
+    print_sql_mode_dependent(str, query_type);
     return;
   }
-  str->append(Item_func_trim::func_name());
-  str->append(func_name_ext());
+  print_sql_mode_dependent_name(str, query_type, Item_func_trim::func_name());
   str->append('(');
   str->append(mode_name());
   str->append(' ');
@@ -2379,6 +2399,20 @@ void Item_func_decode::crypto_transform(String *res)
 }
 
 
+Item_func_decode *Item_func_decode::create(THD *thd, const LEX_CSTRING &name,
+                                           List<Item> *item_list)
+{
+  if (unlikely(!item_list || item_list->elements != 2))
+  {
+    wrong_param_count_error(mariadb_schema.name(), name);
+    return NULL;
+  }
+  Item_args args(thd, *item_list);
+  return new (thd->mem_root) Item_func_decode(thd, args.arguments()[0],
+                                                   args.arguments()[1]);
+}
+
+
 String *Item_func_database::val_str(String *str)
 {
   DBUG_ASSERT(fixed == 1);
@@ -3204,6 +3238,47 @@ static String *default_pad_str(String *pad_str, CHARSET_INFO *collation)
   return pad_str;
 }
 
+
+Item_func_lpad*
+Item_func_lpad::create(THD *thd, const LEX_CSTRING &name,
+                       List<Item> *item_list)
+{
+  if (create_check_args_between(name, item_list, 2, 3))
+    return NULL;
+  return new (thd->mem_root) Item_func_lpad(thd, *item_list);
+}
+
+
+Item_func_lpad_oracle*
+Item_func_lpad_oracle::create(THD *thd, const LEX_CSTRING &name,
+                              List<Item> *item_list)
+{
+  if (create_check_args_between(name, item_list, 2, 3))
+    return NULL;
+  return new (thd->mem_root) Item_func_lpad_oracle(thd, *item_list);
+}
+
+
+Item_func_rpad*
+Item_func_rpad::create(THD *thd, const LEX_CSTRING &name,
+                       List<Item> *item_list)
+{
+  if (create_check_args_between(name, item_list, 2, 3))
+    return NULL;
+  return new (thd->mem_root) Item_func_rpad(thd, *item_list);
+}
+
+
+Item_func_rpad_oracle*
+Item_func_rpad_oracle::create(THD *thd, const LEX_CSTRING &name,
+                              List<Item> *item_list)
+{
+  if (create_check_args_between(name, item_list, 2, 3))
+    return NULL;
+  return new (thd->mem_root) Item_func_rpad_oracle(thd, *item_list);
+}
+
+
 bool Item_func_pad::fix_length_and_dec()
 {
   if (arg_count == 3)