← Back to team overview

maria-developers team mailing list archive

Re: 5f1cd8fe7a5: 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

 

Hi, Alexander!

On Aug 28, Alexander Barkov wrote:
> revision-id: 5f1cd8fe7a5 (mariadb-10.2.26-50-g5f1cd8fe7a5)
> parent(s): 9de2e60d749
> author: Alexander Barkov <bar@xxxxxxxxxxx>
> committer: Alexander Barkov <bar@xxxxxxxxxxx>
> timestamp: 2019-08-26 15:28:32 +0400
> message:
> 
>     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 is not a trivial one-liner fix. Before pushing,
please add a comment explaning the problem and your fix.

> 
> diff --git a/mysql-test/suite/vcol/r/vcol_sql_mode.result b/mysql-test/suite/vcol/r/vcol_sql_mode.result
> new file mode 100644
> index 00000000000..6af46dffb80
> --- /dev/null
> +++ b/mysql-test/suite/vcol/r/vcol_sql_mode.result
> @@ -0,0 +1,234 @@
> +#
> +# Start of 10.2 tests
> +#
> +#
> +# 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
> +#
> +#
> +# PAD_CHAR_TO_FULL_LENGTH + various virtual column data types
> +#
> +CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v));
> +DROP TABLE t1;

please add SHOW CREATE after every CREATE. To show what automatic
expression rewriting does (or does not) happen here.

> +CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v));
> +ERROR HY000: Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;

Better do --enable_warnings at the beginning of the test.
It'll serve two purposes  - you won't need to do SHOW WARNINGS manually
and it'll make clear when there are no warnings after a CREATE.

> +Level	Code	Message
> +Error	1901	Function or expression '`c`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH

Let's make it a proper sentence: "Expression `c` implicitly depends on the @@sql_mode value PAD_CHAR_TO_FULL_LENGTH"

> +CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100)));
> +ERROR HY000: Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a`' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +# PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100)));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100)));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100)));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +# PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`

nice :)

> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100)));
> +ERROR HY000: Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(leading ' ' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(trailing '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(both '' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(trailing 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(both 'x' from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +# PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a)
> +CREATE TABLE t1 (
> +a CHAR(5),
> +b CHAR(5),
> +v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100)));
> +ERROR HY000: Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'trim(trailing `b` from `a`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +# PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +# PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency
> +CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'rpad(`a`,4,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (
> +a CHAR(5),
> +b CHAR(5),
> +v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL,
> +KEY(v)
> +);
> +ERROR HY000: Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'rpad(`a`,NULL,`b`)' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +# PAD_CHAR_TO_FULL_LENGTH + comparison
> +CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (
> +a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin,
> +v INT AS (a='a') VIRTUAL, KEY(v)
> +);
> +ERROR HY000: Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a` = 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +# PAD_CHAR_TO_FULL_LENGTH + LIKE
> +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v));
> +DROP TABLE t1;
> +CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v));
> +ERROR HY000: Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a` like 'a'' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +#
> +# Testing NO_UNSIGNED_SUBTRACTION
> +#
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c INT GENERATED ALWAYS AS (a-b) VIRTUAL,
> +KEY (c)
> +);
> +ERROR HY000: Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a` - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
> +Warning	1105	depends on system variable @@sql_mode value NO_UNSIGNED_SUBTRACTION
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL,
> +KEY (c)
> +);
> +ERROR HY000: Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'cast(`a` as signed) - `b`' cannot be used in the GENERATED ALWAYS AS clause of `c`
> +Warning	1105	depends on system variable @@sql_mode value NO_UNSIGNED_SUBTRACTION
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL,
> +KEY (c)
> +);
> +ERROR HY000: Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression '`a` - cast(`b` as signed)' cannot be used in the GENERATED ALWAYS AS clause of `c`
> +Warning	1105	depends on system variable @@sql_mode value NO_UNSIGNED_SUBTRACTION
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-CAST(b AS SIGNED)) VIRTUAL,
> +KEY (c)
> +);
> +DROP TABLE t1;
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c INT GENERATED ALWAYS AS (CAST(a AS DECIMAL(20,0))-CAST(b AS DECIMAL(20,0))) VIRTUAL,
> +KEY (c)
> +);
> +DROP TABLE t1;
> +#
> +# Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION
> +#
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c CHAR(5),
> +v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,a-b,' ')) VIRTUAL,
> +KEY (v)
> +);
> +ERROR HY000: Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'rpad(`c`,`a` - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value NO_UNSIGNED_SUBTRACTION
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +CREATE TABLE t1 (
> +a INT UNSIGNED,
> +b INT UNSIGNED,
> +c CHAR(5),
> +v VARCHAR(5) GENERATED ALWAYS AS (RPAD(c,CAST(a AS DECIMAL(20,1))-b,' ')) VIRTUAL,
> +KEY (v)
> +);
> +ERROR HY000: Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +SHOW WARNINGS;
> +Level	Code	Message
> +Error	1901	Function or expression 'rpad(`c`,cast(`a` as decimal(20,1)) - `b`,' ')' cannot be used in the GENERATED ALWAYS AS clause of `v`
> +Warning	1105	depends on system variable @@sql_mode value PAD_CHAR_TO_FULL_LENGTH
> +#
> +# End of 10.2 tests
> +#
> diff --git a/sql/field.cc b/sql/field.cc
> index e9eaa440952..28caea78c7d 100644
> --- a/sql/field.cc
> +++ b/sql/field.cc
> @@ -1428,6 +1428,35 @@ void Field::load_data_set_value(const char *pos, uint length,
>  }
>  
>  
> +void Field::error_generated_column_function_is_not_allowed(THD *thd) const
> +{
> +  StringBuffer<64> tmp;
> +  vcol_info->expr->print(&tmp, QT_TO_SYSTEM_CHARSET);
> +  my_error(ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED, MYF(0),
> +           tmp.c_ptr(), vcol_info->get_vcol_type_name(),
> +           (const char *) field_name /*add .str on merge to 10.3 */);

better const_cast<const char*>(field_name)
and it won't need a comment - the compiler will catch it

> +}
> +
> +

this needs a comment. May be as simple as "see sql_mode.h"
btw, you might need this one-line comment in few other places too

> +bool Field::check_vcol_sql_mode_dependency(THD *thd) const
> +{
> +  DBUG_ASSERT(vcol_info);
> +  if (!(part_of_key.is_clear_all() && key_start.is_clear_all()))

1. why not for persistent?
2. why both part_of_key and key_start? I though part_of_key means "anywhere
   in the key", that is it's a superset of key_start.

> +  {
> +    Sql_mode_dependency dep=
> +        vcol_info->expr->value_depends_on_sql_mode() &
> +        Sql_mode_dependency(~0, ~can_handle_sql_mode_dependency_on_store());
> +    if (dep)
> +    {
> +      error_generated_column_function_is_not_allowed(thd);
> +      dep.push_dependency_warnings(thd);
> +      return true;
> +    }
> +  }
> +  return false;
> +}
> +
> +
>  /**
>    Numeric fields base class constructor.
>  */
> diff --git a/sql/item_func.h b/sql/item_func.h
> index 81a7e948085..5be62427852 100644
> --- a/sql/item_func.h
> +++ b/sql/item_func.h
> @@ -773,11 +773,16 @@ class Item_func_plus :public Item_func_additive_op
>  
>  class Item_func_minus :public Item_func_additive_op
>  {
> +  Sql_mode_dependency m_sql_mode_dependency;
>  public:
>    Item_func_minus(THD *thd, Item *a, Item *b):
>      Item_func_additive_op(thd, a, b) {}
>    const char *func_name() const { return "-"; }
>    enum precedence precedence() const { return ADD_PRECEDENCE; }
> +  Sql_mode_dependency value_depends_on_sql_mode() const
> +  {
> +    return m_sql_mode_dependency;

1. no soft_to_hard here?
2. If you do need soft_to_hard here, then, may be, better to do
   soft_to_hard not in value_depends_on_sql_mode() at all,
   but just in one place - in Field::check_vcol_sql_mode_dependency() ?

> +  }
>    longlong int_op();
>    double real_op();
>    my_decimal *decimal_op(my_decimal *);
> diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc
> index 753b6134419..846920bc8c2 100644
> --- a/sql/item_strfunc.cc
> +++ b/sql/item_strfunc.cc
> @@ -2103,6 +2103,38 @@ void Item_func_trim::print(String *str, enum_query_type query_type)
>  }
>  
>  
> +/*
> +  RTRIM(expr)
> +  TRIM(TRAILING ' ' FROM expr)
> +  remove argument's soft dependency on PAD_CHAR_TO_FULL_LENGTH:
> +*/
> +Sql_mode_dependency Item_func_trim::value_depends_on_sql_mode() const
> +{
> +  DBUG_ASSERT(fixed);
> +  if (arg_count == 1) // RTRIM(expr)
> +    return (args[0]->value_depends_on_sql_mode() &
> +            Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)).
> +           soft_to_hard();
> +  // TRIM(... FROM expr)
> +  DBUG_ASSERT(arg_count == 2);
> +  if (!args[1]->value_depends_on_sql_mode_const_item())
> +    return Item_func::value_depends_on_sql_mode();
> +  StringBuffer<64> trimstrbuf;
> +  String *trimstr= args[1]->val_str(&trimstrbuf);
> +  if (!trimstr)
> +    return Sql_mode_dependency();                  // will return NULL
> +  if (trimstr->length() == 0)
> +    return Item_func::value_depends_on_sql_mode(); // will trim nothing
> +  if (trimstr->lengthsp() != 0)
> +    return Item_func::value_depends_on_sql_mode(); // will trim not only spaces

what about TRIM(TRAILING '  ' FROM expr) ?
it'll trim an even number of spaces, might leave one space untrimmed.

> +  // TRIM(TRAILING ' ' FROM expr)
> +  return ((args[0]->value_depends_on_sql_mode() |
> +           args[1]->value_depends_on_sql_mode()) &
> +          Sql_mode_dependency(~0, ~MODE_PAD_CHAR_TO_FULL_LENGTH)).
> +         soft_to_hard();
> +}
> +
> +
>  /* Item_func_password */
>  
>  bool Item_func_password::fix_fields(THD *thd, Item **ref)

Regards,
Sergei
VP of MariaDB Server Engineering
and security@xxxxxxxxxxx


Follow ups