diff options
| author | Marko Mäkelä <marko.makela@mariadb.com> | 2018-05-17 08:42:53 +0300 |
|---|---|---|
| committer | Marko Mäkelä <marko.makela@mariadb.com> | 2018-05-17 08:42:53 +0300 |
| commit | 4c7608aeb187383f2629e96e085b5b50fc81337f (patch) | |
| tree | fc8dce68b02a63ca204203034dae81a7162a4b9f /mysql-test | |
| parent | c2352c45fbd670f50b73415eeeb676aa67fb4a29 (diff) | |
| parent | a4e7800701d0764fe4cbb85b81d7c7cb54677334 (diff) | |
| download | mariadb-git-4c7608aeb187383f2629e96e085b5b50fc81337f.tar.gz | |
Merge 10.2 into 10.3
Diffstat (limited to 'mysql-test')
42 files changed, 2366 insertions, 31 deletions
diff --git a/mysql-test/main/alter_table_errors.result b/mysql-test/main/alter_table_errors.result new file mode 100644 index 00000000000..020a30304d0 --- /dev/null +++ b/mysql-test/main/alter_table_errors.result @@ -0,0 +1,10 @@ +create table t (a int, v int as (a)) engine=innodb; +alter table t change column a b tinyint, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t; diff --git a/mysql-test/main/alter_table_errors.test b/mysql-test/main/alter_table_errors.test new file mode 100644 index 00000000000..d9982ac26f4 --- /dev/null +++ b/mysql-test/main/alter_table_errors.test @@ -0,0 +1,10 @@ +--source include/have_innodb.inc + +# +# MDEV-16110 ALTER with ALGORITHM=INPLACE breaks temporary table with virtual columns +# +create table t (a int, v int as (a)) engine=innodb; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t change column a b tinyint, algorithm=inplace; +show create table t; +drop table t; diff --git a/mysql-test/main/check_constraint.result b/mysql-test/main/check_constraint.result index 70d64cd6ff7..9a32e6f12bc 100644 --- a/mysql-test/main/check_constraint.result +++ b/mysql-test/main/check_constraint.result @@ -156,3 +156,44 @@ create table t1 (id int auto_increment primary key, datecol datetime, check (dat insert into t1 (datecol) values (now()); insert into t1 (datecol) values (now()); drop table t1; +CREATE TABLE t1 ( +EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, +FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) +); +INSERT INTO t1 VALUES (NULL, 'Ken'); +ERROR 22007: Truncated incorrect DOUBLE value: 'Ken' +SHOW WARNINGS; +Level Code Message +Error 1292 Truncated incorrect DOUBLE value: 'Ken' +Error 4025 CONSTRAINT `FirstName` failed for `test`.`t1` +INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); +ERROR 22007: Truncated incorrect DOUBLE value: 'Ken' +SHOW WARNINGS; +Level Code Message +Error 1292 Truncated incorrect DOUBLE value: 'Ken' +Error 4025 CONSTRAINT `FirstName` failed for `test`.`t1` +INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'); +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'Ken' +INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'Ken' +Warning 1292 Truncated incorrect DOUBLE value: 'Brian' +set sql_mode=""; +INSERT INTO t1 VALUES (NULL, 'Ken'); +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'Ken' +INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); +Warnings: +Warning 1292 Truncated incorrect DOUBLE value: 'Ken' +Warning 1292 Truncated incorrect DOUBLE value: 'Brian' +set sql_mode=default; +select * from t1; +EmployeeID FirstName +1 Ken +2 Ken +3 Brian +4 Ken +5 Ken +6 Brian +drop table t1; diff --git a/mysql-test/main/check_constraint.test b/mysql-test/main/check_constraint.test index 9a77736acd7..02081071bd4 100644 --- a/mysql-test/main/check_constraint.test +++ b/mysql-test/main/check_constraint.test @@ -111,3 +111,27 @@ create table t1 (id int auto_increment primary key, datecol datetime, check (dat insert into t1 (datecol) values (now()); insert into t1 (datecol) values (now()); drop table t1; + +# +# MDEV-15461 Check Constraints with binary logging makes insert inconsistent +# + +CREATE TABLE t1 ( + EmployeeID SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, + FirstName VARCHAR(30) NOT NULL CHECK (CHAR_LENGTH(FirstName > 2)) +); + +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (NULL, 'Ken'); +SHOW WARNINGS; +--error ER_TRUNCATED_WRONG_VALUE +INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); +SHOW WARNINGS; +INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'); +INSERT IGNORE INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); +set sql_mode=""; +INSERT INTO t1 VALUES (NULL, 'Ken'); +INSERT INTO t1 VALUES (NULL, 'Ken'),(NULL, 'Brian'); +set sql_mode=default; +select * from t1; +drop table t1; diff --git a/mysql-test/main/derived_cond_pushdown.result b/mysql-test/main/derived_cond_pushdown.result index 326e56b222f..867da5bbbf3 100644 --- a/mysql-test/main/derived_cond_pushdown.result +++ b/mysql-test/main/derived_cond_pushdown.result @@ -12778,6 +12778,434 @@ where t.d between date ('2017-01-01') and date ('2019-01-01'); d 2018-01-01 # +# MDEV-16088: pushdown into derived defined in the IN subquery +# +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (e INT, f INT, g INT); +INSERT INTO t1 VALUES (1,14),(2,13),(1,19),(2,32),(3,24); +INSERT INTO t2 VALUES (1,19,2),(3,24,1),(1,12,2),(3,11,3),(2,32,1); +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e,d_tab.max_f +FROM ( +SELECT t2.e, MAX(t2.f) AS max_f +FROM t2 +GROUP BY t2.e +HAVING max_f>18 +) as d_tab +WHERE d_tab.e>1 +) +; +a b +2 32 +3 24 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e,d_tab.max_f +FROM ( +SELECT t2.e, MAX(t2.f) AS max_f +FROM t2 +GROUP BY t2.e +HAVING max_f>18 +) as d_tab +WHERE d_tab.e>1 +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where +3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e,d_tab.max_f +FROM ( +SELECT t2.e, MAX(t2.f) AS max_f +FROM t2 +GROUP BY t2.e +HAVING max_f>18 +) as d_tab +WHERE d_tab.e>1 +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "max_f"], + "ref": ["func", "func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_f > 18", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.e > 1" + } + } + } + } + } + } + } + } + } + } +} +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e,d_tab.max_f +FROM ( +SELECT t2.e, MAX(t2.f) AS max_f +FROM t2 +GROUP BY t2.e +HAVING max_f>18 +) as d_tab +WHERE d_tab.max_f<25 +) +; +a b +1 19 +3 24 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e,d_tab.max_f +FROM ( +SELECT t2.e, MAX(t2.f) AS max_f +FROM t2 +GROUP BY t2.e +HAVING max_f>18 +) as d_tab +WHERE d_tab.max_f<25 +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 func,func 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where +3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e,d_tab.max_f +FROM ( +SELECT t2.e, MAX(t2.f) AS max_f +FROM t2 +GROUP BY t2.e +HAVING max_f>18 +) as d_tab +WHERE d_tab.max_f<25 +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "max_f"], + "ref": ["func", "func"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "d_tab.max_f < 25", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_f > 18 and max_f < 25", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } +} +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e, MAX(d_tab.max_f) AS max_f +FROM ( +SELECT t2.e, MAX(t2.f) as max_f, t2.g +FROM t2 +GROUP BY t2.e +) as d_tab +WHERE d_tab.e>1 +GROUP BY d_tab.g +) +; +a b +2 32 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e, MAX(d_tab.max_f) AS max_f +FROM ( +SELECT t2.e, MAX(t2.f) as max_f, t2.g +FROM t2 +GROUP BY t2.e +) as d_tab +WHERE d_tab.e>1 +GROUP BY d_tab.g +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using where; Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e, MAX(d_tab.max_f) AS max_f +FROM ( +SELECT t2.e, MAX(t2.f) as max_f, t2.g +FROM t2 +GROUP BY t2.e +) as d_tab +WHERE d_tab.e>1 +GROUP BY d_tab.g +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a is not null and t1.b is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "max_f"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "d_tab.e > 1", + "materialized": { + "query_block": { + "select_id": 3, + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t2.e > 1" + } + } + } + } + } + } + } + } + } + } + } +} +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e, MAX(d_tab.max_f) AS max_f +FROM ( +SELECT t2.e, MAX(t2.f) as max_f, t2.g +FROM t2 +GROUP BY t2.e +) as d_tab +WHERE d_tab.max_f>20 +GROUP BY d_tab.g +) +; +a b +2 32 +EXPLAIN SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e, MAX(d_tab.max_f) AS max_f +FROM ( +SELECT t2.e, MAX(t2.f) as max_f, t2.g +FROM t2 +GROUP BY t2.e +) as d_tab +WHERE d_tab.max_f>20 +GROUP BY d_tab.g +) +; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 Using where +1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 8 test.t1.a,test.t1.b 1 +2 MATERIALIZED <derived3> ALL NULL NULL NULL NULL 5 Using where; Using temporary +3 DERIVED t2 ALL NULL NULL NULL NULL 5 Using temporary; Using filesort +EXPLAIN FORMAT=JSON SELECT * FROM t1 +WHERE (t1.a,t1.b) IN +( +SELECT d_tab.e, MAX(d_tab.max_f) AS max_f +FROM ( +SELECT t2.e, MAX(t2.f) as max_f, t2.g +FROM t2 +GROUP BY t2.e +) as d_tab +WHERE d_tab.max_f>20 +GROUP BY d_tab.g +) +; +EXPLAIN +{ + "query_block": { + "select_id": 1, + "table": { + "table_name": "t1", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "t1.a is not null and t1.b is not null" + }, + "table": { + "table_name": "<subquery2>", + "access_type": "eq_ref", + "possible_keys": ["distinct_key"], + "key": "distinct_key", + "key_length": "8", + "used_key_parts": ["e", "max_f"], + "ref": ["test.t1.a", "test.t1.b"], + "rows": 1, + "filtered": 100, + "materialized": { + "unique": 1, + "query_block": { + "select_id": 2, + "temporary_table": { + "table": { + "table_name": "<derived3>", + "access_type": "ALL", + "rows": 5, + "filtered": 100, + "attached_condition": "d_tab.max_f > 20", + "materialized": { + "query_block": { + "select_id": 3, + "having_condition": "max_f > 20", + "filesort": { + "sort_key": "t2.e", + "temporary_table": { + "table": { + "table_name": "t2", + "access_type": "ALL", + "rows": 5, + "filtered": 100 + } + } + } + } + } + } + } + } + } + } + } +} +DROP TABLE t1,t2; +# +# MDEV-15765: pushing condition with IN subquery defined with constants +# using substitution +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT * FROM +( +SELECT DISTINCT * FROM t1 +) der_tab +WHERE (a>0 AND a<2 OR a IN (2,3)) AND +(a=2 OR 0); +a +2 +DROP TABLE t1; +# # MDEV-10855: Pushdown into derived with window functions # set @save_optimizer_switch= @@optimizer_switch; diff --git a/mysql-test/main/derived_cond_pushdown.test b/mysql-test/main/derived_cond_pushdown.test index 596641299ae..d523ea3916f 100644 --- a/mysql-test/main/derived_cond_pushdown.test +++ b/mysql-test/main/derived_cond_pushdown.test @@ -2221,6 +2221,105 @@ select * from (select date('2018-01-01') as d select * from (select date('2018-01-01') as d) as t where t.d between date ('2017-01-01') and date ('2019-01-01'); +--echo # +--echo # MDEV-16088: pushdown into derived defined in the IN subquery +--echo # + +CREATE TABLE t1 (a INT, b INT); +CREATE TABLE t2 (e INT, f INT, g INT); +INSERT INTO t1 VALUES (1,14),(2,13),(1,19),(2,32),(3,24); +INSERT INTO t2 VALUES (1,19,2),(3,24,1),(1,12,2),(3,11,3),(2,32,1); + +LET $query= +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN + ( + SELECT d_tab.e,d_tab.max_f + FROM ( + SELECT t2.e, MAX(t2.f) AS max_f + FROM t2 + GROUP BY t2.e + HAVING max_f>18 + ) as d_tab + WHERE d_tab.e>1 + ) +; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN + ( + SELECT d_tab.e,d_tab.max_f + FROM ( + SELECT t2.e, MAX(t2.f) AS max_f + FROM t2 + GROUP BY t2.e + HAVING max_f>18 + ) as d_tab + WHERE d_tab.max_f<25 + ) +; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN + ( + SELECT d_tab.e, MAX(d_tab.max_f) AS max_f + FROM ( + SELECT t2.e, MAX(t2.f) as max_f, t2.g + FROM t2 + GROUP BY t2.e + ) as d_tab + WHERE d_tab.e>1 + GROUP BY d_tab.g + ) +; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +LET $query= +SELECT * FROM t1 +WHERE (t1.a,t1.b) IN + ( + SELECT d_tab.e, MAX(d_tab.max_f) AS max_f + FROM ( + SELECT t2.e, MAX(t2.f) as max_f, t2.g + FROM t2 + GROUP BY t2.e + ) as d_tab + WHERE d_tab.max_f>20 + GROUP BY d_tab.g + ) +; +EVAL $query; +EVAL EXPLAIN $query; +EVAL EXPLAIN FORMAT=JSON $query; + +DROP TABLE t1,t2; + +--echo # +--echo # MDEV-15765: pushing condition with IN subquery defined with constants +--echo # using substitution +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +SELECT * FROM +( + SELECT DISTINCT * FROM t1 +) der_tab +WHERE (a>0 AND a<2 OR a IN (2,3)) AND + (a=2 OR 0); + +DROP TABLE t1; + # Start of 10.3 tests --echo # diff --git a/mysql-test/main/derived_view.result b/mysql-test/main/derived_view.result index 85e56ff176e..6c4b3310e11 100644 --- a/mysql-test/main/derived_view.result +++ b/mysql-test/main/derived_view.result @@ -1841,7 +1841,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; Start temporary; End temporary -3 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); @@ -1856,7 +1856,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY <derived3> ref key1 key1 8 const,const 0 Start temporary 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) -3 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); diff --git a/mysql-test/main/distinct.result b/mysql-test/main/distinct.result index 926dc17d0c4..70bce519ad2 100644 --- a/mysql-test/main/distinct.result +++ b/mysql-test/main/distinct.result @@ -1039,4 +1039,14 @@ count(distinct case when id<=63 then id end) 63 drop table tb; SET @@tmp_table_size= @tmp_table_size_save; +# +# MDEV-14695: Assertion `n < m_size' failed in Bounds_checked_array<Element_type>::operator +# +CREATE TABLE t1 (b1 BIT, b2 BIT, b3 BIT, b4 BIT , b5 BIT, b6 BIT); +INSERT INTO t1 VALUES (1,0,0,1,0,1),(0,1,0,0,1,0); +SELECT DISTINCT b1+'0', b2+'0', b3+'0', b4+'0', b5+'0', b6 +'0' FROM t1; +b1+'0' b2+'0' b3+'0' b4+'0' b5+'0' b6 +'0' +1 0 0 1 0 1 +0 1 0 0 1 0 +DROP TABLE t1; End of 5.5 tests diff --git a/mysql-test/main/distinct.test b/mysql-test/main/distinct.test index 7cf3d6810bb..c11f8b501bc 100644 --- a/mysql-test/main/distinct.test +++ b/mysql-test/main/distinct.test @@ -790,4 +790,12 @@ drop table tb; SET @@tmp_table_size= @tmp_table_size_save; +--echo # +--echo # MDEV-14695: Assertion `n < m_size' failed in Bounds_checked_array<Element_type>::operator +--echo # + +CREATE TABLE t1 (b1 BIT, b2 BIT, b3 BIT, b4 BIT , b5 BIT, b6 BIT); +INSERT INTO t1 VALUES (1,0,0,1,0,1),(0,1,0,0,1,0); +SELECT DISTINCT b1+'0', b2+'0', b3+'0', b4+'0', b5+'0', b6 +'0' FROM t1; +DROP TABLE t1; --echo End of 5.5 tests diff --git a/mysql-test/main/multi_update.result b/mysql-test/main/multi_update.result index 45239f6e090..c40de47668a 100644 --- a/mysql-test/main/multi_update.result +++ b/mysql-test/main/multi_update.result @@ -968,3 +968,75 @@ NULL 6 7 7 8 8 drop table t1, t2; +create table t1 (i int) engine=memory; +insert t1 values (1),(2); +create table t2 (f int) engine=myisam; +insert t2 values (1),(2); +explain update t1, t2 set f = 126 order by f limit 2; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 +update t1, t2 set f = 126 order by f limit 2; +select * from t2; +f +126 +2 +drop table t1, t2; +create table t0(a int); +insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, key(a)); +insert t1 select a,a,a from t0; +create table t2 as select * from t1; +create table t3 as select * from t1; +select * from t1, t2 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +a b c a b c +0 0 0 0 0 0 +1 1 1 1 1 1 +2 2 2 2 2 2 +3 3 3 3 3 3 +4 4 4 4 4 4 +set optimizer_switch='firstmatch=off'; +explain update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c< t2.c) order by t2.c, t1.c limit 10; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t2 ALL NULL NULL NULL NULL 10 Using temporary; Using filesort +1 PRIMARY t1 ALL a NULL NULL NULL 10 Using where +1 PRIMARY t3 ALL NULL NULL NULL NULL 10 Using where; Start temporary; End temporary +update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +select * from t2; +a b c +0 0 1 +1 1 1 +2 2 1 +3 3 1 +4 4 1 +5 5 5 +6 6 6 +7 7 7 +8 8 8 +9 9 9 +set optimizer_switch=default; +drop table t0,t1,t2,t3; +create table t0 (x int); +create table t1 (a int); +create table t2 (b int, c int default 0); +insert t0 (x) values (0),(10); +insert t1 (a) values (1), (2); +insert t2 (b) values (1), (2); +create view v1 as select t2.b,t2.c from t1, t2 +where t1.a=t2.b and t2.b < 3 with check option; +select * from t0 join v1 on (x=c); +x b c +0 1 0 +0 2 0 +explain update v1,t0 set c=1 where b=1 and x=c order by x,b limit 1; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 Using where; Using temporary; Using filesort +1 SIMPLE t2 ALL NULL NULL NULL NULL 2 Using where +1 SIMPLE t0 ALL NULL NULL NULL NULL 2 Using where +update v1,t0 set c=1 where b<3 and x=c order by x,b limit 1; +select * from v1; +b c +1 1 +2 0 +drop view v1; +drop table t0, t1,t2; diff --git a/mysql-test/main/multi_update.test b/mysql-test/main/multi_update.test index 5feebe87a5a..42e34d1e4a1 100644 --- a/mysql-test/main/multi_update.test +++ b/mysql-test/main/multi_update.test @@ -914,3 +914,49 @@ update t1 set c1=NULL; update t1, t2 set t1.c1=t2.c3 where t1.c3=t2.c3 order by t1.c3 desc limit 2; select * from t1; drop table t1, t2; + +# +# MDEV-14551 Can't find record in table on multi-table update with ORDER BY +# + +# simple test with multi-update and Using temporary: +create table t1 (i int) engine=memory; +insert t1 values (1),(2); +create table t2 (f int) engine=myisam; +insert t2 values (1),(2); +explain update t1, t2 set f = 126 order by f limit 2; +update t1, t2 set f = 126 order by f limit 2; +select * from t2; +drop table t1, t2; + +# test with DuplicateElimination +# (so that keep_current_rowid is set for DuplicateElimination too) +create table t0(a int); +insert t0 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +create table t1 (a int, b int, c int, key(a)); +insert t1 select a,a,a from t0; +create table t2 as select * from t1; +create table t3 as select * from t1; +select * from t1, t2 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +set optimizer_switch='firstmatch=off'; +explain update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c< t2.c) order by t2.c, t1.c limit 10; +update t1, t2 set t2.c=1 where t1.a=t2.a and t1.b in (select b from t3 where t3.c<=t2.c) order by t2.c, t1.c limit 5; +select * from t2; +set optimizer_switch=default; +drop table t0,t1,t2,t3; + +# test WITH CHECK OPTION +create table t0 (x int); +create table t1 (a int); +create table t2 (b int, c int default 0); +insert t0 (x) values (0),(10); +insert t1 (a) values (1), (2); +insert t2 (b) values (1), (2); +create view v1 as select t2.b,t2.c from t1, t2 + where t1.a=t2.b and t2.b < 3 with check option; +select * from t0 join v1 on (x=c); +explain update v1,t0 set c=1 where b=1 and x=c order by x,b limit 1; +update v1,t0 set c=1 where b<3 and x=c order by x,b limit 1; +select * from v1; +drop view v1; +drop table t0, t1,t2; diff --git a/mysql-test/main/subselect-crash_15755.result b/mysql-test/main/subselect-crash_15755.result new file mode 100644 index 00000000000..81b4bd16ab5 --- /dev/null +++ b/mysql-test/main/subselect-crash_15755.result @@ -0,0 +1,317 @@ +set global innodb_stats_persistent= 1; +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create table t1 ( +f1 bigint(20) default 0, +f2 varchar(50) default '', +f3 int(10) default 0, +f4 bigint(20) default 0, +f5 bigint(20) default 0, +f6 varchar(50) default '', +f7 varchar(64) default '', +f8 varchar(30) default '', +f9 varchar(30) default '', +f10 bigint(20) default 0, +f11 bigint(20) default 0, +f12 bigint(20) default 0, +f13 bigint(20) default 0, +f14 varchar(50) default '', +f15 varchar(100) default '', +f16 varchar(30) default '', +f17 varchar(40) default '', +f18 varchar(30) default '', +f19 varchar(10) default '', +f20 varchar(30) default '', +f21 int(10) default 0, +f22 int(10) default 0, +f23 int(10) default 0, +f24 int(10) default 0, +f25 varchar(20) default '', +f26 varchar(20) default '', +f27 varchar(100) default '', +f28 varchar(55) default '', +f29 varchar(20) default '', +f30 varchar(100) default '', +f31 varchar(30) default '', +f32 varchar(20) default '', +f33 int(10) default 0, +f34 int(10) default 0, +f35 varchar(30) default '', +f36 varchar(30) default '', +f37 varchar(30) default '', +f38 varchar(20) default '', +f39 tinyint(4) default 0, +f40 tinyint(4) default 0, +f41 bigint(20) default 0, +f42 varchar(50) default '', +f43 varchar(50) default '', +f44 varchar(50) default '', +f45 int(10) default 0, +f46 tinyint(1) default 0 +) engine=innodb row_format=dynamic; +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select * from t1 where f2 in (select f2 from t1 group by f2 having count(distinct f3) = 1); +f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27 f28 f29 f30 f31 f32 f33 f34 f35 f36 f37 f38 f39 f40 f41 f42 f43 f44 f45 f46 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +drop table t1; +set global innodb_stats_persistent= 0; diff --git a/mysql-test/main/subselect-crash_15755.test b/mysql-test/main/subselect-crash_15755.test new file mode 100644 index 00000000000..79e259d6337 --- /dev/null +++ b/mysql-test/main/subselect-crash_15755.test @@ -0,0 +1,60 @@ +--source include/have_innodb.inc +set global innodb_stats_persistent= 1; +drop table if exists t1; +create table t1 ( + f1 bigint(20) default 0, + f2 varchar(50) default '', + f3 int(10) default 0, + f4 bigint(20) default 0, + f5 bigint(20) default 0, + f6 varchar(50) default '', + f7 varchar(64) default '', + f8 varchar(30) default '', + f9 varchar(30) default '', + f10 bigint(20) default 0, + f11 bigint(20) default 0, + f12 bigint(20) default 0, + f13 bigint(20) default 0, + f14 varchar(50) default '', + f15 varchar(100) default '', + f16 varchar(30) default '', + f17 varchar(40) default '', + f18 varchar(30) default '', + f19 varchar(10) default '', + f20 varchar(30) default '', + f21 int(10) default 0, + f22 int(10) default 0, + f23 int(10) default 0, + f24 int(10) default 0, + f25 varchar(20) default '', + f26 varchar(20) default '', + f27 varchar(100) default '', + f28 varchar(55) default '', + f29 varchar(20) default '', + f30 varchar(100) default '', + f31 varchar(30) default '', + f32 varchar(20) default '', + f33 int(10) default 0, + f34 int(10) default 0, + f35 varchar(30) default '', + f36 varchar(30) default '', + f37 varchar(30) default '', + f38 varchar(20) default '', + f39 tinyint(4) default 0, + f40 tinyint(4) default 0, + f41 bigint(20) default 0, + f42 varchar(50) default '', + f43 varchar(50) default '', + f44 varchar(50) default '', + f45 int(10) default 0, + f46 tinyint(1) default 0 +) engine=innodb row_format=dynamic; + +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select * from t1 where f2 in (select f2 from t1 group by f2 having count(distinct f3) = 1); +drop table t1; +set global innodb_stats_persistent= 0; diff --git a/mysql-test/main/subselect_extra.result b/mysql-test/main/subselect_extra.result index 73642c09324..a3a0f1f9a15 100644 --- a/mysql-test/main/subselect_extra.result +++ b/mysql-test/main/subselect_extra.result @@ -434,7 +434,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 3 Using where; FirstMatch(t3); Using join buffer (flat, BNL join) -3 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); @@ -449,7 +449,7 @@ id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY t2 system NULL NULL NULL NULL 1 1 PRIMARY <derived3> ref key1 key1 8 const,const 0 Start temporary 1 PRIMARY t3 ALL NULL NULL NULL NULL 2 Using where; End temporary; Using join buffer (flat, BNL join) -3 DERIVED t1 ALL NULL NULL NULL NULL 3 +3 DERIVED t1 ALL NULL NULL NULL NULL 3 Using where SELECT * FROM t3 WHERE t3.b IN (SELECT v1.b FROM v1, t2 WHERE t2.c = v1.c AND t2.c = v1.b AND v1.b = t3.c); diff --git a/mysql-test/r/alter_table_errors.result b/mysql-test/r/alter_table_errors.result new file mode 100644 index 00000000000..020a30304d0 --- /dev/null +++ b/mysql-test/r/alter_table_errors.result @@ -0,0 +1,10 @@ +create table t (a int, v int as (a)) engine=innodb; +alter table t change column a b tinyint, algorithm=inplace; +ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY +show create table t; +Table Create Table +t CREATE TABLE `t` ( + `a` int(11) DEFAULT NULL, + `v` int(11) GENERATED ALWAYS AS (`a`) VIRTUAL +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +drop table t; diff --git a/mysql-test/r/subselect-crash_15755.result b/mysql-test/r/subselect-crash_15755.result new file mode 100644 index 00000000000..81b4bd16ab5 --- /dev/null +++ b/mysql-test/r/subselect-crash_15755.result @@ -0,0 +1,317 @@ +set global innodb_stats_persistent= 1; +drop table if exists t1; +Warnings: +Note 1051 Unknown table 'test.t1' +create table t1 ( +f1 bigint(20) default 0, +f2 varchar(50) default '', +f3 int(10) default 0, +f4 bigint(20) default 0, +f5 bigint(20) default 0, +f6 varchar(50) default '', +f7 varchar(64) default '', +f8 varchar(30) default '', +f9 varchar(30) default '', +f10 bigint(20) default 0, +f11 bigint(20) default 0, +f12 bigint(20) default 0, +f13 bigint(20) default 0, +f14 varchar(50) default '', +f15 varchar(100) default '', +f16 varchar(30) default '', +f17 varchar(40) default '', +f18 varchar(30) default '', +f19 varchar(10) default '', +f20 varchar(30) default '', +f21 int(10) default 0, +f22 int(10) default 0, +f23 int(10) default 0, +f24 int(10) default 0, +f25 varchar(20) default '', +f26 varchar(20) default '', +f27 varchar(100) default '', +f28 varchar(55) default '', +f29 varchar(20) default '', +f30 varchar(100) default '', +f31 varchar(30) default '', +f32 varchar(20) default '', +f33 int(10) default 0, +f34 int(10) default 0, +f35 varchar(30) default '', +f36 varchar(30) default '', +f37 varchar(30) default '', +f38 varchar(20) default '', +f39 tinyint(4) default 0, +f40 tinyint(4) default 0, +f41 bigint(20) default 0, +f42 varchar(50) default '', +f43 varchar(50) default '', +f44 varchar(50) default '', +f45 int(10) default 0, +f46 tinyint(1) default 0 +) engine=innodb row_format=dynamic; +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select * from t1 where f2 in (select f2 from t1 group by f2 having count(distinct f3) = 1); +f1 f2 f3 f4 f5 f6 f7 f8 f9 f10 f11 f12 f13 f14 f15 f16 f17 f18 f19 f20 f21 f22 f23 f24 f25 f26 f27 f28 f29 f30 f31 f32 f33 f34 f35 f36 f37 f38 f39 f40 f41 f42 f43 f44 f45 f46 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 +drop table t1; +set global innodb_stats_persistent= 0; diff --git a/mysql-test/suite/federated/assisted_discovery.result b/mysql-test/suite/federated/assisted_discovery.result index d44f69effcd..4818ff7bb02 100644 --- a/mysql-test/suite/federated/assisted_discovery.result +++ b/mysql-test/suite/federated/assisted_discovery.result @@ -72,14 +72,6 @@ t1 CREATE TABLE `t1` ( drop table t1; connection slave; drop table t1; -create or replace table t1 (x int) with system versioning; -connection master; -create table t1 engine=federated connection='mysql://root@127.0.0.1:SLAVE_MYPORT/test/t1'; -ERROR HY000: Engine FEDERATED failed to discover table `test`.`t1` with 'CREATE TABLE `t1` ( - `x` int(11) DEFAULT NULL -) WITH SYSTEM VERSIONING CONNECTION='mysql://root@127.0.0.1:SLAVE_MYPORT/test/t1'' -connection slave; -drop table t1; connection master; DROP TABLE IF EXISTS federated.t1; DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/assisted_discovery.test b/mysql-test/suite/federated/assisted_discovery.test index 7099cfedb23..fa83a2a8e19 100644 --- a/mysql-test/suite/federated/assisted_discovery.test +++ b/mysql-test/suite/federated/assisted_discovery.test @@ -54,14 +54,5 @@ drop table t1; connection slave; drop table t1; -create or replace table t1 (x int) with system versioning; -connection master; ---replace_result $SLAVE_MYPORT SLAVE_MYPORT ---error ER_SQL_DISCOVER_ERROR -eval create table t1 engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/test/t1'; - -connection slave; -drop table t1; - source include/federated_cleanup.inc; diff --git a/mysql-test/suite/federated/federatedx_versioning.result b/mysql-test/suite/federated/federatedx_versioning.result new file mode 100644 index 00000000000..7af5a5f3f0c --- /dev/null +++ b/mysql-test/suite/federated/federatedx_versioning.result @@ -0,0 +1,100 @@ +create or replace table t1 ( +x int, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for system_time (row_start, row_end)) +with system versioning; +create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1'; +show create table tf; +Table Create Table +tf CREATE TABLE `tf` ( + `x` int(11) DEFAULT NULL, + `row_start` SYS_TYPE NOT NULL INVISIBLE DEFAULT 0, + `row_end` SYS_TYPE NOT NULL INVISIBLE DEFAULT 0 +) ENGINE=FEDERATED DEFAULT CHARSET=latin1 CONNECTION='mysql://root@127.0.0.1:MASTER_MYPORT/test/t1' +# INSERT +insert into t1 values (1); +select * from tf; +x +1 +insert into tf (x) values (2); +select * from t1; +x +1 +2 +select * from tf; +x +1 +2 +# UPDATE +update tf set x= x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +x check_row(row_start, row_end) +1 HISTORICAL ROW +2 HISTORICAL ROW +3 CURRENT ROW +4 CURRENT ROW +# DELETE +delete from tf; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +x check_row(row_start, row_end) +1 HISTORICAL ROW +2 HISTORICAL ROW +3 HISTORICAL ROW +4 HISTORICAL ROW +select * from tf; +x +# TRUNCATE +truncate tf; +select * from t1 for system_time all; +x +# REPLACE +create or replace table t2 ( +id int primary key, y int, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for system_time (row_start, row_end)) +with system versioning; +create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:MASTER_MYPORT/test/t2'; +insert t2f (id, y) values (1, 2); +replace t2f (id, y) values (1, 3); +select *, check_row(row_start, row_end) from t2 for system_time all +order by y; +id y check_row(row_start, row_end) +1 2 HISTORICAL ROW +1 3 CURRENT ROW +# VIEW +create or replace view vt1 as select * from tf; +insert into vt1 values (3); +update vt1 set x= x + 1; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +x check_row(row_start, row_end) +3 HISTORICAL ROW +4 CURRENT ROW +delete from vt1; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +x check_row(row_start, row_end) +3 HISTORICAL ROW +4 HISTORICAL ROW +# multi-UPDATE +truncate t1; +truncate t2; +insert into t1 values (1); +insert into t2 values (2, 2); +update tf, t2f set tf.x= 11, t2f.y= 22; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +x check_row(row_start, row_end) +1 HISTORICAL ROW +11 CURRENT ROW +select *, check_row(row_start, row_end) from t2 for system_time all +order by y; +id y check_row(row_start, row_end) +2 2 HISTORICAL ROW +2 22 CURRENT ROW +drop database test; +create database test; diff --git a/mysql-test/suite/federated/federatedx_versioning.test b/mysql-test/suite/federated/federatedx_versioning.test new file mode 100644 index 00000000000..692edb5b00a --- /dev/null +++ b/mysql-test/suite/federated/federatedx_versioning.test @@ -0,0 +1,77 @@ +--source include/not_embedded.inc +--source have_federatedx.inc +--source suite/versioning/engines.inc +--source suite/versioning/common.inc + +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t1 ( + x int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create or replace table tf engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t1'; +--replace_result $MASTER_MYPORT MASTER_MYPORT $sys_datatype_expl SYS_TYPE "'0000-00-00 00:00:00.000000'" 0 +show create table tf; +--echo # INSERT +insert into t1 values (1); +select * from tf; +insert into tf (x) values (2); +select * from t1; +select * from tf; + +--echo # UPDATE +update tf set x= x + 2; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; + +--echo # DELETE +delete from tf; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +select * from tf; + +--echo # TRUNCATE +truncate tf; +select * from t1 for system_time all; + +--echo # REPLACE +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t2 ( + id int primary key, y int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; +--replace_result $MASTER_MYPORT MASTER_MYPORT +eval create or replace table t2f engine=FEDERATED connection='mysql://root@127.0.0.1:$MASTER_MYPORT/test/t2'; +insert t2f (id, y) values (1, 2); +replace t2f (id, y) values (1, 3); +select *, check_row(row_start, row_end) from t2 for system_time all +order by y; + +--echo # VIEW +create or replace view vt1 as select * from tf; +insert into vt1 values (3); +update vt1 set x= x + 1; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +delete from vt1; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; + +--echo # multi-UPDATE +truncate t1; +truncate t2; +insert into t1 values (1); +insert into t2 values (2, 2); +update tf, t2f set tf.x= 11, t2f.y= 22; +select *, check_row(row_start, row_end) from t1 for system_time all +order by x; +select *, check_row(row_start, row_end) from t2 for system_time all +order by y; + +--source suite/versioning/common_finish.inc +drop database test; +create database test; diff --git a/mysql-test/suite/federated/timestamps.result b/mysql-test/suite/federated/timestamps.result new file mode 100644 index 00000000000..9f3be82a4ec --- /dev/null +++ b/mysql-test/suite/federated/timestamps.result @@ -0,0 +1,64 @@ +connect master,127.0.0.1,root,,test,$MASTER_MYPORT,; +connect slave,127.0.0.1,root,,test,$SLAVE_MYPORT,; +connection master; +CREATE DATABASE federated; +connection slave; +CREATE DATABASE federated; +connection slave; +set global time_zone='Europe/Moscow'; +set time_zone='Europe/Moscow'; +create table federated.t1 (dt datetime, ts timestamp, unique(ts)); +connection master; +set time_zone='+01:00'; +create table t1 engine=federated connection='mysql://root@127.0.0.1:SLAVE_PORT/federated/t1'; +set @@timestamp=1; +insert t1 values (now(), now()); +set @@timestamp=2147483647; +insert t1 values (now(), now()); +set @@timestamp=1067121000; +insert t1 values (now(), now()); +set @@timestamp=1067124600; +insert t1 values (now(), now()); +set @@timestamp=0; +select * from t1; +dt ts +1970-01-01 01:00:01 1970-01-01 01:00:01 +2038-01-19 04:14:07 2038-01-19 04:14:07 +2003-10-25 23:30:00 2003-10-25 23:30:00 +2003-10-26 00:30:00 2003-10-26 00:30:00 +delete from t1 where ts='1970-01-01 01:00:01'; +select * from t1; +dt ts +2038-01-19 04:14:07 2038-01-19 04:14:07 +2003-10-25 23:30:00 2003-10-25 23:30:00 +2003-10-26 00:30:00 2003-10-26 00:30:00 +insert t1 values ('1970-01-01 01:00:01', now()); +update t1 set ts=dt; +select * from t1; +dt ts +1970-01-01 01:00:01 1970-01-01 01:00:01 +2038-01-19 04:14:07 2038-01-19 04:14:07 +2003-10-25 23:30:00 2003-10-25 23:30:00 +2003-10-26 00:30:00 2003-10-26 00:30:00 +select * from t1 where ts='2003-10-25 23:30:00'; +dt ts +2003-10-25 23:30:00 2003-10-25 23:30:00 +select * from t1 where ts='2003-10-26 00:30:00'; +dt ts +2003-10-26 00:30:00 2003-10-26 00:30:00 +connection slave; +select * from federated.t1; +dt ts +1970-01-01 01:00:01 1970-01-01 03:00:01 +2038-01-19 04:14:07 2038-01-19 06:14:07 +2003-10-25 23:30:00 2003-10-26 02:30:00 +2003-10-26 00:30:00 2003-10-26 02:30:00 +set global time_zone=default; +connection master; +drop table t1; +connection master; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; +connection slave; +DROP TABLE IF EXISTS federated.t1; +DROP DATABASE IF EXISTS federated; diff --git a/mysql-test/suite/federated/timestamps.test b/mysql-test/suite/federated/timestamps.test new file mode 100644 index 00000000000..7b46b797f2f --- /dev/null +++ b/mysql-test/suite/federated/timestamps.test @@ -0,0 +1,45 @@ +source have_federatedx.inc; +source include/federated.inc; + +connection slave; +set global time_zone='Europe/Moscow'; +set time_zone='Europe/Moscow'; +create table federated.t1 (dt datetime, ts timestamp, unique(ts)); + +connection master; +set time_zone='+01:00'; +replace_result $SLAVE_MYPORT SLAVE_PORT; +eval create table t1 engine=federated connection='mysql://root@127.0.0.1:$SLAVE_MYPORT/federated/t1'; + +set @@timestamp=1; # min value +insert t1 values (now(), now()); +set @@timestamp=2147483647; # max value +insert t1 values (now(), now()); +set @@timestamp=1067121000; # DST ambiguous (in Europe/Moscow) +insert t1 values (now(), now()); +set @@timestamp=1067124600; # DST ambiguous (in Europe/Moscow) +insert t1 values (now(), now()); +set @@timestamp=0; + +# reads +select * from t1; + +# deletes +delete from t1 where ts='1970-01-01 01:00:01'; +select * from t1; + +# updates +insert t1 values ('1970-01-01 01:00:01', now()); +update t1 set ts=dt; +select * from t1; + +# index lookups +select * from t1 where ts='2003-10-25 23:30:00'; +select * from t1 where ts='2003-10-26 00:30:00'; + +connection slave; +select * from federated.t1; +set global time_zone=default; +connection master; +drop table t1; +source include/federated_cleanup.inc; diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index d3c03ee83b4..58d7b6cc8be 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -31,7 +31,5 @@ galera.MW-44 : MDEV-15809 Test failure on galera.MW-44 galera.galera_pc_ignore_sb : MDEV-15811 Test failure on galera_pc_ignore_sb galera_kill_applier : race condition at the start of the test galera_ist_progress: MDEV-15236 galera_ist_progress fails when trying to read transfer status -GAL-480 : "Lost connection to MySQL" galera_concurrent_ctas : MDEV-15845 Test failure on galera.galera_concurrent_ctas -galera_sst_mysqldump : MDEV-14069 pxc-421: Lock timeout exceeded diff --git a/mysql-test/suite/gcol/r/innodb_virtual_index.result b/mysql-test/suite/gcol/r/innodb_virtual_index.result index df9985f88e3..48efd4edeb1 100644 --- a/mysql-test/suite/gcol/r/innodb_virtual_index.result +++ b/mysql-test/suite/gcol/r/innodb_virtual_index.result @@ -194,3 +194,16 @@ VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace; ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: INPLACE ADD or DROP of virtual columns cannot be combined with other ALTER TABLE actions. Try ALGORITHM=COPY DROP TABLE t1; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; +# +# Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7 +# +CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `col1` int(10) DEFAULT NULL, + `col2` char(21) GENERATED ALWAYS AS (`col1` * `col1`) VIRTUAL, + KEY `n` (`col2`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 +DROP TABLE t1; diff --git a/mysql-test/suite/gcol/t/innodb_virtual_index.test b/mysql-test/suite/gcol/t/innodb_virtual_index.test index 432faeb65ae..6604a6d94f4 100644 --- a/mysql-test/suite/gcol/t/innodb_virtual_index.test +++ b/mysql-test/suite/gcol/t/innodb_virtual_index.test @@ -224,3 +224,11 @@ VIRTUAL, ADD UNIQUE index idx (col1), algorithm=inplace; DROP TABLE t1; SET GLOBAL innodb_purge_rseg_truncate_frequency = @saved_frequency; + +--echo # +--echo # Bug 27122803 - BACKPORT FIX FOR BUG 25899959 TO MYSQL-5.7 +--echo # +CREATE TABLE t1 (col1 int(10)) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; +ALTER TABLE t1 ADD col2 char(21) AS (col1 * col1), ADD INDEX n (col2); +SHOW CREATE TABLE t1; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/r/alter_foreign_crash.result b/mysql-test/suite/innodb/r/alter_foreign_crash.result new file mode 100644 index 00000000000..66ffb5f5411 --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_foreign_crash.result @@ -0,0 +1,26 @@ +# +# Bug #20476395 DICT_LOAD_FOREIGNS() FAILED IN +# COMMIT_INPLACE_ALTER_TABLE +# +call mtr.add_suppression("InnoDB: Failed to load table"); +create database bug; +use bug; +create table parent(a serial) engine=innodb; +create table child(a serial, foreign key fk (a) references parent(a))engine=innodb; +insert into parent values(1); +insert into child values(1); +connect con1,localhost,root,,bug; +SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +ALTER TABLE child ROW_FORMAT=DYNAMIC, ALGORITHM=COPY; +connection default; +SET DEBUG_SYNC='now WAIT_FOR s1'; +SET DEBUG_SYNC='now SIGNAL s2 WAIT_FOR s1'; +disconnect con1; +show tables; +Tables_in_bug +parent +alter table parent row_format=dynamic; +Warnings: +Warning 1088 InnoDB: Could not add foreign key constraints. +drop table parent; +drop database bug; diff --git a/mysql-test/suite/innodb/r/alter_kill.result b/mysql-test/suite/innodb/r/alter_kill.result new file mode 100644 index 00000000000..9b24fddf9ef --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_kill.result @@ -0,0 +1,78 @@ +# +# Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP +# +SET GLOBAL innodb_file_per_table=1; +CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; +connect con1,localhost,root; +CREATE TABLE bug16720368 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO bug16720368 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); +connection default; +# Cleanly shutdown mysqld +disconnect con1; +# Corrupt FIL_PAGE_OFFSET in bug16720368.ibd, +# and update the checksum to the "don't care" value. +# Restart mysqld +# This will succeed after a clean shutdown, due to +# fil_open_single_table_tablespace(check_space_id=FALSE). +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +8 +INSERT INTO bug16720368_1 VALUES(1); +# The table is unaccessible, because after a crash we will +# validate the tablespace header. +SELECT COUNT(*) FROM bug16720368; +ERROR 42S02: Table 'test.bug16720368' doesn't exist in engine +INSERT INTO bug16720368 VALUES(0,1); +ERROR 42S02: Table 'test.bug16720368' doesn't exist in engine +# The table is readable thanks to innodb-force-recovery. +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +8 +INSERT INTO bug16720368 VALUES(0,1); +# Shut down the server cleanly to hide the corruption. +# The table is accessible, because after a clean shutdown we will +# NOT validate the tablespace header. +# We can modify the existing pages, but we cannot allocate or free +# any pages, because that would hit the corruption on page 0. +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +9 +# Shut down the server to uncorrupt the data. +# Restart the server after uncorrupting the file. +INSERT INTO bug16720368 VALUES(9,1); +SELECT COUNT(*) FROM bug16720368; +COUNT(*) +10 +DROP TABLE bug16720368, bug16720368_1; +# +# Bug#16735660 ASSERT TABLE2 == NULL, ROLLBACK OF RESURRECTED TXNS, +# DICT_TABLE_ADD_TO_CACHE +# +SET GLOBAL innodb_file_per_table=1; +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(42); +connect con1,localhost,root; +CREATE TABLE bug16735660 (a INT PRIMARY KEY) ENGINE=InnoDB; +XA START 'x'; +INSERT INTO bug16735660 VALUES(1),(2),(3); +XA END 'x'; +XA PREPARE 'x'; +connection default; +# Kill the server +disconnect con1; +# Attempt to start without an *.ibd file. +FOUND 1 /\[ERROR\] InnoDB: Tablespace [0-9]+ was not found at .*test.bug16735660.ibd/ in mysqld.1.err +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM bug16735660; +a +1 +2 +3 +XA RECOVER; +formatID gtrid_length bqual_length data +1 1 0 x +XA ROLLBACK 'x'; +SELECT * FROM bug16735660; +a +DROP TABLE bug16735660; diff --git a/mysql-test/suite/innodb/r/alter_rename_files.result b/mysql-test/suite/innodb/r/alter_rename_files.result new file mode 100644 index 00000000000..7df63a051da --- /dev/null +++ b/mysql-test/suite/innodb/r/alter_rename_files.result @@ -0,0 +1,20 @@ +CREATE TABLE t1 (x INT NOT NULL UNIQUE KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(5); +SET GLOBAL innodb_log_checkpoint_now=TRUE; +SET DEBUG_SYNC='commit_cache_rebuild SIGNAL ready WAIT_FOR finish'; +ALTER TABLE t1 ADD PRIMARY KEY(x); +connect con1,localhost,root,,; +SET DEBUG_SYNC='now WAIT_FOR ready'; +SET GLOBAL innodb_log_checkpoint_now=TRUE; +SET DEBUG_SYNC='now SIGNAL finish'; +disconnect con1; +connection default; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `x` int(11) NOT NULL, + PRIMARY KEY (`x`), + UNIQUE KEY `x` (`x`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +DROP TABLE t1; +SET DEBUG_SYNC='RESET'; diff --git a/mysql-test/suite/innodb/r/analyze_table.result b/mysql-test/suite/innodb/r/analyze_table.result new file mode 100644 index 00000000000..a5c25289ad1 --- /dev/null +++ b/mysql-test/suite/innodb/r/analyze_table.result @@ -0,0 +1,25 @@ +CREATE PROCEDURE populate_t1() +BEGIN +DECLARE i int DEFAULT 1; +START TRANSACTION; +WHILE (i <= 1000000) DO +INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); +SET i = i + 1; +END WHILE; +COMMIT; +END| +CREATE TABLE t1( +class INT, +id INT, +title VARCHAR(100) +) ENGINE=InnoDB; +SELECT COUNT(*) FROM t1; +COUNT(*) +1000000 +SET GLOBAL innodb_stats_persistent_sample_pages=2000; +ANALYZE TABLE t1; +Table Op Msg_type Msg_text +test.t1 analyze status OK +DROP TABLE t1; +DROP PROCEDURE populate_t1; +SET GLOBAL innodb_stats_persistent_sample_pages=default; diff --git a/mysql-test/suite/innodb/r/innodb-online-alter-gis.result b/mysql-test/suite/innodb/r/innodb-online-alter-gis.result index faf6ab7fa4a..34cc62f0a55 100644 --- a/mysql-test/suite/innodb/r/innodb-online-alter-gis.result +++ b/mysql-test/suite/innodb/r/innodb-online-alter-gis.result @@ -47,3 +47,21 @@ DESCRIBE t1; Field Type Null Key Default Extra a int(11) YES NULL DROP TABLE t1; +# +# Bug #19077964 ASSERT PAGE_SIZE.EQUALS_TO SPACE_PAGE_SIZE +# BTR_COPY_BLOB_PREFIX +# +CREATE TABLE t1(f1 INT PRIMARY KEY, f3 LINESTRING NOT NULL, +SPATIAL KEY(f3))ENGINE=InnoDB ROW_FORMAT=COMPRESSED +KEY_BLOCK_SIZE=1; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `f1` int(11) NOT NULL, + `f3` linestring NOT NULL, + PRIMARY KEY (`f1`), + SPATIAL KEY `f3` (`f3`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=1 +INSERT INTO t1 VALUES (1, ST_linefromtext(concat('linestring', '( 0 0, 1 1, 2 2, 3 3, 4 4, 5 5, 6 6, 7 7, 8 8, 9 9, 10 10, 11 11, 12 12, 13 13, 14 14, 15 15, 16 16, 17 17, 18 18, 19 19, 20 20, 21 21, 22 22, 23 23, 24 24, 25 25, 26 26, 27 27, 28 28, 29 29, 30 30, 31 31, 32 32, 33 33, 34 34, 35 35, 36 36, 37 37, 38 38, 39 39, 40 40, 41 41, 42 42, 43 43, 44 44, 45 45, 46 46, 47 47, 48 48, 49 49, 50 50, 51 51, 52 52, 53 53, 54 54, 55 55, 56 56, 57 57, 58 58, 59 59, 60 60, 61 61, 62 62, 63 63, 64 64, 65 65, 66 66, 67 67, 68 68, 69 69, 70 70, 71 71, 72 72, 73 73, 74 74, 75 75, 76 76, 77 77, 78 78, 79 79, 9999 9999)')));; +ALTER TABLE t1 ROW_FORMAT = DYNAMIC, KEY_BLOCK_SIZE=0, ALGORITHM=INPLACE; +DROP TABLE t1; diff --git a/mysql-test/suite/innodb/t/alter_foreign_crash.test b/mysql-test/suite/innodb/t/alter_foreign_crash.test new file mode 100644 index 00000000000..1952a1b30d4 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_foreign_crash.test @@ -0,0 +1,37 @@ +--source include/have_innodb.inc +--source include/have_debug_sync.inc +# The embedded server does not support restarting. +--source include/not_embedded.inc + +--echo # +--echo # Bug #20476395 DICT_LOAD_FOREIGNS() FAILED IN +--echo # COMMIT_INPLACE_ALTER_TABLE +--echo # + +call mtr.add_suppression("InnoDB: Failed to load table"); + +create database bug; +use bug; + +create table parent(a serial) engine=innodb; +create table child(a serial, foreign key fk (a) references parent(a))engine=innodb; + +insert into parent values(1); +insert into child values(1); + +connect (con1,localhost,root,,bug); +SET DEBUG_SYNC='innodb_rename_table_ready SIGNAL s1 WAIT_FOR s2 EXECUTE 2'; +--send ALTER TABLE child ROW_FORMAT=DYNAMIC, ALGORITHM=COPY +connection default; +SET DEBUG_SYNC='now WAIT_FOR s1'; +SET DEBUG_SYNC='now SIGNAL s2 WAIT_FOR s1'; + +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc +disconnect con1; + +show tables; +alter table parent row_format=dynamic; + +drop table parent; +drop database bug; diff --git a/mysql-test/suite/innodb/t/alter_kill-master.opt b/mysql-test/suite/innodb/t/alter_kill-master.opt new file mode 100644 index 00000000000..e472160c2b7 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_kill-master.opt @@ -0,0 +1 @@ +--innodb-doublewrite=false diff --git a/mysql-test/suite/innodb/t/alter_kill.test b/mysql-test/suite/innodb/t/alter_kill.test new file mode 100644 index 00000000000..922378d2919 --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_kill.test @@ -0,0 +1,158 @@ +--source include/have_innodb.inc +# The embedded server does not support restarting in mysql-test-run. +-- source include/not_embedded.inc +-- source include/no_valgrind_without_big.inc + +let MYSQLD_DATADIR=`select @@datadir`; +let PAGE_SIZE=`select @@innodb_page_size`; + +-- disable_query_log +call mtr.add_suppression("InnoDB: innodb_force_recovery is on."); +call mtr.add_suppression("InnoDB: Header page contains inconsistent data in .*bug16720368.ibd"); +call mtr.add_suppression("InnoDB: Checksum mismatch in datafile:.*bug16720368"); +call mtr.add_suppression("InnoDB: Ignoring tablespace for.*bug16720368"); +call mtr.add_suppression("Found 1 prepared XA transactions"); +call mtr.add_suppression("InnoDB: Operating system error.*in a file operation"); +call mtr.add_suppression("InnoDB: \(The error means\|If you are\)"); +call mtr.add_suppression("InnoDB: Ignoring tablespace `test/bug16720368` because it could not be opened"); +call mtr.add_suppression("InnoDB: Tablespace .* was not found at.*bug16735660"); +call mtr.add_suppression("InnoDB: Set innodb_force_recovery=1 to ignore this and to permanently lose all changes to the tablespace."); +call mtr.add_suppression("InnoDB: Plugin initialization aborted*"); +call mtr.add_suppression("Plugin 'InnoDB' init function returned error."); +call mtr.add_suppression("Plugin 'InnoDB' registration as a STORAGE ENGINE failed."); +-- enable_query_log + +-- echo # +-- echo # Bug#16720368 INNODB CRASHES ON BROKEN #SQL*.IBD FILE AT STARTUP +-- echo # + +SET GLOBAL innodb_file_per_table=1; + +CREATE TABLE bug16720368_1 (a INT PRIMARY KEY) ENGINE=InnoDB; + +connect (con1,localhost,root); +CREATE TABLE bug16720368 (a INT PRIMARY KEY, b INT) ENGINE=InnoDB; +INSERT INTO bug16720368 (a) VALUES (1),(2),(3),(4),(5),(6),(7),(8); + +connection default; + +-- echo # Cleanly shutdown mysqld +-- source include/shutdown_mysqld.inc + +disconnect con1; + +-- echo # Corrupt FIL_PAGE_OFFSET in bug16720368.ibd, +-- echo # and update the checksum to the "don't care" value. +perl; +my $file = "$ENV{MYSQLD_DATADIR}/test/bug16720368.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +print FILE pack("H*","deadbeefc001cafe") || die "Unable to write $file"; +seek(FILE, $ENV{PAGE_SIZE}-8, 0) || die "Unable to seek $file"; +print FILE pack("H*","deadbeef") || die "Unable to write $file"; +close(FILE) || die "Unable to close $file"; +EOF + +-- echo # Restart mysqld +-- source include/start_mysqld.inc + +-- echo # This will succeed after a clean shutdown, due to +-- echo # fil_open_single_table_tablespace(check_space_id=FALSE). +SELECT COUNT(*) FROM bug16720368; + +INSERT INTO bug16720368_1 VALUES(1); + +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc + +-- echo # The table is unaccessible, because after a crash we will +-- echo # validate the tablespace header. +--error ER_NO_SUCH_TABLE_IN_ENGINE +SELECT COUNT(*) FROM bug16720368; +--error ER_NO_SUCH_TABLE_IN_ENGINE +INSERT INTO bug16720368 VALUES(0,1); + +let $restart_parameters = --innodb-force-recovery=3; +--let $shutdown_timeout= 0 +--source include/restart_mysqld.inc + +-- echo # The table is readable thanks to innodb-force-recovery. +SELECT COUNT(*) FROM bug16720368; +INSERT INTO bug16720368 VALUES(0,1); + +-- echo # Shut down the server cleanly to hide the corruption. +let $shutdown_timeout=; +let $restart_parameters =; +-- source include/restart_mysqld.inc + +-- echo # The table is accessible, because after a clean shutdown we will +-- echo # NOT validate the tablespace header. +-- echo # We can modify the existing pages, but we cannot allocate or free +-- echo # any pages, because that would hit the corruption on page 0. +SELECT COUNT(*) FROM bug16720368; + +-- echo # Shut down the server to uncorrupt the data. +-- source include/shutdown_mysqld.inc + +# Uncorrupt the FIL_PAGE_OFFSET. +perl; +my $file = "$ENV{MYSQLD_DATADIR}/test/bug16720368.ibd"; +open(FILE, "+<$file") || die "Unable to open $file"; +# Uncorrupt FIL_PAGE_OFFSET. +print FILE pack("H*","deadbeef00000000") || die "Unable to write $file"; +close(FILE) || die "Unable to close $file"; +EOF + +-- echo # Restart the server after uncorrupting the file. +-- source include/start_mysqld.inc + +INSERT INTO bug16720368 VALUES(9,1); +SELECT COUNT(*) FROM bug16720368; +# A debug assertion would fail in buf_block_align_instance() +# if we did not uncorrupt the page number first. +DROP TABLE bug16720368, bug16720368_1; + +-- echo # +-- echo # Bug#16735660 ASSERT TABLE2 == NULL, ROLLBACK OF RESURRECTED TXNS, +-- echo # DICT_TABLE_ADD_TO_CACHE +-- echo # + +SET GLOBAL innodb_file_per_table=1; + +CREATE TEMPORARY TABLE t1 (a INT PRIMARY KEY) ENGINE=InnoDB; +BEGIN; +INSERT INTO t1 VALUES(42); + +-- connect (con1,localhost,root) + +CREATE TABLE bug16735660 (a INT PRIMARY KEY) ENGINE=InnoDB; + +XA START 'x'; +INSERT INTO bug16735660 VALUES(1),(2),(3); +XA END 'x'; +XA PREPARE 'x'; + +-- connection default + +-- source include/kill_mysqld.inc +-- disconnect con1 +-- move_file $MYSQLD_DATADIR/test/bug16735660.ibd $MYSQLD_DATADIR/bug16735660.omg + +-- echo # Attempt to start without an *.ibd file. +let SEARCH_FILE= $MYSQLTEST_VARDIR/log/mysqld.1.err; +--source include/start_mysqld.inc + +let SEARCH_PATTERN= \[ERROR\] InnoDB: Tablespace [0-9]+ was not found at .*test.bug16735660.ibd; +-- source include/search_pattern_in_file.inc + +-- move_file $MYSQLD_DATADIR/bug16735660.omg $MYSQLD_DATADIR/test/bug16735660.ibd + +-- source include/restart_mysqld.inc + +SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; +SELECT * FROM bug16735660; + +XA RECOVER; +XA ROLLBACK 'x'; + +SELECT * FROM bug16735660; +DROP TABLE bug16735660; diff --git a/mysql-test/suite/innodb/t/alter_rename_files.test b/mysql-test/suite/innodb/t/alter_rename_files.test new file mode 100644 index 00000000000..3ed1cb5d9fa --- /dev/null +++ b/mysql-test/suite/innodb/t/alter_rename_files.test @@ -0,0 +1,31 @@ +--source include/have_debug.inc +--source include/have_debug_sync.inc +--source include/have_innodb.inc +--source include/count_sessions.inc + +CREATE TABLE t1 (x INT NOT NULL UNIQUE KEY) ENGINE=InnoDB; +INSERT INTO t1 VALUES(5); + +SET GLOBAL innodb_log_checkpoint_now=TRUE; + +# Start an ALTER TABLE and stop it before renaming the files +SET DEBUG_SYNC='commit_cache_rebuild SIGNAL ready WAIT_FOR finish'; + +--send ALTER TABLE t1 ADD PRIMARY KEY(x) + +connect (con1,localhost,root,,); + +SET DEBUG_SYNC='now WAIT_FOR ready'; + +SET GLOBAL innodb_log_checkpoint_now=TRUE; + +SET DEBUG_SYNC='now SIGNAL finish'; + +disconnect con1; +connection default; +reap; +SHOW CREATE TABLE t1; +DROP TABLE t1; +SET DEBUG_SYNC='RESET'; + +--source include/wait_until_count_sessions.inc diff --git a/mysql-test/suite/innodb/t/analyze_table.test b/mysql-test/suite/innodb/t/analyze_table.test new file mode 100644 index 00000000000..e9db3668f02 --- /dev/null +++ b/mysql-test/suite/innodb/t/analyze_table.test @@ -0,0 +1,42 @@ +# +# BUG#22385442 - INNODB: DIFFICULT TO FIND FREE BLOCKS IN THE BUFFER POOL +# + +--source include/have_innodb.inc +--source include/big_test.inc + +DELIMITER |; +CREATE PROCEDURE populate_t1() +BEGIN + DECLARE i int DEFAULT 1; + + START TRANSACTION; + WHILE (i <= 1000000) DO + INSERT INTO t1 VALUES (i, i, CONCAT('a', i)); + SET i = i + 1; + END WHILE; + COMMIT; +END| +DELIMITER ;| + +CREATE TABLE t1( + class INT, + id INT, + title VARCHAR(100) +) ENGINE=InnoDB; + +-- disable_query_log +CALL populate_t1(); +-- enable_query_log + +SELECT COUNT(*) FROM t1; + +SET GLOBAL innodb_stats_persistent_sample_pages=2000; + +ANALYZE TABLE t1; + +DROP TABLE t1; + +DROP PROCEDURE populate_t1; + +SET GLOBAL innodb_stats_persistent_sample_pages=default; diff --git a/mysql-test/suite/innodb/t/innodb-online-alter-gis.test b/mysql-test/suite/innodb/t/innodb-online-alter-gis.test index 570e22d5dd1..1c99c6eeb9d 100644 --- a/mysql-test/suite/innodb/t/innodb-online-alter-gis.test +++ b/mysql-test/suite/innodb/t/innodb-online-alter-gis.test @@ -29,3 +29,34 @@ CREATE TABLE t1 (a INT) ENGINE=InnoDB; ALTER TABLE t1 ADD COLUMN b LINESTRING DEFAULT POINT(1,1); DESCRIBE t1; DROP TABLE t1; + +--echo # +--echo # Bug #19077964 ASSERT PAGE_SIZE.EQUALS_TO SPACE_PAGE_SIZE +--echo # BTR_COPY_BLOB_PREFIX +--echo # + +CREATE TABLE t1(f1 INT PRIMARY KEY, f3 LINESTRING NOT NULL, + SPATIAL KEY(f3))ENGINE=InnoDB ROW_FORMAT=COMPRESSED + KEY_BLOCK_SIZE=1; +SHOW CREATE TABLE t1; + +let $points = 80; +let $x = 0; +let $y = 0; +let $linestr = (; + +while ($points) +{ + let $linestr = $linestr $x $y,; + dec $points; + inc $x; + inc $y; +} + +let $linestr = $linestr 9999 9999); + +--eval INSERT INTO t1 VALUES (1, ST_linefromtext(concat('linestring', '$linestr'))); + +ALTER TABLE t1 ROW_FORMAT = DYNAMIC, KEY_BLOCK_SIZE=0, ALGORITHM=INPLACE; + +DROP TABLE t1; diff --git a/mysql-test/suite/parts/r/quoting.result b/mysql-test/suite/parts/r/show_create.result index 66606832e77..79ac61d180d 100644 --- a/mysql-test/suite/parts/r/quoting.result +++ b/mysql-test/suite/parts/r/show_create.result @@ -91,3 +91,15 @@ t2 CREATE TABLE "t2" ( PARTITION BY RANGE ("f1") (PARTITION "p1" VALUES LESS THAN MAXVALUE ENGINE = MyISAM) drop table t1, t2; +set sql_mode=default; +create table t_partition (f1 int) partition by hash(f1) partitions 2; +select * from t_partition as tbl; +f1 +show create table t_partition; +Table Create Table +t_partition CREATE TABLE `t_partition` ( + `f1` int(11) DEFAULT NULL +) ENGINE=MyISAM DEFAULT CHARSET=latin1 + PARTITION BY HASH (`f1`) +PARTITIONS 2 +drop table t_partition; diff --git a/mysql-test/suite/parts/t/quoting.test b/mysql-test/suite/parts/t/show_create.test index 61af8d2d345..9c43b163790 100644 --- a/mysql-test/suite/parts/t/quoting.test +++ b/mysql-test/suite/parts/t/show_create.test @@ -30,3 +30,12 @@ set sql_mode=ansi_quotes; show create table t1; show create table t2; drop table t1, t2; +set sql_mode=default; + +# +# MDEV-14750 Valgrind Invalid read, ASAN heap-use-after-free in Item_ident::print upon SHOW CREATE on partitioned table +# +create table t_partition (f1 int) partition by hash(f1) partitions 2; +select * from t_partition as tbl; +show create table t_partition; +drop table t_partition; diff --git a/mysql-test/suite/versioning/r/truncate.result b/mysql-test/suite/versioning/r/truncate.result index 181b120eafb..308501915dc 100644 --- a/mysql-test/suite/versioning/r/truncate.result +++ b/mysql-test/suite/versioning/r/truncate.result @@ -1,7 +1,12 @@ create table t (a int); delete history from t before system_time now(); ERROR HY000: Table `t` is not system-versioned -create or replace table t (a int) with system versioning; +create or replace table t ( +a int, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for system_time (row_start, row_end)) +with system versioning; insert into t values (1); update t set a=2; set @test = 'correct'; @@ -12,7 +17,12 @@ select @test from t; @test correct drop table t; -create table t (a int) with system versioning; +create or replace table t ( +a int, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for system_time (row_start, row_end)) +with system versioning; insert into t values (1), (2); update t set a=11 where a=1; set @ts1=now(6); @@ -48,7 +58,6 @@ drop procedure truncate_sp; # Truncate partitioned create or replace table t (a int) with system versioning -engine myisam partition by system_time limit 1 ( partition p0 history, partition p1 history, @@ -61,7 +70,12 @@ select * from t for system_time all; a 3 # VIEW -create or replace table t (i int) with system versioning; +create or replace table t ( +i int, +row_start SYS_TYPE as row start invisible, +row_end SYS_TYPE as row end invisible, +period for system_time (row_start, row_end)) +with system versioning; delete history from t; create or replace view v as select * from t; delete history from v; @@ -86,3 +100,5 @@ ERROR 42S02: 'v' is a view unlock tables; drop view v; drop table t; +drop database test; +create database test; diff --git a/mysql-test/suite/versioning/t/truncate.test b/mysql-test/suite/versioning/t/truncate.test index f52d52d81ea..4e039a76567 100644 --- a/mysql-test/suite/versioning/t/truncate.test +++ b/mysql-test/suite/versioning/t/truncate.test @@ -1,3 +1,4 @@ +--source suite/versioning/common.inc --source include/have_partition.inc --source suite/versioning/engines.inc @@ -6,7 +7,13 @@ create table t (a int); delete history from t before system_time now(); # TRUNCATE is not DELETE and trigger must not be called. -create or replace table t (a int) with system versioning; +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + a int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; insert into t values (1); update t set a=2; set @test = 'correct'; @@ -16,7 +23,13 @@ delete history from t; select @test from t; drop table t; -create table t (a int) with system versioning; +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + a int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; insert into t values (1), (2); update t set a=11 where a=1; --real_sleep 0.01 @@ -45,7 +58,6 @@ drop procedure truncate_sp; --echo # Truncate partitioned create or replace table t (a int) with system versioning -engine myisam partition by system_time limit 1 ( partition p0 history, partition p1 history, @@ -57,7 +69,13 @@ delete history from t; select * from t for system_time all; --echo # VIEW -create or replace table t (i int) with system versioning; +--replace_result $sys_datatype_expl SYS_TYPE +eval create or replace table t ( + i int, + row_start $sys_datatype_expl as row start invisible, + row_end $sys_datatype_expl as row end invisible, + period for system_time (row_start, row_end)) +with system versioning; delete history from t; create or replace view v as select * from t; --error ER_IT_IS_A_VIEW @@ -88,3 +106,6 @@ delete history from v before system_time now(6); unlock tables; drop view v; drop table t; + +drop database test; +create database test; diff --git a/mysql-test/t/alter_table_errors.test b/mysql-test/t/alter_table_errors.test new file mode 100644 index 00000000000..d9982ac26f4 --- /dev/null +++ b/mysql-test/t/alter_table_errors.test @@ -0,0 +1,10 @@ +--source include/have_innodb.inc + +# +# MDEV-16110 ALTER with ALGORITHM=INPLACE breaks temporary table with virtual columns +# +create table t (a int, v int as (a)) engine=innodb; +--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON +alter table t change column a b tinyint, algorithm=inplace; +show create table t; +drop table t; diff --git a/mysql-test/t/subselect-crash_15755.test b/mysql-test/t/subselect-crash_15755.test new file mode 100644 index 00000000000..79e259d6337 --- /dev/null +++ b/mysql-test/t/subselect-crash_15755.test @@ -0,0 +1,60 @@ +--source include/have_innodb.inc +set global innodb_stats_persistent= 1; +drop table if exists t1; +create table t1 ( + f1 bigint(20) default 0, + f2 varchar(50) default '', + f3 int(10) default 0, + f4 bigint(20) default 0, + f5 bigint(20) default 0, + f6 varchar(50) default '', + f7 varchar(64) default '', + f8 varchar(30) default '', + f9 varchar(30) default '', + f10 bigint(20) default 0, + f11 bigint(20) default 0, + f12 bigint(20) default 0, + f13 bigint(20) default 0, + f14 varchar(50) default '', + f15 varchar(100) default '', + f16 varchar(30) default '', + f17 varchar(40) default '', + f18 varchar(30) default '', + f19 varchar(10) default '', + f20 varchar(30) default '', + f21 int(10) default 0, + f22 int(10) default 0, + f23 int(10) default 0, + f24 int(10) default 0, + f25 varchar(20) default '', + f26 varchar(20) default '', + f27 varchar(100) default '', + f28 varchar(55) default '', + f29 varchar(20) default '', + f30 varchar(100) default '', + f31 varchar(30) default '', + f32 varchar(20) default '', + f33 int(10) default 0, + f34 int(10) default 0, + f35 varchar(30) default '', + f36 varchar(30) default '', + f37 varchar(30) default '', + f38 varchar(20) default '', + f39 tinyint(4) default 0, + f40 tinyint(4) default 0, + f41 bigint(20) default 0, + f42 varchar(50) default '', + f43 varchar(50) default '', + f44 varchar(50) default '', + f45 int(10) default 0, + f46 tinyint(1) default 0 +) engine=innodb row_format=dynamic; + +insert into t1 () values (),(),(),(),(),(),(),(),(),(),(),(),(),(),(),(); +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +insert into t1 select * from t1; +select * from t1 where f2 in (select f2 from t1 group by f2 having count(distinct f3) = 1); +drop table t1; +set global innodb_stats_persistent= 0; |