diff options
Diffstat (limited to 'mysql-test/suite/gcol')
| -rw-r--r-- | mysql-test/suite/gcol/inc/gcol_select.inc | 2 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_bug20746926.result | 8 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_bugfixes.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result | 5 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_keys_innodb.result | 6 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_keys_myisam.result | 8 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_rollback.result | 1 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_select_innodb.result | 32 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_select_myisam.result | 58 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_view_innodb.result | 1 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/gcol_view_myisam.result | 1 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result | 2 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/r/innodb_virtual_rebuild.result | 16 | ||||
| -rw-r--r-- | mysql-test/suite/gcol/t/innodb_virtual_rebuild.test | 5 |
14 files changed, 96 insertions, 51 deletions
diff --git a/mysql-test/suite/gcol/inc/gcol_select.inc b/mysql-test/suite/gcol/inc/gcol_select.inc index 1f54b2bb3ae..939d2f64e84 100644 --- a/mysql-test/suite/gcol/inc/gcol_select.inc +++ b/mysql-test/suite/gcol/inc/gcol_select.inc @@ -40,7 +40,7 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); analyze table t1,t2,t3; --echo # select_type=SIMPLE, type=system diff --git a/mysql-test/suite/gcol/r/gcol_bug20746926.result b/mysql-test/suite/gcol/r/gcol_bug20746926.result index 74fe76b3f1a..590caf02503 100644 --- a/mysql-test/suite/gcol/r/gcol_bug20746926.result +++ b/mysql-test/suite/gcol/r/gcol_bug20746926.result @@ -11,22 +11,22 @@ c timestamp generated always as ((a not in (b,b))) stored insert t1(a) values(7777777777); Warnings: Warning 1265 Data truncated for column 'a' at row 1 -Warning 1292 Incorrect datetime value: '0' +Warning 1292 Truncated incorrect datetime value: '0' show warnings; Level Code Message Warning 1265 Data truncated for column 'a' at row 1 -Warning 1292 Incorrect datetime value: '0' +Warning 1292 Truncated incorrect datetime value: '0' disconnect con1; connect con2,localhost,root,,; set sql_mode=''; insert t1(a) values(6666666666); Warnings: Warning 1265 Data truncated for column 'a' at row 1 -Warning 1292 Incorrect datetime value: '0' +Warning 1292 Truncated incorrect datetime value: '0' show warnings; Level Code Message Warning 1265 Data truncated for column 'a' at row 1 -Warning 1292 Incorrect datetime value: '0' +Warning 1292 Truncated incorrect datetime value: '0' drop table t1; disconnect con2; connection default; diff --git a/mysql-test/suite/gcol/r/gcol_bugfixes.result b/mysql-test/suite/gcol/r/gcol_bugfixes.result index 8eb7a9372b5..1acb343c498 100644 --- a/mysql-test/suite/gcol/r/gcol_bugfixes.result +++ b/mysql-test/suite/gcol/r/gcol_bugfixes.result @@ -231,6 +231,7 @@ Note 1265 Data truncated for column 'col_time_key' at row 5 Note 1265 Data truncated for column 'col_time_key' at row 6 ANALYZE TABLE c; Table Op Msg_type Msg_text +test.c analyze status Engine-independent statistics collected test.c analyze status OK explain SELECT COUNT(DISTINCT col_varchar_key) AS x FROM c @@ -327,6 +328,7 @@ ALTER TABLE t1 ADD COLUMN c INT GENERATED ALWAYS AS (b + 1) VIRTUAL; ALTER TABLE t1 ADD INDEX( c ); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK # Make sure the index is correct. That's kinda important. EXPLAIN diff --git a/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result b/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result index c7e5cab4f8c..210c6450b70 100644 --- a/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_ins_upd_myisam.result @@ -549,8 +549,13 @@ a BLOB GENERATED ALWAYS AS ('') VIRTUAL, b TIMESTAMP(4) GENERATED ALWAYS AS ('') VIRTUAL, KEY (a(183),b) ); +Warnings: +Warning 1901 Function or expression '''' cannot be used in the GENERATED ALWAYS AS clause of `b` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL INSERT IGNORE INTO t VALUES(), (), (); Warnings: +Warning 1901 Function or expression '''' cannot be used in the GENERATED ALWAYS AS clause of `b` +Warning 1105 Expression depends on the @@sql_mode value TIME_ROUND_FRACTIONAL Warning 1265 Data truncated for column 'b' at row 1 Warning 1265 Data truncated for column 'b' at row 2 Warning 1265 Data truncated for column 'b' at row 3 diff --git a/mysql-test/suite/gcol/r/gcol_keys_innodb.result b/mysql-test/suite/gcol/r/gcol_keys_innodb.result index 05bc8347e5f..0249dbb2d87 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_keys_innodb.result @@ -348,6 +348,7 @@ CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc)); INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK # Should use index SELECT * FROM t1 WHERE f1 + 1 > 7; @@ -455,6 +456,7 @@ INSERT INTO t1(f1) VALUES (070707),(080808); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT * FROM t1 WHERE f1 + 1 > 070707; f1 gc_int gc_date @@ -489,6 +491,7 @@ KEY col_int_gc_key(col_int_gc_key) INSERT INTO t1 ( col_int_key) VALUES (7); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk)) @@ -727,6 +730,7 @@ INDEX(gc_case)); INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1); ANALYZE TABLE t; Table Op Msg_type Msg_text +test.t analyze status Engine-independent statistics collected test.t analyze status OK EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -809,7 +813,9 @@ CREATE TABLE t2 (b INTEGER); INSERT INTO t2 VALUES (1); ANALYZE TABLE t1, t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK # Used to choose the index on a1 and get wrong results. EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0; diff --git a/mysql-test/suite/gcol/r/gcol_keys_myisam.result b/mysql-test/suite/gcol/r/gcol_keys_myisam.result index a91a77aedf3..3f00d344901 100644 --- a/mysql-test/suite/gcol/r/gcol_keys_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_keys_myisam.result @@ -205,7 +205,7 @@ outr.col_varchar_nokey in ('c', 'x', 'i') AND (outr.col_time_key IS NULL OR outr.col_datetime_key = '2009-09-27'); id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE outr ALL col_time_key,col_datetime_key NULL NULL NULL 4 x +1 SIMPLE outr index_merge col_time_key,col_datetime_key col_time_key,col_datetime_key 4,6 NULL 2 x SELECT outr.col_time_key AS x FROM c AS outr @@ -348,6 +348,7 @@ CREATE TABLE t1 (f1 int, gc int AS (f1 + 1) STORED, UNIQUE(gc)); INSERT INTO t1(f1) VALUES (1),(2),(0),(9),(3),(4),(8),(7),(5),(6); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK # Should use index SELECT * FROM t1 WHERE f1 + 1 > 7; @@ -455,6 +456,7 @@ INSERT INTO t1(f1) VALUES (070707),(080808); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT * FROM t1 WHERE f1 + 1 > 070707; f1 gc_int gc_date @@ -489,6 +491,7 @@ KEY col_int_gc_key(col_int_gc_key) INSERT INTO t1 ( col_int_key) VALUES (7); ANALYZE TABLE t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK SELECT table1.col_int_key + 1 AS field1, table2.col_int_key AS field2 FROM (t1 AS table1 JOIN t1 AS table2 ON (table2.pk = table1.pk)) @@ -727,6 +730,7 @@ INDEX(gc_case)); INSERT INTO t (a, b) VALUES (0, 0), (0, 1), (1, 0), (1, 1); ANALYZE TABLE t; Table Op Msg_type Msg_text +test.t analyze status Engine-independent statistics collected test.t analyze status OK EXPLAIN SELECT a, b FROM t WHERE (a AND b) = 1; id select_type table type possible_keys key key_len ref rows Extra @@ -809,7 +813,9 @@ CREATE TABLE t2 (b INTEGER); INSERT INTO t2 VALUES (1); ANALYZE TABLE t1, t2; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK # Used to choose the index on a1 and get wrong results. EXPLAIN SELECT * FROM t1 WHERE (a2 AND a2) = 0; diff --git a/mysql-test/suite/gcol/r/gcol_rollback.result b/mysql-test/suite/gcol/r/gcol_rollback.result index 3fe40d1eccd..5ee94d3ef44 100644 --- a/mysql-test/suite/gcol/r/gcol_rollback.result +++ b/mysql-test/suite/gcol/r/gcol_rollback.result @@ -18,6 +18,7 @@ a b 9 9 BEGIN; INSERT INTO t (a) VALUES (10); +# restart SELECT * FROM t; a b 9 9 diff --git a/mysql-test/suite/gcol/r/gcol_select_innodb.result b/mysql-test/suite/gcol/r/gcol_select_innodb.result index 81194cf7e13..4e308bc8fc5 100644 --- a/mysql-test/suite/gcol/r/gcol_select_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_select_innodb.result @@ -17,11 +17,14 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); analyze table t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK # select_type=SIMPLE, type=system select * from t2; @@ -76,8 +79,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -86,7 +89,7 @@ a b c 2 -2 -2 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index 1 PRIMARY t1 ALL c NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> @@ -170,7 +173,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed gcol expr> select * from t3 where c between -2 and -1; a b c @@ -233,7 +236,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 index NULL PRIMARY 4 NULL 3 Using where +1 SIMPLE t3 index NULL PRIMARY 4 NULL 6 Using where # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -241,7 +244,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -257,7 +260,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> select * from t3 where c between -2 and -1 order by c; a b c @@ -493,6 +496,7 @@ Warning 1292 Truncated incorrect DOUBLE value: 'c' Warning 1292 Truncated incorrect DOUBLE value: 't' Warning 1292 Truncated incorrect DOUBLE value: 'm' Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DOUBLE value: 'd' DROP TABLE cc; SET sql_mode=@save_old_sql_mode; # @@ -676,9 +680,13 @@ INSERT INTO t4 VALUES (1, 'j'), (2, 'c'), (0, 'a'); ANALYZE TABLE t1, t2, t3, t4; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK +test.t4 analyze status Engine-independent statistics collected test.t4 analyze status OK EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 @@ -692,7 +700,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -747,7 +755,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -803,7 +811,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -867,7 +875,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where diff --git a/mysql-test/suite/gcol/r/gcol_select_myisam.result b/mysql-test/suite/gcol/r/gcol_select_myisam.result index 05d9fa3a244..81632c8b3cf 100644 --- a/mysql-test/suite/gcol/r/gcol_select_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_select_myisam.result @@ -17,11 +17,14 @@ insert into t2 (a) values (1); create table t3 (a int primary key, b int generated always as (-a) virtual, c int generated always as (-a) stored unique); -insert into t3 (a) values (2),(1),(3); +insert into t3 (a) values (2),(1),(3),(5),(4),(7); analyze table t1,t2,t3; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK # select_type=SIMPLE, type=system select * from t2; @@ -57,7 +60,7 @@ a b c 1 -1 -1 explain select * from t3 where c>=-1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 2 Using index condition +1 SIMPLE t3 range c c 5 NULL 1 Using index condition # select_type=SIMPLE, type=ref select * from t1,t3 where t1.c=t3.c and t3.c=-1; a b c a b c @@ -76,8 +79,8 @@ a b c 3 -3 -3 explain select * from t1 where b in (select c from t3); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using index -1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where; Using join buffer (flat, BNL join) +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY t3 eq_ref c c 5 test.t1.b 1 Using index # select_type=PRIMARY, type=range,ref select * from t1 where c in (select c from t3 where c between -2 and -1); a b c @@ -86,7 +89,7 @@ a b c 2 -2 -2 explain select * from t1 where c in (select c from t3 where c between -2 and -1); id select_type table type possible_keys key key_len ref rows Extra -1 PRIMARY t3 index c c 5 NULL 3 Using where; Using index +1 PRIMARY t3 index c c 5 NULL 6 Using where; Using index 1 PRIMARY t1 ref c c 5 test.t3.c 1 # select_type=UNION, type=system # select_type=UNION RESULT, type=<union1,2> @@ -162,7 +165,7 @@ a b c 2 -2 -2 explain select * from t3 where a between 1 and 2; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> select * from t3 where b between -2 and -1; a b c @@ -170,7 +173,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where # SELECT * FROM tbl_name WHERE <indexed gcol expr> select * from t3 where c between -2 and -1; a b c @@ -178,7 +181,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # bug#20022189: WL411:DEBUG ASSERT AT FIELD_LONG::VAL_INT IN SQL/FIELD.CC CREATE TABLE t4 ( `pk` int(11) NOT NULL , @@ -208,7 +211,7 @@ a b c 1 -1 -1 explain select * from t3 where a between 1 and 2 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range PRIMARY PRIMARY 4 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-gcol> select * from t3 where b between -2 and -1 order by a; a b c @@ -216,7 +219,7 @@ a b c 2 -2 -2 explain select * from t3 where b between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-gcol> select * from t3 where c between -2 and -1 order by a; a b c @@ -224,7 +227,7 @@ a b c 2 -2 -2 explain select * from t3 where c between -2 and -1 order by a; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where b between -2 and -1 order by b; a b c @@ -232,7 +235,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <non-indexed gcol> select * from t3 where c between -2 and -1 order by b; a b c @@ -240,7 +243,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by b; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition; Using filesort +1 SIMPLE t3 range c c 5 NULL 2 Using index condition; Using filesort # SELECT * FROM tbl_name WHERE <non-indexed gcol expr> ORDER BY <indexed gcol> select * from t3 where b between -2 and -1 order by c; a b c @@ -248,7 +251,7 @@ a b c 1 -1 -1 explain select * from t3 where b between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using filesort +1 SIMPLE t3 ALL NULL NULL NULL NULL 6 Using where; Using filesort # SELECT * FROM tbl_name WHERE <indexed gcol expr> ORDER BY <indexed gcol> select * from t3 where c between -2 and -1 order by c; a b c @@ -256,7 +259,7 @@ a b c 1 -1 -1 explain select * from t3 where c between -2 and -1 order by c; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t3 range c c 5 NULL 1 Using index condition +1 SIMPLE t3 range c c 5 NULL 2 Using index condition # SELECT sum(<non-indexed gcol>) FROM tbl_name GROUP BY <non-indexed gcol> select sum(b) from t1 group by b; sum(b) @@ -791,15 +794,15 @@ 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; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE cc ALL col_int_key NULL NULL NULL 6 # +1 SIMPLE cc range col_int_key col_int_key 5 NULL 3 # SELECT pk FROM cc WHERE col_int_key > 3; pk -3 5 6 +3 EXPLAIN SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE cc ALL col_int_key NULL NULL NULL 6 # +1 SIMPLE cc range col_int_key col_int_key 5 NULL 3 # SELECT pk FROM cc WHERE col_int_key > 3 ORDER BY 1; pk 3 @@ -853,7 +856,9 @@ Warnings: Note 1265 Data truncated for column 'col_time_key' at row 1 ANALYZE TABLE a, c; Table Op Msg_type Msg_text +test.a analyze status Engine-independent statistics collected test.a analyze status OK +test.c analyze status Engine-independent statistics collected test.c analyze status OK EXPLAIN SELECT @@ -931,7 +936,9 @@ col_varchar_nokey (5, 'b'),(8,'m'),(7, 'j'),(2, 'v'); ANALYZE TABLE c, cc; Table Op Msg_type Msg_text +test.c analyze status Engine-independent statistics collected test.c analyze status OK +test.cc analyze status Engine-independent statistics collected test.cc analyze status OK EXPLAIN SELECT alias2 . col_varchar_key AS field1 @@ -1073,6 +1080,7 @@ Warning 1292 Truncated incorrect DOUBLE value: 'c' Warning 1292 Truncated incorrect DOUBLE value: 't' Warning 1292 Truncated incorrect DOUBLE value: 'm' Warning 1292 Truncated incorrect DOUBLE value: 'd' +Warning 1292 Truncated incorrect DOUBLE value: 'd' DROP TABLE cc; SET sql_mode=@save_old_sql_mode; # @@ -1204,7 +1212,7 @@ FROM t0 AS a0, t0 AS a1, t0 AS a2; EXPLAIN SELECT * FROM t1 WHERE i1 > 41 AND i1 <= 43; id select_type table type possible_keys key key_len ref rows Extra -1 SIMPLE t1 range idx idx 4 NULL 19 Using index condition +1 SIMPLE t1 range idx idx 4 NULL 20 Using index condition SELECT * FROM t1 WHERE i1 > 41 AND i1 <= 43; pk i1 i2 v1 v2 @@ -1298,9 +1306,13 @@ INSERT INTO t4 VALUES (1, 'j'), (2, 'c'), (0, 'a'); ANALYZE TABLE t1, t2, t3, t4; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK +test.t2 analyze status Engine-independent statistics collected test.t2 analyze status OK +test.t3 analyze status Engine-independent statistics collected test.t3 analyze status OK +test.t4 analyze status Engine-independent statistics collected test.t4 analyze status OK EXPLAIN SELECT /*+ NO_SEMIJOIN(@subq1) */ t1.c1, t2.i1 FROM t1 STRAIGHT_JOIN t3 STRAIGHT_JOIN t2 @@ -1314,7 +1326,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -1370,7 +1382,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -1428,7 +1440,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where @@ -1495,7 +1507,7 @@ 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 3 +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 t2 ALL NULL NULL NULL NULL 5 Using join buffer (flat, BNL join) 2 MATERIALIZED t4 ALL NULL NULL NULL NULL 3 Using where diff --git a/mysql-test/suite/gcol/r/gcol_view_innodb.result b/mysql-test/suite/gcol/r/gcol_view_innodb.result index ec82c792493..b23dbfc4bff 100644 --- a/mysql-test/suite/gcol/r/gcol_view_innodb.result +++ b/mysql-test/suite/gcol/r/gcol_view_innodb.result @@ -5,6 +5,7 @@ c int generated always as (-a) stored); insert into t1 (a) values (1), (1), (2), (2), (3); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create view v1 (d,e) as select abs(b), abs(c) from t1; select d,e from v1; diff --git a/mysql-test/suite/gcol/r/gcol_view_myisam.result b/mysql-test/suite/gcol/r/gcol_view_myisam.result index 13cb74ebcb5..264bd904c30 100644 --- a/mysql-test/suite/gcol/r/gcol_view_myisam.result +++ b/mysql-test/suite/gcol/r/gcol_view_myisam.result @@ -5,6 +5,7 @@ c int generated always as (-a) stored); insert into t1 (a) values (1), (1), (2), (2), (3); analyze table t1; Table Op Msg_type Msg_text +test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK create view v1 (d,e) as select abs(b), abs(c) from t1; select d,e from v1; diff --git a/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result b/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result index 1d4766eab19..c45579aec2b 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_fk_restart.result @@ -14,6 +14,7 @@ INSERT INTO u1 SET a=1; INSERT INTO u2 SET b=1; INSERT INTO t1(fld1) VALUES(1); INSERT INTO t2(fld1, fld2) VALUES(1, 2); +# restart UPDATE t1 SET fld1= 2; DELETE FROM u1; SELECT * FROM u2; @@ -37,6 +38,7 @@ FOREIGN KEY(fld1) REFERENCES t1(fld1) ON UPDATE CASCADE) engine=innodb; INSERT INTO t1 VALUES(1), (2); INSERT INTO t2 VALUES(1, DEFAULT), (2, default); +# restart CREATE TEMPORARY TABLE t2 (fld1 INT NOT NULL)ENGINE=INNODB; UPDATE t1 SET fld1= 3 WHERE fld1= 2; connect con1,localhost,root,,test; diff --git a/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result b/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result index 2e35698e47e..35f37034721 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_rebuild.result @@ -7,31 +7,31 @@ 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 DROP k, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t4 DROP INDEX v, LOCK=NONE; ALTER TABLE t4 DROP k, 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 DROP k, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t3 DROP INDEX v, LOCK=NONE; ALTER TABLE t3 DROP k, 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 DROP k, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t2 DROP INDEX v, LOCK=NONE; ALTER TABLE t2 DROP k, 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 DROP k, LOCK=NONE; ERROR 0A000: LOCK=NONE is not supported. Reason: online rebuild with indexed virtual columns. Try LOCK=SHARED -ALTER TABLE t1 DROP INDEX v, LOCK=NONE; ALTER TABLE t1 DROP k, 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; connection default; connection ddl; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test index 37ab82c46db..fe4f5e307b3 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_rebuild.test @@ -14,11 +14,12 @@ while ($n) { eval INSERT INTO t$n SET i=1; eval ALTER TABLE t$n ADD INDEX(v), LOCK=NONE; -eval ALTER TABLE t$n ADD COLUMN k INT, 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; +--error ER_CANT_DROP_FIELD_OR_KEY eval ALTER TABLE t$n DROP k, LOCK=NONE; eval ALTER TABLE t$n DROP INDEX v, LOCK=NONE; -eval ALTER TABLE t$n DROP k, LOCK=NONE; dec $n; } |