diff options
Diffstat (limited to 'mysql-test/suite/vcol/t/vcol_sql_mode.test')
| -rw-r--r-- | mysql-test/suite/vcol/t/vcol_sql_mode.test | 81 |
1 files changed, 54 insertions, 27 deletions
diff --git a/mysql-test/suite/vcol/t/vcol_sql_mode.test b/mysql-test/suite/vcol/t/vcol_sql_mode.test index e4cc6d21718..44d3686ac01 100644 --- a/mysql-test/suite/vcol/t/vcol_sql_mode.test +++ b/mysql-test/suite/vcol/t/vcol_sql_mode.test @@ -21,21 +21,25 @@ DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v TIME AS (a) VIRTUAL, KEY(v)); DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (c CHAR(8), v BINARY(8) AS (c), KEY(v)); SHOW WARNINGS; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v BIT(64) AS (a) VIRTUAL, KEY(v)); SHOW WARNINGS; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) VIRTUAL, KEY(v)); SHOW WARNINGS; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v TEXT AS (a) VIRTUAL, KEY(v(100))); SHOW WARNINGS; +DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM resolving dependency @@ -75,52 +79,61 @@ DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM not resolving dependency ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED # 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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED # 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; +DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + TRIM(... non_constant FROM a) ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + RPAD resolving dependency @@ -144,11 +157,12 @@ DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + RPAD not resolving dependency ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--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; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a CHAR(5), b CHAR(5), @@ -156,6 +170,7 @@ CREATE TABLE t1 ( KEY(v) ); SHOW WARNINGS; +DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + comparison @@ -164,12 +179,13 @@ 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 +#--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; +DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + LIKE @@ -182,23 +198,25 @@ 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 +#--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; +DROP TABLE t1; --echo # PAD_CHAR_TO_FULL_LENGTH + LENGTH(char_column) = hard dependency ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v INT AS (LENGTH(a)) VIRTUAL, KEY(v)); SHOW WARNINGS; +DROP TABLE t1; --echo # --echo # Testing NO_UNSIGNED_SUBTRACTION --echo # ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -206,8 +224,9 @@ CREATE TABLE t1 ( KEY (c) ); SHOW WARNINGS; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -215,8 +234,9 @@ CREATE TABLE t1 ( KEY (c) ); SHOW WARNINGS; +DROP TABLE t1; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -224,6 +244,7 @@ CREATE TABLE t1 ( KEY (c) ); SHOW WARNINGS; +DROP TABLE t1; CREATE TABLE t1 ( a INT UNSIGNED, @@ -250,7 +271,7 @@ DROP TABLE t1; --echo # Comnination: PAD_CHAR_TO_FULL_LENGTH + NO_UNSIGNED_SUBTRACTION --echo # ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -259,13 +280,14 @@ CREATE TABLE t1 ( KEY (v) ); SHOW WARNINGS; +DROP TABLE t1; # 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 +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 ( a INT UNSIGNED, b INT UNSIGNED, @@ -274,6 +296,7 @@ CREATE TABLE t1 ( KEY (v) ); SHOW WARNINGS; +DROP TABLE t1; @@ -291,10 +314,12 @@ CREATE TABLE t1 ( c CHAR(5), v VARCHAR(5) GENERATED ALWAYS AS (c) VIRTUAL ); ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED ALTER TABLE t1 ADD KEY(v); SHOW WARNINGS; ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +ALTER TABLE t1 DROP KEY v; + +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE INDEX v ON t1 (v); SHOW WARNINGS; DROP TABLE t1; @@ -302,16 +327,18 @@ 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 +#--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; +DROP TABLE t1; --echo # A persisten virtual column cannot depend on sql_mode ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +#--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (a) PERSISTENT); SHOW WARNINGS; +DROP TABLE t1; CREATE TABLE t1 (a CHAR(5), v VARCHAR(5) AS (RTRIM(a)) PERSISTENT); SHOW CREATE TABLE t1; |