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
