Adding upstream version 1:10.11.6.upstream/1%10.11.6upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 322 insertions, 0 deletions

diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode.test b/mysql-test/suite/vcol/t/vcol_sql_mode.test
new file mode 100644
index 00000000..f52345c7
--- /dev/null
+++ b/mysql-test/suite/vcol/t/vcol_sql_mode.test
@@ -0,0 +1,322 @@
+--echo #
+--echo # Start of 10.2 tests
+--echo #
+
+--echo #
+--echo # 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
+--echo #
+
+--echo #
+--echo # PAD_CHAR_TO_FULL_LENGTH + various virtual column data types
+--echo #
+
+CREATE TABLE t1 (a CHAR(5), v CHAR(5) AS (a) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v INT AS (a) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v));
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100)));
+SHOW WARNINGS;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v TEXT AS (RTRIM(a)) VIRTUAL, KEY(v(100)));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(TRAILING ' ' FROM a)) VIRTUAL, KEY(v(100)));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(BOTH ' ' FROM a)) VIRTUAL, KEY(v(100)));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING NULL FROM a)) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH NULL FROM a)) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v TEXT AS (TRIM(LEADING ' ' FROM a)) VIRTUAL, KEY(v(100)));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING '' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH '' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(TRAILING 'x' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (TRIM(BOTH 'x' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+# more than one space
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a CHAR(5),
+  v VARCHAR(5) AS (TRIM(TRAILING '  ' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+# more than one space
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a CHAR(5),
+  v VARCHAR(5) AS (TRIM(BOTH '  ' FROM a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a)
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a CHAR(5),
+  b CHAR(5),
+  v TEXT AS (TRIM(TRAILING b FROM a)) VIRTUAL, KEY(v(100)));
+SHOW WARNINGS;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,5,' ')) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,' ')) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,6,NULL)) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,NULL,' ')) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RPAD(a,4,' ')) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a CHAR(5),
+  b CHAR(5),
+  v VARCHAR(5) AS (RPAD(a,NULL,b)) VIRTUAL,
+  KEY(v)
+);
+SHOW WARNINGS;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + comparison
+
+CREATE TABLE t1 (a CHAR(5), v INT AS (a='a') VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+   a CHAR(5) CHARACTER SET latin1 COLLATE latin1_nopad_bin,
+   v INT AS (a='a') VIRTUAL, KEY(v)
+);
+SHOW WARNINGS;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + LIKE
+
+CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a%') VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE NULL) VIRTUAL, KEY(v));
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v INT AS (a LIKE 'a') VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+
+--echo # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v));
+SHOW WARNINGS;
+
+
+--echo #
+--echo # Testing NO_UNSIGNED_SUBTRACTION
+--echo #
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a INT UNSIGNED,
+  b INT UNSIGNED,
+  c INT GENERATED ALWAYS AS (a-b) VIRTUAL,
+  KEY (c)
+);
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a INT UNSIGNED,
+  b INT UNSIGNED,
+  c INT GENERATED ALWAYS AS (CAST(a AS SIGNED)-b) VIRTUAL,
+  KEY (c)
+);
+SHOW WARNINGS;
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (
+  a INT UNSIGNED,
+  b INT UNSIGNED,
+  c INT GENERATED ALWAYS AS (a-CAST(b AS SIGNED)) VIRTUAL,
+  KEY (c)
+);
+SHOW WARNINGS;
+
+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)
+);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+# 'CAST(signed AS DECIMAL)' does not copy 'unsigned_flag' from the argument.
+# So the below is safe.
+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)
+);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION
+--echo #
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+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)
+);
+SHOW WARNINGS;
+
+
+# The below solves the dependency on NO_UNSIGNED_SUBTRACTION
+# but does not solve the dependency on PAD_CHAR_TO_FULL_LENGTH,
+# because the 'length' argument to RPAD() is not a constant.
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+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)
+);
+SHOW WARNINGS;
+
+
+
+--echo # ALTER TABLE ADD KEY(vcol_depending_on_sql_mode) --> error
+
+# This makes sure that QT_ITEM_IDENT_SKIP_DB_NAMES and
+# QT_ITEM_IDENT_SKIP_TABLE_NAMES are passed to print()
+# to avoid temporary table names like `test`.`#sql-50a6_4`.`c`
+# in the error message.
+#
+
+CREATE TABLE t1 (
+  a INT UNSIGNED,
+  b INT UNSIGNED,
+  c CHAR(5),
+  v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL
+);
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+ALTER TABLE t1 ADD KEY(v);
+SHOW WARNINGS;
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE INDEX v ON t1 (v);
+SHOW WARNINGS;
+DROP TABLE t1;
+
+
+--echo # A virtual column on the second position in an index - cannot depend on sql_mode
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (id int, a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(id, v(100)));
+SHOW WARNINGS;
+
+
+--echo # A persisten virtual column cannot depend on sql_mode
+
+--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT);
+SHOW WARNINGS;
+
+CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT);
+SHOW CREATE TABLE t1;
+DROP TABLE t1;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #