MDEV-18156 Assertion `0' failed or `btr_validate_index(index, 0, false)' in row_upd_sec_index_entry or error code 126: Index is corrupted upon DELETE with PAD_CHAR_TO_FULL_LENGTH

This change takes into account a column's GENERATED ALWAYS AS
expression dependcy on sql_mode's PAD_CHAR_TO_FULL_LENGTH and
NO_UNSIGNED_SUBTRACTION flags.

Indexed virtual columns as well as persistent generated columns are
now not allowed to have such dependencies to avoid inconsistent data
or index files on sql_mode changes.
So an error is now returned in cases like this:

  CREATE OR REPLACE TABLE t1
  (
    a CHAR(5),
    v VARCHAR(5) AS (a) PERSISTENT -- CHAR->VARCHAR or CHAR->TEXT = ERROR
  );

Functions RPAD() and RTRIM() can now remove dependency on
PAD_CHAR_TO_FULL_LENGTH. So this can be used instead:

  CREATE OR REPLACE TABLE t1
  (
    a CHAR(5),
    v VARCHAR(5) AS (RTRIM(a)) PERSISTENT
  );

Note, unlike CHAR->VARCHAR and CHAR->TEXT this still works,
not RPAD(a) is needed:

  CREATE OR REPLACE TABLE t1
  (
    a CHAR(5),
    v CHAR(5) AS (a) PERSISTENT -- CHAR->CHAR is OK
  );

More sql_mode flags may affect values of generated columns.
They will be addressed separately.

See comments in sql_mode.h for implementation details.

1 files changed, 6 insertions, 0 deletions

diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h
index a398b4972cb..df8761534a1 100644
--- a/sql/item_strfunc.h
+++ b/sql/item_strfunc.h
@@ -489,6 +489,7 @@ protected:
 public:
   Item_func_trim(THD *thd, Item *a, Item *b): Item_str_func(thd, a, b) {}
   Item_func_trim(THD *thd, Item *a): Item_str_func(thd, a) {}
+  Sql_mode_dependency value_depends_on_sql_mode() const;
   String *val_str(String *);
   bool fix_length_and_dec();
   const char *func_name() const { return "trim"; }
@@ -504,6 +505,10 @@ class Item_func_ltrim :public Item_func_trim
 public:
   Item_func_ltrim(THD *thd, Item *a, Item *b): Item_func_trim(thd, a, b) {}
   Item_func_ltrim(THD *thd, Item *a): Item_func_trim(thd, a) {}
+  Sql_mode_dependency value_depends_on_sql_mode() const
+  {
+    return Item_func::value_depends_on_sql_mode();
+  }
   String *val_str(String *);
   const char *func_name() const { return "ltrim"; }
   const char *mode_name() const { return "leading"; }
@@ -950,6 +955,7 @@ public:
     Item_func_pad(thd, arg1, arg2, arg3) {}
   String *val_str(String *);
   const char *func_name() const { return "rpad"; }
+  Sql_mode_dependency value_depends_on_sql_mode() const;
   Item *get_copy(THD *thd, MEM_ROOT *mem_root)
   { return get_item_copy<Item_func_rpad>(thd, mem_root, this); }
 };