--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 #