mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 10:56:12 +01:00
322 lines
8.5 KiB
Text
322 lines
8.5 KiB
Text
--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 # Combination: 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 persistent 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 #
|