diff options
Diffstat (limited to 'mysql-test/suite/gcol')
17 files changed, 94 insertions, 81 deletions
diff --git a/mysql-test/suite/gcol/inc/gcol_blocked_sql_funcs_main.inc b/mysql-test/suite/gcol/inc/gcol_blocked_sql_funcs_main.inc index 4cc10730..13b7dd19 100644 --- a/mysql-test/suite/gcol/inc/gcol_blocked_sql_funcs_main.inc +++ b/mysql-test/suite/gcol/inc/gcol_blocked_sql_funcs_main.inc @@ -257,9 +257,13 @@ drop table t1; --echo # create table t1 (a int); ---error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +insert t1 values (1); alter table t1 add column b binary(32) generated always as (random_bytes(a)); -alter table t1 add column b binary(32) default (random_bytes(a)); +select a,length(b) from t1; +--error ER_GENERATED_COLUMN_FUNCTION_IS_NOT_ALLOWED +alter table t1 add index (b); +alter table t1 add column c binary(32) default (random_bytes(a)); +select a,length(b),length(c) from t1; drop table t1; --echo # diff --git a/mysql-test/suite/gcol/inc/gcol_keys.inc b/mysql-test/suite/gcol/inc/gcol_keys.inc index 4ae47afc..cc45e250 100644 --- a/mysql-test/suite/gcol/inc/gcol_keys.inc +++ b/mysql-test/suite/gcol/inc/gcol_keys.inc @@ -14,6 +14,8 @@ # Change: # ################################################################################ +--source include/have_sequence.inc + if (!$support_virtual_index) { let $skip_spatial_index_check=1; let $skip_foreign_key_check=1; @@ -197,12 +199,16 @@ PRIMARY KEY (pk), KEY (col_time_key), KEY (col_datetime_key)); +--disable_warnings INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values ('14:03:03.042673','2001-11-28 00:50:27.051028', 'c'), ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL), ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'), ('18:56:33.027423','2003-04-01 00:00:00', 'i'); +insert into c (col_time_nokey,col_datetime_nokey,col_varchar_nokey) select '10:10:10', '2021-12-24 01:50:27', 'z' from seq_1_to_10; +--enable_warnings + --replace_column 9 x 10 x EXPLAIN SELECT outr.col_time_key AS x diff --git a/mysql-test/suite/gcol/inc/gcol_select.inc b/mysql-test/suite/gcol/inc/gcol_select.inc index 2386c55f..8dc76276 100644 --- a/mysql-test/suite/gcol/inc/gcol_select.inc +++ b/mysql-test/suite/gcol/inc/gcol_select.inc @@ -545,11 +545,11 @@ CREATE TABLE cc ( ); INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5); --replace_column 9 # 10 # -EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3; -SELECT pk FROM cc WHERE col_int_key > 3; +EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3; +SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3; --replace_column 9 # 10 # -EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; -SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; +EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1; +SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1; DROP TABLE cc; --echo # @@ -872,7 +872,10 @@ CREATE TABLE t1(a INT); INSERT INTO t1 VALUES(2147483647); ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; +# COPY/NONE is not supported in embedded +--error ER_WARN_DATA_OUT_OF_RANGE,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; +--error ER_WARN_DATA_OUT_OF_RANGE,ER_ALTER_OPERATION_NOT_SUPPORTED_REASON ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED; ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE; diff --git a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result index a79ed43c..5810dc33 100644 --- a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_innodb.result @@ -177,9 +177,17 @@ drop table t1; # MDEV-29029 Index corruption and/or assertion failure upon using RANDOM_BYTES for indexed virtual column # create table t1 (a int); +insert t1 values (1); alter table t1 add column b binary(32) generated always as (random_bytes(a)); +select a,length(b) from t1; +a length(b) +1 32 +alter table t1 add index (b); ERROR HY000: Function or expression 'random_bytes()' cannot be used in the GENERATED ALWAYS AS clause of `b` -alter table t1 add column b binary(32) default (random_bytes(a)); +alter table t1 add column c binary(32) default (random_bytes(a)); +select a,length(b),length(c) from t1; +a length(b) length(c) +1 32 32 drop table t1; # # diff --git a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result index ac8bd0a3..ffd407db 100644 --- a/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_blocked_sql_funcs_myisam.result @@ -179,9 +179,17 @@ drop table t1; # MDEV-29029 Index corruption and/or assertion failure upon using RANDOM_BYTES for indexed virtual column # create table t1 (a int); +insert t1 values (1); alter table t1 add column b binary(32) generated always as (random_bytes(a)); +select a,length(b) from t1; +a length(b) +1 32 +alter table t1 add index (b); ERROR HY000: Function or expression 'random_bytes()' cannot be used in the GENERATED ALWAYS AS clause of `b` -alter table t1 add column b binary(32) default (random_bytes(a)); +alter table t1 add column c binary(32) default (random_bytes(a)); +select a,length(b),length(c) from t1; +a length(b) length(c) +1 32 32 drop table t1; # # diff --git a/mysql-test/suite/gcol/r/gcol_bugfixes.result b/mysql-test/suite/gcol/r/gcol_bugfixes.result index 5dfa416a..f26b0ad3 100644 --- a/mysql-test/suite/gcol/r/gcol_bugfixes.result +++ b/mysql-test/suite/gcol/r/gcol_bugfixes.result @@ -638,10 +638,10 @@ DEFAULT SUBSTRING_INDEX(USER(),'@',1) ); EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE EXPLAIN UPDATE gafld SET nuigafld = 0 WHERE nuigafld = 10; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE gafld ALL NULL NULL NULL NULL 1 Using where +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE DROP TABLE gafld; # (duplicate) MDEV-17653 replace into generated columns is unstable # Some columns are snipped from the MDEV test diff --git a/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result b/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result index 193ef064..bc3b5493 100644 --- a/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_ins_upd_innodb.result @@ -497,7 +497,7 @@ WHERE OUTR1.pk = 1 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY OUTR1 const PRIMARY PRIMARY 4 const 1 1 PRIMARY INNR1 ALL NULL NULL NULL NULL 2 Using where; FirstMatch(OUTR1) -1 PRIMARY OUTR2 index NULL PRIMARY 4 NULL 2 Using index +1 PRIMARY OUTR2 ALL NULL NULL NULL NULL 2 DROP TABLE IF EXISTS b,bb,d; # # Bug#21216067 ASSERTION FAILED ROW_UPD_SEC_INDEX_ENTRY (INNOBASE/ROW/ROW0UPD.CC:2103) diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result index 0228f9be..4b1e5b48 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result @@ -192,11 +192,7 @@ INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL), ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'), ('18:56:33.027423','2003-04-01 00:00:00', 'i'); -Warnings: -Note 1265 Data truncated for column 'col_time_key' at row 1 -Note 1265 Data truncated for column 'col_time_key' at row 2 -Note 1265 Data truncated for column 'col_time_key' at row 3 -Note 1265 Data truncated for column 'col_time_key' at row 4 +insert into c (col_time_nokey,col_datetime_nokey,col_varchar_nokey) select '10:10:10', '2021-12-24 01:50:27', 'z' from seq_1_to_10; EXPLAIN SELECT outr.col_time_key AS x FROM c as outr diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result index 523ff3a3..3c9093cf 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result @@ -192,11 +192,7 @@ INSERT INTO c ( col_time_nokey,col_datetime_nokey,col_varchar_nokey) values ('01:46:09.016386','2007-10-09 19:53:04.008332', NULL), ('16:21:18.052408','2001-11-08 21:02:12.009395', 'x'), ('18:56:33.027423','2003-04-01 00:00:00', 'i'); -Warnings: -Note 1265 Data truncated for column 'col_time_key' at row 1 -Note 1265 Data truncated for column 'col_time_key' at row 2 -Note 1265 Data truncated for column 'col_time_key' at row 3 -Note 1265 Data truncated for column 'col_time_key' at row 4 +insert into c (col_time_nokey,col_datetime_nokey,col_varchar_nokey) select '10:10:10', '2021-12-24 01:50:27', 'z' from seq_1_to_10; EXPLAIN SELECT outr.col_time_key AS x FROM c as outr diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index 72d1e9f3..8423c3bc 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -146,7 +146,7 @@ count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by +1 SIMPLE t1 range NULL c 5 NULL 5 Using index for group-by ### ### filesort & range-based utils ### @@ -536,9 +536,9 @@ INSERT INTO t1 VALUES(2147483647); ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +Got one of the listed errors ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +Got one of the listed errors ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED; ERROR 22003: Out of range value for column 'f' at row 1 ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE; @@ -699,11 +699,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN @@ -754,11 +753,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN @@ -810,11 +808,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN @@ -874,11 +871,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index 0d18976f..df1fbd27 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -146,7 +146,7 @@ count(distinct c) 3 explain select count(distinct c) from t1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range NULL c 5 NULL 6 Using index for group-by +1 SIMPLE t1 range NULL c 5 NULL 5 Using index for group-by ### ### filesort & range-based utils ### @@ -792,18 +792,18 @@ PRIMARY KEY (pk), KEY (col_int_key) ); INSERT INTO cc (col_int_nokey) VALUES (0),(1),(7),(0),(4),(5); -EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3; +EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE cc range col_int_key col_int_key 5 NULL # # -SELECT pk FROM cc WHERE col_int_key > 3; +SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3; pk 5 6 3 -EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; +EXPLAIN SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE cc range col_int_key col_int_key 5 NULL # # -SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; +SELECT pk FROM cc force index(col_int_key) WHERE col_int_key > 3 ORDER BY 1; pk 3 5 @@ -1155,9 +1155,9 @@ INSERT INTO t1 VALUES(2147483647); ALTER TABLE t1 ADD COLUMN h INT AS (a) VIRTUAL; ALTER TABLE t1 CHANGE h i INT AS (a) VIRTUAL, ALGORITHM=COPY; ALTER TABLE t1 ADD COLUMN b SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +Got one of the listed errors ALTER TABLE t1 ADD COLUMN e SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=NONE; -ERROR 0A000: LOCK=NONE is not supported. Reason: COPY algorithm requires a lock. Try LOCK=SHARED +Got one of the listed errors ALTER TABLE t1 ADD COLUMN f SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=SHARED; ERROR 22003: Out of range value for column 'f' at row 1 ALTER TABLE t1 ADD COLUMN g SMALLINT AS (a) VIRTUAL, ALGORITHM=COPY, LOCK=EXCLUSIVE; @@ -1325,11 +1325,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN @@ -1381,11 +1380,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY,v_idx,v_idx2 PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN @@ -1439,11 +1437,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY,v_idx2 PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN @@ -1506,11 +1503,10 @@ WHERE t4.c1 < 'o' ) AND t1.i1 <= t3.i2_key; id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 4 -1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 2 -1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where +1 PRIMARY t4 ALL NULL NULL NULL NULL 3 Using where; Start temporary +1 PRIMARY t1 ALL NULL NULL NULL NULL 4 Using join buffer (flat, BNL join) +1 PRIMARY t3 eq_ref PRIMARY,v_idx PRIMARY 4 test.t4.i1 1 Using where; End temporary 1 PRIMARY t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) -2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1, t3.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 WHERE ( t3.pk IN diff --git a/mysql-test/suite/gcol/r/innodb_virtual_basic.result b/mysql-test/suite/gcol/r/innodb_virtual_basic.result index 38238871..db8e39d8 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_basic.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_basic.result @@ -957,12 +957,12 @@ CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); INSERT INTO t1 VALUES (11, 3, DEFAULT, 'mm'); INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm'); INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm'); -ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 ADD INDEX idx12 (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED ALTER TABLE t1 ADD INDEX idx12 (c), LOCK=NONE; -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), ALGORITHM = INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED DROP TABLE t1 ; CREATE TABLE t1 (a INT, b INT, c INT GENERATED ALWAYS AS(a+b), d INT GENERATED ALWAYS AS(a+b), h VARCHAR(10)); diff --git a/mysql-test/suite/gcol/r/innodb_virtual_debug.result b/mysql-test/suite/gcol/r/innodb_virtual_debug.result index e1b87938..1ca30b84 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_debug.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_debug.result @@ -55,7 +55,7 @@ a b c h 18 1 19 mm 28 1 29 mm NULL NULL NULL mx -ALTER TABLE t FORCE, LOCK=NONE; +ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED disconnect con1; DROP TABLE t; diff --git a/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result b/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result index 35f37034..028ae023 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result @@ -6,30 +6,30 @@ CREATE TABLE t4 (i INT, v INT AS (i) VIRTUAL) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; INSERT INTO t4 SET i=1; ALTER TABLE t4 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t4 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t4 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t4 DROP k, LOCK=NONE; +ALTER TABLE t4 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t4 DROP INDEX v, LOCK=NONE; INSERT INTO t3 SET i=1; ALTER TABLE t3 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t3 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t3 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t3 DROP k, LOCK=NONE; +ALTER TABLE t3 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t3 DROP INDEX v, LOCK=NONE; INSERT INTO t2 SET i=1; ALTER TABLE t2 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t2 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t2 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t2 DROP k, LOCK=NONE; +ALTER TABLE t2 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t2 DROP INDEX v, LOCK=NONE; INSERT INTO t1 SET i=1; ALTER TABLE t1 ADD INDEX(v), LOCK=NONE; -ALTER TABLE t1 ADD COLUMN k INT, LOCK=NONE; +ALTER TABLE t1 ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t1 DROP k, LOCK=NONE; +ALTER TABLE t1 DROP k, ALGORITHM=INPLACE, LOCK=NONE; ERROR 42000: Can't DROP COLUMN `k`; check that it exists ALTER TABLE t1 DROP INDEX v, LOCK=NONE; connect ddl,localhost,root,,test; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_basic.test b/mysql-test/suite/gcol/t/innodb_virtual_basic.test index 32598758..cb4f2295 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_basic.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_basic.test @@ -886,12 +886,12 @@ INSERT INTO t1 VALUES (18, 1, DEFAULT, 'mm'); INSERT INTO t1 VALUES (28, 1, DEFAULT, 'mm'); --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 ADD INDEX idx12 (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 ADD INDEX idx12 (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; ALTER TABLE t1 ADD INDEX idx12 (c), LOCK=NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c) , FORCE, LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), FORCE, ALGORITHM = INPLACE, LOCK=NONE; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), LOCK=NONE; +ALTER TABLE t1 DROP COLUMN h, ADD INDEX idx (c), ALGORITHM = INPLACE, LOCK=NONE; DROP TABLE t1 ; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_debug.test b/mysql-test/suite/gcol/t/innodb_virtual_debug.test index c359f3c8..337af3e5 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_debug.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_debug.test @@ -58,7 +58,7 @@ SHOW CREATE TABLE t; SELECT * FROM t; --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -ALTER TABLE t FORCE, LOCK=NONE; +ALTER TABLE t FORCE, ALGORITHM=INPLACE, LOCK=NONE; if (0) {# MDEV-14341 TODO: re-enable this SET DEBUG_SYNC = 'innodb_inplace_alter_table_enter SIGNAL start_create WAIT_FOR go_ahead'; --send ALTER TABLE t FORCE diff --git a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test index fe4f5e30..85707ca0 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test @@ -16,9 +16,9 @@ eval INSERT INTO t$n SET i=1; eval ALTER TABLE t$n ADD INDEX(v), LOCK=NONE; # MDEV-17468 FIXME: Fix this, and remove the 2 --error below. --error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON -eval ALTER TABLE t$n ADD COLUMN k INT, LOCK=NONE; +eval ALTER TABLE t$n ADD COLUMN k INT, ALGORITHM=INPLACE, LOCK=NONE; --error ER_CANT_DROP_FIELD_OR_KEY -eval ALTER TABLE t$n DROP k, LOCK=NONE; +eval ALTER TABLE t$n DROP k, ALGORITHM=INPLACE, LOCK=NONE; eval ALTER TABLE t$n DROP INDEX v, LOCK=NONE; dec $n; } |