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 | |
| parent | c2352c45fbd670f50b73415eeeb676aa67fb4a29 (diff) | |
| parent | a4e7800701d0764fe4cbb85b81d7c7cb54677334 (diff) | |
| download | mariadb-git-4c7608aeb187383f2629e96e085b5b50fc81337f.tar.gz | |
Merge 10.2 into 10.3
92 files changed, 2958 insertions, 374 deletions
diff --git a/libmariadb b/libmariadb -Subproject 668757aaa9a55d2bcd806908cb5a8e806cd6dc3 +Subproject 27b2f3d1f1550dfaee0f63a331a406ab31c1b37 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; diff --git a/sql/item.cc b/sql/item.cc index bfd5ed78bf8..6b8a79f9996 100644 --- a/sql/item.cc +++ b/sql/item.cc @@ -6357,19 +6357,15 @@ error: return TRUE; } - -/* - @brief - Mark virtual columns as used in a partitioning expression -*/ - -bool Item_field::vcol_in_partition_func_processor(void *int_arg) +bool Item_field::post_fix_fields_part_expr_processor(void *int_arg) { DBUG_ASSERT(fixed); if (field->vcol_info) - { field->vcol_info->mark_as_in_partitioning_expr(); - } + /* + Update table_name to be real table name, not the alias. Because alias is + reallocated for every statement, and this item has a long life time */ + table_name= field->table->s->table_name.str; return FALSE; } @@ -6797,6 +6793,11 @@ fast_field_copier Item_field::setup_fast_field_copier(Field *to) return to->get_fast_field_copier(field); } +void Item_field::save_in_result_field(bool no_conversions) +{ + bool unused; + save_field_in_field(field, &unused, result_field, no_conversions); +} /** Set a field's value from a item. diff --git a/sql/item.h b/sql/item.h index bd5c85e6140..c1e306d95b6 100644 --- a/sql/item.h +++ b/sql/item.h @@ -1710,7 +1710,7 @@ public: fields. */ virtual bool check_partition_func_processor(void *arg) { return 1;} - virtual bool vcol_in_partition_func_processor(void *arg) { return 0; } + virtual bool post_fix_fields_part_expr_processor(void *arg) { return 0; } virtual bool rename_fields_processor(void *arg) { return 0; } /** Processor used to check acceptability of an item in the defining expression for a virtual column @@ -2997,8 +2997,7 @@ public: cond_equal_ref); } bool is_result_field() { return false; } - void set_result_field(Field *field_arg) {} - void save_in_result_field(bool no_conversions) { } + void save_in_result_field(bool no_conversions); Item *get_tmp_table_item(THD *thd); bool collect_item_field_processor(void * arg); bool add_field_to_set_processor(void * arg); @@ -3007,7 +3006,7 @@ public: bool register_field_in_write_map(void *arg); bool register_field_in_bitmap(void *arg); bool check_partition_func_processor(void *int_arg) {return FALSE;} - bool vcol_in_partition_func_processor(void *bool_arg); + bool post_fix_fields_part_expr_processor(void *bool_arg); bool check_valid_arguments_processor(void *bool_arg); bool check_field_expression_processor(void *arg); bool enumerate_field_refs_processor(void *arg); diff --git a/sql/item_func.h b/sql/item_func.h index 376f8fc16a1..d6bb0f18700 100644 --- a/sql/item_func.h +++ b/sql/item_func.h @@ -70,7 +70,7 @@ public: SP_CONTAINS_FUNC,SP_OVERLAPS_FUNC, SP_STARTPOINT,SP_ENDPOINT,SP_EXTERIORRING, SP_POINTN,SP_GEOMETRYN,SP_INTERIORRINGN, SP_RELATE_FUNC, - NOT_FUNC, NOT_ALL_FUNC, + NOT_FUNC, NOT_ALL_FUNC, TEMPTABLE_ROWID, NOW_FUNC, NOW_UTC_FUNC, SYSDATE_FUNC, TRIG_COND_FUNC, SUSERVAR_FUNC, GUSERVAR_FUNC, COLLATE_FUNC, EXTRACT_FUNC, CHAR_TYPECAST_FUNC, FUNC_SP, UDF_FUNC, diff --git a/sql/item_strfunc.cc b/sql/item_strfunc.cc index 8df4e72462d..d5911e01cef 100644 --- a/sql/item_strfunc.cc +++ b/sql/item_strfunc.cc @@ -5204,3 +5204,23 @@ null: my_free(names); return NULL; } + +Item_temptable_rowid::Item_temptable_rowid(TABLE *table_arg) + : Item_str_func(table_arg->in_use), table(table_arg) +{ + max_length= table->file->ref_length; +} + +void Item_temptable_rowid::fix_length_and_dec() +{ + used_tables_cache= table->map; + const_item_cache= false; +} + +String *Item_temptable_rowid::val_str(String *str) +{ + if (!((null_value= table->null_row))) + table->file->position(table->record[0]); + str_value.set((char*)(table->file->ref), max_length, &my_charset_bin); + return &str_value; +} diff --git a/sql/item_strfunc.h b/sql/item_strfunc.h index b3462a60d36..eb084c3f58d 100644 --- a/sql/item_strfunc.h +++ b/sql/item_strfunc.h @@ -1751,5 +1751,25 @@ public: { return get_item_copy<Item_func_dyncol_list>(thd, this); } }; -#endif /* ITEM_STRFUNC_INCLUDED */ +/* + this is used by JOIN_TAB::keep_current_rowid + and stores handler::position(). + It has nothing to do with _rowid pseudo-column, that the parser supports. +*/ +class Item_temptable_rowid :public Item_str_func +{ +public: + TABLE *table; + Item_temptable_rowid(TABLE *table_arg); + const Type_handler *type_handler() const { return &type_handler_string; } + Field *create_tmp_field(bool group, TABLE *table) + { return create_table_field_from_handler(table); } + String *val_str(String *str); + enum Functype functype() const { return TEMPTABLE_ROWID; } + const char *func_name() const { return "<rowid>"; } + void fix_length_and_dec(); + Item *get_copy(THD *thd) + { return get_item_copy<Item_temptable_rowid>(thd, this); } +}; +#endif /* ITEM_STRFUNC_INCLUDED */ diff --git a/sql/item_subselect.cc b/sql/item_subselect.cc index 99be2024c2f..143894ad949 100644 --- a/sql/item_subselect.cc +++ b/sql/item_subselect.cc @@ -156,6 +156,7 @@ void Item_subselect::cleanup() reset(); filesort_buffer.free_sort_buffer(); my_free(sortbuffer.str); + sortbuffer.str= 0; value_assigned= 0; expr_cache= 0; diff --git a/sql/item_vers.cc b/sql/item_vers.cc index 8a83ffddcf4..d7361f687f9 100644 --- a/sql/item_vers.cc +++ b/sql/item_vers.cc @@ -123,7 +123,7 @@ Item_func_trt_id::get_by_commit_ts(MYSQL_TIME &commit_ts, bool backwards) TR_table trt(thd); null_value= !trt.query(commit_ts, backwards); if (null_value) - return 0; + return backwards ? ULONGLONG_MAX : 0; return trt[trt_field]->val_int(); } diff --git a/sql/opt_subselect.cc b/sql/opt_subselect.cc index 1d2fd5548a5..a04a67c9a3f 100644 --- a/sql/opt_subselect.cc +++ b/sql/opt_subselect.cc @@ -1063,8 +1063,6 @@ bool convert_join_subqueries_to_semijoins(JOIN *join) while ((in_subq= li++)) { SELECT_LEX *subq_sel= in_subq->get_select_lex(); - if (subq_sel->handle_derived(thd->lex, DT_OPTIMIZE)) - DBUG_RETURN(1); if (subq_sel->handle_derived(thd->lex, DT_MERGE)) DBUG_RETURN(TRUE); if (subq_sel->join->transform_in_predicates_into_in_subq(thd)) diff --git a/sql/sql_base.cc b/sql/sql_base.cc index 6c400bf6b25..4fc667dcdc2 100644 --- a/sql/sql_base.cc +++ b/sql/sql_base.cc @@ -7178,7 +7178,7 @@ static bool setup_natural_join_row_types(THD *thd, int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, List<Item> *sum_func_list, - uint wild_num) + uint wild_num, uint *hidden_bit_fields) { if (!wild_num) return(0); @@ -7218,7 +7218,7 @@ int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, else if (insert_fields(thd, ((Item_field*) item)->context, ((Item_field*) item)->db_name, ((Item_field*) item)->table_name, &it, - any_privileges)) + any_privileges, hidden_bit_fields)) { if (arena) thd->restore_active_arena(arena, &backup); @@ -7683,7 +7683,7 @@ bool get_key_map_from_key_list(key_map *map, TABLE *table, bool insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, const char *table_name, List_iterator<Item> *it, - bool any_privileges) + bool any_privileges, uint *hidden_bit_fields) { Field_iterator_table_ref field_iterator; bool found; @@ -7811,6 +7811,9 @@ insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, else it->after(item); /* Add 'item' to the SELECT list. */ + if (item->type() == Item::FIELD_ITEM && item->field_type() == MYSQL_TYPE_BIT) + (*hidden_bit_fields)++; + #ifndef NO_EMBEDDED_ACCESS_CHECKS /* Set privilege information for the fields of newly created views. diff --git a/sql/sql_base.h b/sql/sql_base.h index 645a4a83fcb..b85e01032fa 100644 --- a/sql/sql_base.h +++ b/sql/sql_base.h @@ -155,11 +155,12 @@ bool fill_record_n_invoke_before_triggers(THD *thd, TABLE *table, enum trg_event_type event); bool insert_fields(THD *thd, Name_resolution_context *context, const char *db_name, const char *table_name, - List_iterator<Item> *it, bool any_privileges); + List_iterator<Item> *it, bool any_privileges, + uint *hidden_bit_fields); void make_leaves_list(THD *thd, List<TABLE_LIST> &list, TABLE_LIST *tables, bool full_table_list, TABLE_LIST *boundary); int setup_wild(THD *thd, TABLE_LIST *tables, List<Item> &fields, - List<Item> *sum_func_list, uint wild_num); + List<Item> *sum_func_list, uint wild_num, uint * hidden_bit_fields); bool setup_fields(THD *thd, Ref_ptr_array ref_pointer_array, List<Item> &item, enum_column_usage column_usage, List<Item> *sum_func_list, List<Item> *pre_fix, diff --git a/sql/sql_class.h b/sql/sql_class.h index e453eb9ce60..0ff588a6d38 100644 --- a/sql/sql_class.h +++ b/sql/sql_class.h @@ -4981,7 +4981,7 @@ public: unit= u; return 0; } - virtual int prepare2(void) { return 0; } + virtual int prepare2(JOIN *join) { return 0; } /* Because of peculiarities of prepared statements protocol we need to know number of columns in the result set (if @@ -4990,7 +4990,7 @@ public: virtual uint field_count(List<Item> &fields) const { return fields.elements; } virtual bool send_result_set_metadata(List<Item> &list, uint flags)=0; - virtual bool initialize_tables (JOIN *join=0) { return 0; } + virtual bool initialize_tables (JOIN *join) { return 0; } virtual bool send_eof()=0; /** Check if this query returns a result set and therefore is allowed in @@ -5225,7 +5225,7 @@ class select_insert :public select_result_interceptor { enum_duplicates duplic, bool ignore); ~select_insert(); int prepare(List<Item> &list, SELECT_LEX_UNIT *u); - virtual int prepare2(void); + virtual int prepare2(JOIN *join); virtual int send_data(List<Item> &items); virtual void store_values(List<Item> &values); virtual bool can_rollback_data() { return 0; } @@ -5277,7 +5277,7 @@ public: // Needed for access from local class MY_HOOKS in prepare(), since thd is proteted. const THD *get_thd(void) { return thd; } const HA_CREATE_INFO *get_create_info() { return create_info; }; - int prepare2(void) { return 0; } + int prepare2(JOIN *join) { return 0; } private: TABLE *create_table_from_items(THD *thd, @@ -5542,7 +5542,7 @@ public: bool postponed_prepare(List<Item> &types); bool send_result_set_metadata(List<Item> &list, uint flags); int send_data(List<Item> &items); - bool initialize_tables (JOIN *join= NULL); + bool initialize_tables (JOIN *join); bool send_eof(); bool flush() { return false; } bool check_simple_select() const @@ -5961,6 +5961,7 @@ public: int prepare(List<Item> &list, SELECT_LEX_UNIT *u); int send_data(List<Item> &items); bool initialize_tables (JOIN *join); + int prepare2(JOIN *join); int do_updates(); bool send_eof(); inline ha_rows num_found() const { return found; } diff --git a/sql/sql_delete.cc b/sql/sql_delete.cc index d4a2f779544..2781bd50d26 100644 --- a/sql/sql_delete.cc +++ b/sql/sql_delete.cc @@ -324,13 +324,6 @@ bool mysql_delete(THD *thd, TABLE_LIST *table_list, COND *conds, DBUG_ASSERT(!conds); conds= table_list->on_expr; table_list->on_expr= NULL; - - // trx_sees() in InnoDB reads row_start - if (!table->versioned(VERS_TIMESTAMP)) - { - DBUG_ASSERT(table_list->vers_conditions.type == SYSTEM_TIME_BEFORE); - bitmap_set_bit(table->read_set, table->vers_end_field()->field_index); - } } if (mysql_handle_list_of_derived(thd->lex, table_list, DT_MERGE_FOR_INSERT)) @@ -951,7 +944,8 @@ int mysql_prepare_delete(THD *thd, TABLE_LIST *table_list, if (select_lex->vers_setup_conds(thd, table_list)) DBUG_RETURN(true); } - if ((wild_num && setup_wild(thd, table_list, field_list, NULL, wild_num)) || + if ((wild_num && setup_wild(thd, table_list, field_list, NULL, wild_num, + &select_lex->hidden_bit_fields)) || setup_fields(thd, Ref_ptr_array(), field_list, MARK_COLUMNS_READ, NULL, NULL, 0) || setup_conds(thd, table_list, select_lex->leaf_tables, conds) || diff --git a/sql/sql_insert.cc b/sql/sql_insert.cc index d36d9aa82d2..9cb6a88fd4d 100644 --- a/sql/sql_insert.cc +++ b/sql/sql_insert.cc @@ -3786,7 +3786,7 @@ select_insert::prepare(List<Item> &values, SELECT_LEX_UNIT *u) 0 OK */ -int select_insert::prepare2(void) +int select_insert::prepare2(JOIN *) { DBUG_ENTER("select_insert::prepare2"); if (thd->lex->current_select->options & OPTION_BUFFER_RESULT && diff --git a/sql/sql_lex.cc b/sql/sql_lex.cc index d132f3a8f2b..18373ed4449 100644 --- a/sql/sql_lex.cc +++ b/sql/sql_lex.cc @@ -2274,6 +2274,7 @@ void st_select_lex::init_query() select_n_having_items= 0; n_sum_items= 0; n_child_sum_items= 0; + hidden_bit_fields= 0; subquery_in_having= explicit_limit= 0; is_item_list_lookup= 0; first_execution= 1; @@ -2810,6 +2811,10 @@ ulong st_select_lex::get_table_join_options() bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) { + + if (!((options & SELECT_DISTINCT) && !group_list.elements)) + hidden_bit_fields= 0; + // find_order_in_list() may need some extra space, so multiply by two. order_group_num*= 2; @@ -2824,7 +2829,8 @@ bool st_select_lex::setup_ref_array(THD *thd, uint order_group_num) select_n_reserved + select_n_having_items + select_n_where_fields + - order_group_num) * 5; + order_group_num + + hidden_bit_fields) * 5; if (!ref_pointer_array.is_null()) { /* diff --git a/sql/sql_lex.h b/sql/sql_lex.h index 4a98fd1d454..f1be02ef1f1 100644 --- a/sql/sql_lex.h +++ b/sql/sql_lex.h @@ -1037,6 +1037,11 @@ public: uint select_n_where_fields; /* reserved for exists 2 in */ uint select_n_reserved; + /* + it counts the number of bit fields in the SELECT list. These are used when DISTINCT is + converted to a GROUP BY involving BIT fields. + */ + uint hidden_bit_fields; enum_parsing_place parsing_place; /* where we are parsing expression */ enum_parsing_place context_analysis_place; /* where we are in prepare */ bool with_sum_func; /* sum function indicator */ diff --git a/sql/sql_list.h b/sql/sql_list.h index 1f069ef9388..0219c226803 100644 --- a/sql/sql_list.h +++ b/sql/sql_list.h @@ -138,6 +138,13 @@ public: first == rhs.first && last == rhs.last; } + base_list& operator=(const base_list &rhs) + { + elements= rhs.elements; + first= rhs.first; + last= elements ? rhs.last : &first; + return *this; + } inline void empty() { elements=0; first= &end_of_list; last=&first;} inline base_list() { empty(); } @@ -152,9 +159,7 @@ public: */ inline base_list(const base_list &tmp) :Sql_alloc() { - elements= tmp.elements; - first= tmp.first; - last= elements ? tmp.last : &first; + *this= tmp; } /** Construct a deep copy of the argument in memory root mem_root. diff --git a/sql/sql_partition.cc b/sql/sql_partition.cc index f7cd6c2a71e..c0ff1c54549 100644 --- a/sql/sql_partition.cc +++ b/sql/sql_partition.cc @@ -860,7 +860,7 @@ static bool fix_fields_part_func(THD *thd, Item* func_expr, TABLE *table, thd->lex->allow_sum_func= 0; if (likely(!(error= func_expr->fix_fields(thd, (Item**)&func_expr)))) - func_expr->walk(&Item::vcol_in_partition_func_processor, 0, NULL); + func_expr->walk(&Item::post_fix_fields_part_expr_processor, 0, NULL); /* Restore agg_field/agg_func and allow_sum_func, diff --git a/sql/sql_select.cc b/sql/sql_select.cc index 987415550fa..0f2a3236942 100644 --- a/sql/sql_select.cc +++ b/sql/sql_select.cc @@ -1083,7 +1083,9 @@ JOIN::prepare(TABLE_LIST *tables_init, select_lex != select_lex->master_unit()->global_parameters()) real_og_num+= select_lex->order_list.elements; - if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num)) + DBUG_ASSERT(select_lex->hidden_bit_fields == 0); + if (setup_wild(thd, tables_list, fields_list, &all_fields, wild_num, + &select_lex->hidden_bit_fields)) DBUG_RETURN(-1); if (select_lex->setup_ref_array(thd, real_og_num)) DBUG_RETURN(-1); @@ -2529,7 +2531,7 @@ int JOIN::optimize_stage2() ordered_index_usage= ordered_index_order_by; } } - } + } if (having) having_is_correlated= MY_TEST(having->used_tables() & OUTER_REF_TABLE_BIT); @@ -2671,6 +2673,25 @@ bool JOIN::add_having_as_table_cond(JOIN_TAB *tab) } +bool JOIN::add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *table_fields) +{ + /* + this will not walk into semi-join materialization nests but this is ok + because we will never need to save current rowids for those. + */ + for (JOIN_TAB *tab=join_tab; tab < cur; tab++) + { + if (!tab->keep_current_rowid) + continue; + Item *item= new (thd->mem_root) Item_temptable_rowid(tab->table); + item->fix_fields(thd, 0); + table_fields->push_back(item, thd->mem_root); + cur->tmp_table_param->func_count++; + } + return 0; +} + + /** Set info for aggregation tables @@ -2977,13 +2998,13 @@ bool JOIN::make_aggr_tables_info() (select_distinct && tmp_table_param.using_outer_summary_function)) { /* Must copy to another table */ DBUG_PRINT("info",("Creating group table")); - + calc_group_buffer(this, group_list); count_field_types(select_lex, &tmp_table_param, tmp_all_fields1, select_distinct && !group_list); - tmp_table_param.hidden_field_count= + tmp_table_param.hidden_field_count= tmp_all_fields1.elements - tmp_fields_list1.elements; - + curr_tab++; aggr_tables++; bzero(curr_tab, sizeof(JOIN_TAB)); @@ -2998,12 +3019,11 @@ bool JOIN::make_aggr_tables_info() if (join_tab->is_using_loose_index_scan()) tmp_table_param.precomputed_group_by= TRUE; - tmp_table_param.hidden_field_count= + tmp_table_param.hidden_field_count= curr_all_fields->elements - curr_fields_list->elements; ORDER *dummy= NULL; //TODO can use table->group here also - if (create_postjoin_aggr_table(curr_tab, - curr_all_fields, dummy, true, + if (create_postjoin_aggr_table(curr_tab, curr_all_fields, dummy, true, distinct, keep_row_order)) DBUG_RETURN(true); @@ -3274,11 +3294,13 @@ JOIN::create_postjoin_aggr_table(JOIN_TAB *tab, List<Item> *table_fields, */ ha_rows table_rows_limit= ((order == NULL || skip_sort_order) && !table_group && - !select_lex->with_sum_func) ? - select_limit : HA_POS_ERROR; + !select_lex->with_sum_func) ? select_limit + : HA_POS_ERROR; if (!(tab->tmp_table_param= new TMP_TABLE_PARAM(tmp_table_param))) DBUG_RETURN(true); + if (tmp_table_keep_current_rowid) + add_fields_for_current_rowid(tab, table_fields); tab->tmp_table_param->skip_create_table= true; TABLE* table= create_tmp_table(thd, tab->tmp_table_param, *table_fields, table_group, distinct, @@ -3673,7 +3695,7 @@ bool JOIN::prepare_result(List<Item> **columns_list) select_lex->handle_derived(thd->lex, DT_CREATE)) goto err; - if (result->prepare2()) + if (result->prepare2(this)) goto err; if ((select_lex->options & OPTION_SCHEMA_TABLE) && @@ -3810,7 +3832,7 @@ void JOIN::exec_inner() } columns_list= &procedure_fields_list; } - if (result->prepare2()) + if (result->prepare2(this)) DBUG_VOID_RETURN; if (!tables_list && (table_count || !select_lex->with_sum_func) && @@ -9373,7 +9395,7 @@ bool JOIN::get_best_combination() */ uint aggr_tables= (group_list ? 1 : 0) + (select_distinct ? - (tmp_table_param. using_outer_summary_function ? 2 : 1) : 0) + + (tmp_table_param.using_outer_summary_function ? 2 : 1) : 0) + (order ? 1 : 0) + (select_options & (SELECT_BIG_RESULT | OPTION_BUFFER_RESULT) ? 1 : 0) ; @@ -16628,8 +16650,6 @@ static void create_tmp_field_from_item_finalize(THD *thd, update the record in the original table. If modify_item is 0 then fill_record() will update the temporary table - @param convert_blob_length If >0 create a varstring(convert_blob_length) - field instead of blob. @retval 0 on error @@ -16947,6 +16967,10 @@ setup_tmp_table_column_bitmaps(TABLE *table, uchar *bitmaps) temporary table @param table_alias possible name of the temporary table that can be used for name resolving; can be "". + @param do_not_open only create the TABLE object, do not + open the table in the engine + @param keep_row_order rows need to be read in the order they were + inserted, the engine should preserve this order */ TABLE * @@ -23399,13 +23423,10 @@ get_sort_by_table(ORDER *a,ORDER *b, List<TABLE_LIST> &tables, calc how big buffer we need for comparing group entries. */ -static void -calc_group_buffer(JOIN *join,ORDER *group) +void calc_group_buffer(TMP_TABLE_PARAM *param, ORDER *group) { uint key_length=0, parts=0, null_parts=0; - if (group) - join->group= 1; for (; group ; group=group->next) { Item *group_item= *group->item; @@ -23475,9 +23496,16 @@ calc_group_buffer(JOIN *join,ORDER *group) if (group_item->maybe_null) null_parts++; } - join->tmp_table_param.group_length=key_length+null_parts; - join->tmp_table_param.group_parts=parts; - join->tmp_table_param.group_null_parts=null_parts; + param->group_length= key_length + null_parts; + param->group_parts= parts; + param->group_null_parts= null_parts; +} + +static void calc_group_buffer(JOIN *join, ORDER *group) +{ + if (group) + join->group= 1; + calc_group_buffer(&join->tmp_table_param, group); } @@ -26220,7 +26248,7 @@ bool JOIN::change_result(select_result *new_result, select_result *old_result) { result= new_result; if (result->prepare(fields_list, select_lex->master_unit()) || - result->prepare2()) + result->prepare2(this)) DBUG_RETURN(true); /* purecov: inspected */ DBUG_RETURN(false); } diff --git a/sql/sql_select.h b/sql/sql_select.h index 5e202e50152..2cc47f6ec3b 100644 --- a/sql/sql_select.h +++ b/sql/sql_select.h @@ -470,7 +470,7 @@ typedef struct st_join_table { Window_funcs_computation* window_funcs_step; /** - List of topmost expressions in the select list. The *next* JOIN TAB + List of topmost expressions in the select list. The *next* JOIN_TAB in the plan should use it to obtain correct values. Same applicable to all_fields. These lists are needed because after tmp tables functions will be turned to fields. These variables are pointing to @@ -1438,6 +1438,9 @@ public: enum { QEP_NOT_PRESENT_YET, QEP_AVAILABLE, QEP_DELETED} have_query_plan; + // if keep_current_rowid=true, whether they should be saved in temporary table + bool tmp_table_keep_current_rowid; + /* Additional WHERE and HAVING predicates to be considered for IN=>EXISTS subquery transformation of a JOIN object. @@ -1543,6 +1546,7 @@ public: pushdown_query= 0; original_join_tab= 0; explain= NULL; + tmp_table_keep_current_rowid= 0; all_fields= fields_arg; if (&fields_list != &fields_arg) /* Avoid valgrind-warning */ @@ -1776,6 +1780,7 @@ private: void cleanup_item_list(List<Item> &items) const; bool add_having_as_table_cond(JOIN_TAB *tab); bool make_aggr_tables_info(); + bool add_fields_for_current_rowid(JOIN_TAB *cur, List<Item> *fields); }; enum enum_with_bush_roots { WITH_BUSH_ROOTS, WITHOUT_BUSH_ROOTS}; @@ -2382,6 +2387,7 @@ int append_possible_keys(MEM_ROOT *alloc, String_list &list, TABLE *table, #define RATIO_TO_PACK_ROWS 2 #define MIN_STRING_LENGTH_TO_PACK_ROWS 10 +void calc_group_buffer(TMP_TABLE_PARAM *param, ORDER *group); TABLE *create_tmp_table(THD *thd,TMP_TABLE_PARAM *param,List<Item> &fields, ORDER *group, bool distinct, bool save_sum_fields, ulonglong select_options, ha_rows rows_limit, diff --git a/sql/sql_table.cc b/sql/sql_table.cc index ccf68b1d06e..550b11d813e 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -7908,6 +7908,7 @@ mysql_prepare_alter_table(THD *thd, TABLE *table, if (field->default_value) field->default_value->expr->walk(&Item::rename_fields_processor, 1, &column_rename_param); + table->m_needs_reopen= 1; // because new column name is on thd->mem_root } /* Check if field is changed */ diff --git a/sql/sql_union.cc b/sql/sql_union.cc index 346b21c0a53..f74c7b5e27a 100644 --- a/sql/sql_union.cc +++ b/sql/sql_union.cc @@ -499,14 +499,14 @@ void select_union_recursive::cleanup() bool select_union_direct::change_result(select_result *new_result) { result= new_result; - return (result->prepare(unit->types, unit) || result->prepare2()); + return (result->prepare(unit->types, unit) || result->prepare2(NULL)); } bool select_union_direct::postponed_prepare(List<Item> &types) { if (result != NULL) - return (result->prepare(types, unit) || result->prepare2()); + return (result->prepare(types, unit) || result->prepare2(NULL)); else return false; } @@ -1932,6 +1932,7 @@ bool st_select_lex::cleanup() } inner_refs_list.empty(); exclude_from_table_unique_test= FALSE; + hidden_bit_fields= 0; DBUG_RETURN(error); } diff --git a/sql/sql_update.cc b/sql/sql_update.cc index 26bd1d79d69..a8b86448d5e 100644 --- a/sql/sql_update.cc +++ b/sql/sql_update.cc @@ -2167,22 +2167,12 @@ loop_end: tbl->prepare_for_position(); join->map2table[tbl->tablenr]->keep_current_rowid= true; - Field_string *field= new Field_string(tbl->file->ref_length, 0, - &field_name, - &my_charset_bin); - if (!field) - DBUG_RETURN(1); - field->init(tbl); - /* - The field will be converted to varstring when creating tmp table if - table to be updated was created by mysql 4.1. Deny this. - */ - field->can_alter_field_type= 0; - Item_field *ifield= new (thd->mem_root) Item_field(join->thd, (Field *) field); - if (!ifield) + Item_temptable_rowid *item= + new (thd->mem_root) Item_temptable_rowid(tbl); + if (!item) DBUG_RETURN(1); - ifield->maybe_null= 0; - if (temp_fields.push_back(ifield, thd->mem_root)) + item->fix_fields(thd, 0); + if (temp_fields.push_back(item, thd->mem_root)) DBUG_RETURN(1); } while ((tbl= tbl_it++)); @@ -2193,10 +2183,10 @@ loop_end: group.direction= ORDER::ORDER_ASC; group.item= (Item**) temp_fields.head_ref(); - tmp_param->quick_group=1; - tmp_param->field_count=temp_fields.elements; - tmp_param->group_parts=1; - tmp_param->group_length= table->file->ref_length; + tmp_param->quick_group= 1; + tmp_param->field_count= temp_fields.elements; + tmp_param->func_count= temp_fields.elements - 1; + calc_group_buffer(tmp_param, &group); /* small table, ignore SQL_BIG_TABLES */ my_bool save_big_tables= thd->variables.big_tables; thd->variables.big_tables= FALSE; @@ -2208,10 +2198,66 @@ loop_end: DBUG_RETURN(1); tmp_tables[cnt]->file->extra(HA_EXTRA_WRITE_CACHE); } + join->tmp_table_keep_current_rowid= TRUE; DBUG_RETURN(0); } +static TABLE *item_rowid_table(Item *item) +{ + if (item->type() != Item::FUNC_ITEM) + return NULL; + Item_func *func= (Item_func *)item; + if (func->functype() != Item_func::TEMPTABLE_ROWID) + return NULL; + Item_temptable_rowid *itr= (Item_temptable_rowid *)func; + return itr->table; +} + + +/* + multi_update stores a rowid and new field values for every updated row in a + temporary table (one temporary table per updated table). These rowids are + obtained via Item_temptable_rowid's by calling handler::position(). But if + the join is resolved via a temp table, rowids cannot be obtained from + handler::position() in the multi_update::send_data(). So, they're stored in + the join's temp table (JOIN::add_fields_for_current_rowid()) and here we + replace Item_temptable_rowid's (that would've done handler::position()) with + Item_field's (that will simply take the corresponding field value from the + temp table). +*/ +int multi_update::prepare2(JOIN *join) +{ + if (!join->need_tmp || !join->tmp_table_keep_current_rowid) + return 0; + + // there cannot be many tmp tables in multi-update + JOIN_TAB *tmptab= join->join_tab + join->exec_join_tab_cnt(); + + for (Item **it= tmptab->tmp_table_param->items_to_copy; *it ; it++) + { + TABLE *tbl= item_rowid_table(*it); + if (!tbl) + continue; + for (uint i= 0; i < table_count; i++) + { + for (Item **it2= tmp_table_param[i].items_to_copy; *it2; it2++) + { + if (item_rowid_table(*it2) != tbl) + continue; + Item *fld= new (thd->mem_root) + Item_field(thd, (*it)->get_tmp_table_field()); + if (!fld) + return 1; + fld->set_result_field((*it2)->get_tmp_table_field()); + *it2= fld; + } + } + } + return 0; +} + + multi_update::~multi_update() { TABLE_LIST *table; @@ -2384,29 +2430,11 @@ int multi_update::send_data(List<Item> ¬_used_values) { int error; TABLE *tmp_table= tmp_tables[offset]; - /* - For updatable VIEW store rowid of the updated table and - rowids of tables used in the CHECK OPTION condition. - */ - uint field_num= 0; - List_iterator_fast<TABLE> tbl_it(unupdated_check_opt_tables); - /* Set first tbl = table and then tbl to tables from tbl_it */ - TABLE *tbl= table; - do - { - tbl->file->position(tbl->record[0]); - memcpy((char*) tmp_table->field[field_num]->ptr, - (char*) tbl->file->ref, tbl->file->ref_length); - /* - For outer joins a rowid field may have no NOT_NULL_FLAG, - so we have to reset NULL bit for this field. - (set_notnull() resets NULL bit only if available). - */ - tmp_table->field[field_num]->set_notnull(); - field_num++; - } while ((tbl= tbl_it++)); - + if (copy_funcs(tmp_table_param[offset].items_to_copy, thd)) + DBUG_RETURN(1); /* Store regular updated fields in the row. */ + DBUG_ASSERT(1 + unupdated_check_opt_tables.elements == + tmp_table_param[offset].func_count); fill_record(thd, tmp_table, tmp_table->field + 1 + unupdated_check_opt_tables.elements, *values_for_table[offset], TRUE, FALSE); diff --git a/sql/table.cc b/sql/table.cc index dd0bff062d7..ced8f8c0ae8 100644 --- a/sql/table.cc +++ b/sql/table.cc @@ -5223,14 +5223,25 @@ int TABLE_LIST::view_check_option(THD *thd, bool ignore_failure) int TABLE::verify_constraints(bool ignore_failure) { + /* + We have to check is_error() first as we are checking it for each + constraint to catch fatal warnings. + */ + if (in_use->is_error()) + return (VIEW_CHECK_ERROR); + /* go trough check option clauses for fields and table */ if (check_constraints && !(in_use->variables.option_bits & OPTION_NO_CHECK_CONSTRAINT_CHECKS)) { for (Virtual_column_info **chk= check_constraints ; *chk ; chk++) { - /* yes! NULL is ok, see 4.23.3.4 Table check constraints, part 2, SQL:2016 */ - if ((*chk)->expr->val_int() == 0 && !(*chk)->expr->null_value) + /* + yes! NULL is ok. + see 4.23.3.4 Table check constraints, part 2, SQL:2016 + */ + if (((*chk)->expr->val_int() == 0 && !(*chk)->expr->null_value) || + in_use->is_error()) { my_error(ER_CONSTRAINT_FAILED, MYF(ignore_failure ? ME_JUST_WARNING : 0), (*chk)->name.str, @@ -5239,7 +5250,11 @@ int TABLE::verify_constraints(bool ignore_failure) } } } - return(VIEW_CHECK_OK); + /* + We have to check in_use() as checking constraints may have generated + warnings that should be treated as errors + */ + return(!in_use->is_error() ? VIEW_CHECK_OK : VIEW_CHECK_ERROR); } /* @@ -8454,8 +8469,8 @@ Item* TABLE_LIST::build_pushable_cond_for_table(THD *thd, Item *cond) new_cond->argument_list()->push_back(fix, thd->mem_root); } - if (is_fix_needed) - new_cond->fix_fields(thd, 0); + if (is_fix_needed && new_cond->fix_fields(thd, 0)) + return 0; switch (new_cond->argument_list()->elements) { diff --git a/storage/federatedx/federatedx_io_mysql.cc b/storage/federatedx/federatedx_io_mysql.cc index a83e54513d3..cc4d8ca7c70 100644 --- a/storage/federatedx/federatedx_io_mysql.cc +++ b/storage/federatedx/federatedx_io_mysql.cc @@ -453,7 +453,8 @@ int federatedx_io_mysql::actual_query(const char *buffer, size_t length) mysql.reconnect= 1; } - error= mysql_real_query(&mysql, buffer, (ulong)length); + if (!(error= mysql_real_query(&mysql, STRING_WITH_LEN("set time_zone='+00:00'")))) + error= mysql_real_query(&mysql, buffer, (ulong)length); DBUG_RETURN(error); } diff --git a/storage/federatedx/ha_federatedx.cc b/storage/federatedx/ha_federatedx.cc index 8c21906fe84..3e449a3019f 100644 --- a/storage/federatedx/ha_federatedx.cc +++ b/storage/federatedx/ha_federatedx.cc @@ -318,6 +318,7 @@ OF THIS SOFTWARE, EVEN IF ADVISED OF THE POSSIBILITY OF SUCH DAMAGE. #include "sql_servers.h" #include "sql_analyse.h" // append_escaped() #include "sql_show.h" // append_identifier() +#include "tztime.h" // my_tz_OFFSET0 #ifdef I_AM_PARANOID #define MIN_PORT 1023 @@ -340,6 +341,8 @@ static const uint sizeof_trailing_comma= sizeof(", ") - 1; static const uint sizeof_trailing_and= sizeof(" AND ") - 1; static const uint sizeof_trailing_where= sizeof(" WHERE ") - 1; +static Time_zone *UTC= 0; + /* Static declaration for handerton */ static handler *federatedx_create_handler(handlerton *hton, TABLE_SHARE *table, @@ -860,8 +863,10 @@ uint ha_federatedx::convert_row_to_internal_format(uchar *record, Field **field; int column= 0; my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, table->write_set); + Time_zone *saved_time_zone= table->in_use->variables.time_zone; DBUG_ENTER("ha_federatedx::convert_row_to_internal_format"); + table->in_use->variables.time_zone= UTC; lengths= io->fetch_lengths(result); for (field= table->field; *field; field++, column++) @@ -885,6 +890,7 @@ uint ha_federatedx::convert_row_to_internal_format(uchar *record, } (*field)->move_field_offset(-old_ptr); } + table->in_use->variables.time_zone= saved_time_zone; dbug_tmp_restore_column_map(table->write_set, old_map); DBUG_RETURN(0); } @@ -1213,6 +1219,7 @@ bool ha_federatedx::create_where_from_key(String *to, char tmpbuff[FEDERATEDX_QUERY_BUFFER_SIZE]; String tmp(tmpbuff, sizeof(tmpbuff), system_charset_info); const key_range *ranges[2]= { start_key, end_key }; + Time_zone *saved_time_zone= table->in_use->variables.time_zone; my_bitmap_map *old_map; DBUG_ENTER("ha_federatedx::create_where_from_key"); @@ -1220,6 +1227,7 @@ bool ha_federatedx::create_where_from_key(String *to, if (start_key == NULL && end_key == NULL) DBUG_RETURN(1); + table->in_use->variables.time_zone= UTC; old_map= dbug_tmp_use_all_columns(table, table->write_set); for (uint i= 0; i <= 1; i++) { @@ -1397,6 +1405,7 @@ prepare_for_next_key_part: } } dbug_tmp_restore_column_map(table->write_set, old_map); + table->in_use->variables.time_zone= saved_time_zone; if (both_not_null) if (tmp.append(STRING_WITH_LEN(") "))) @@ -1412,6 +1421,7 @@ prepare_for_next_key_part: err: dbug_tmp_restore_column_map(table->write_set, old_map); + table->in_use->variables.time_zone= saved_time_zone; DBUG_RETURN(1); } @@ -1582,6 +1592,12 @@ static FEDERATEDX_SHARE *get_share(const char *table_name, TABLE *table) mysql_mutex_lock(&federatedx_mutex); + if (unlikely(!UTC)) + { + String tz_00_name(STRING_WITH_LEN("+00:00"), &my_charset_bin); + UTC= my_tz_find(current_thd, &tz_00_name); + } + tmp_share.share_key= table_name; tmp_share.share_key_length= (int)strlen(table_name); if (parse_url(&mem_root, &tmp_share, table->s, 0)) @@ -1978,9 +1994,11 @@ int ha_federatedx::write_row(uchar *buf) String insert_field_value_string(insert_field_value_buffer, sizeof(insert_field_value_buffer), &my_charset_bin); + Time_zone *saved_time_zone= table->in_use->variables.time_zone; my_bitmap_map *old_map= dbug_tmp_use_all_columns(table, table->read_set); DBUG_ENTER("ha_federatedx::write_row"); + table->in_use->variables.time_zone= UTC; values_string.length(0); insert_field_value_string.length(0); @@ -2033,6 +2051,7 @@ int ha_federatedx::write_row(uchar *buf) } } dbug_tmp_restore_column_map(table->read_set, old_map); + table->in_use->variables.time_zone= saved_time_zone; /* if there were no fields, we don't want to add a closing paren @@ -2340,6 +2359,8 @@ int ha_federatedx::update_row(const uchar *old_data, const uchar *new_data) field=oldvalue */ + Time_zone *saved_time_zone= table->in_use->variables.time_zone; + table->in_use->variables.time_zone= UTC; for (Field **field= table->field; *field; field++) { if (bitmap_is_set(table->write_set, (*field)->field_index)) @@ -2391,6 +2412,7 @@ int ha_federatedx::update_row(const uchar *old_data, const uchar *new_data) where_string.append(STRING_WITH_LEN(" AND ")); } } + table->in_use->variables.time_zone= saved_time_zone; /* Remove last ', '. This works as there must be at least on updated field */ update_string.length(update_string.length() - sizeof_trailing_comma); @@ -2451,6 +2473,8 @@ int ha_federatedx::delete_row(const uchar *buf) share->table_name_length, ident_quote_char); delete_string.append(STRING_WITH_LEN(" WHERE ")); + Time_zone *saved_time_zone= table->in_use->variables.time_zone; + table->in_use->variables.time_zone= UTC; for (Field **field= table->field; *field; field++) { Field *cur_field= *field; @@ -2478,6 +2502,7 @@ int ha_federatedx::delete_row(const uchar *buf) delete_string.append(STRING_WITH_LEN(" AND ")); } } + table->in_use->variables.time_zone= saved_time_zone; // Remove trailing AND delete_string.length(delete_string.length() - sizeof_trailing_and); @@ -3216,7 +3241,10 @@ int ha_federatedx::delete_all_rows() query.length(0); query.set_charset(system_charset_info); - query.append(STRING_WITH_LEN("TRUNCATE ")); + if (thd->lex->sql_command == SQLCOM_TRUNCATE) + query.append(STRING_WITH_LEN("TRUNCATE ")); + else + query.append(STRING_WITH_LEN("DELETE FROM ")); append_ident(&query, share->table_name, share->table_name_length, ident_quote_char); @@ -3583,6 +3611,8 @@ int ha_federatedx::discover_assisted(handlerton *hton, THD* thd, MYSQL mysql; char buf[1024]; String query(buf, sizeof(buf), cs); + static LEX_CSTRING cut_clause={STRING_WITH_LEN(" WITH SYSTEM VERSIONING")}; + int cut_offset; MYSQL_RES *res; MYSQL_ROW rdata; ulong *rlen; @@ -3593,8 +3623,7 @@ int ha_federatedx::discover_assisted(handlerton *hton, THD* thd, mysql_init(&mysql); mysql_options(&mysql, MYSQL_SET_CHARSET_NAME, cs->csname); - mysql_options(&mysql, MYSQL_OPT_USE_THREAD_SPECIFIC_MEMORY, - (char*) &my_true); + mysql_options(&mysql, MYSQL_OPT_USE_THREAD_SPECIFIC_MEMORY, (char*)&my_true); if (!mysql_real_connect(&mysql, tmp_share.hostname, tmp_share.username, tmp_share.password, tmp_share.database, @@ -3618,6 +3647,10 @@ int ha_federatedx::discover_assisted(handlerton *hton, THD* thd, goto err2; query.copy(rdata[1], rlen[1], cs); + cut_offset= (int)query.length() - (int)cut_clause.length; + if (cut_offset > 0 && !memcmp(query.ptr() + cut_offset, + cut_clause.str, cut_clause.length)) + query.length(cut_offset); query.append(STRING_WITH_LEN(" CONNECTION='"), cs); query.append_for_single_quote(table_s->connect_string.str, table_s->connect_string.length); diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc index a1622c0d13f..228e516551d 100644 --- a/storage/innobase/handler/handler0alter.cc +++ b/storage/innobase/handler/handler0alter.cc @@ -4904,6 +4904,15 @@ prepare_inplace_alter_table_dict( if (ha_alter_info->handler_flags & ALTER_ADD_NON_UNIQUE_NON_PRIM_INDEX) { + for (ulint i = 0; i < ctx->num_to_add_vcol; i++) { + /* Set mbminmax for newly added column */ + dict_col_t& col = ctx->add_vcol[i].m_col; + ulint mbminlen, mbmaxlen; + dtype_get_mblen(col.mtype, col.prtype, + &mbminlen, &mbmaxlen); + col.mbminlen = mbminlen; + col.mbmaxlen = mbmaxlen; + } add_v = static_cast<dict_add_v_col_t*>( mem_heap_alloc(ctx->heap, sizeof *add_v)); add_v->n_v_col = ctx->num_to_add_vcol; diff --git a/storage/innobase/include/univ.i b/storage/innobase/include/univ.i index 5b43469f48a..ffa4967b9de 100644 --- a/storage/innobase/include/univ.i +++ b/storage/innobase/include/univ.i @@ -41,7 +41,7 @@ Created 1/20/1994 Heikki Tuuri #define INNODB_VERSION_MAJOR 5 #define INNODB_VERSION_MINOR 7 -#define INNODB_VERSION_BUGFIX 21 +#define INNODB_VERSION_BUGFIX 22 /* The following is the InnoDB version as shown in SELECT plugin_version FROM information_schema.plugins; diff --git a/storage/innobase/srv/srv0srv.cc b/storage/innobase/srv/srv0srv.cc index 2eec7d467ea..d902a60d5ea 100644 --- a/storage/innobase/srv/srv0srv.cc +++ b/storage/innobase/srv/srv0srv.cc @@ -2739,8 +2739,18 @@ DECLARE_THREAD(srv_purge_coordinator_thread)( #endif /* UNIV_DEBUG_THREAD_CREATION */ /* Ensure that all the worker threads quit. */ - if (srv_n_purge_threads > 1) { - srv_release_threads(SRV_WORKER, srv_n_purge_threads - 1); + if (ulint n_workers = srv_n_purge_threads - 1) { + const srv_slot_t* slot; + const srv_slot_t* const end = &srv_sys.sys_threads[ + srv_sys.n_sys_threads]; + + do { + srv_release_threads(SRV_WORKER, n_workers); + srv_sys_mutex_enter(); + for (slot = &srv_sys.sys_threads[2]; + !slot++->in_use && slot < end; ); + srv_sys_mutex_exit(); + } while (slot < end); } innobase_destroy_background_thd(thd); @@ -2795,6 +2805,7 @@ void srv_purge_wakeup() { ut_ad(!srv_read_only_mode); + ut_ad(!sync_check_iterate(sync_check())); if (srv_force_recovery >= SRV_FORCE_NO_BACKGROUND) { return; diff --git a/storage/rocksdb/ha_rocksdb.cc b/storage/rocksdb/ha_rocksdb.cc index edde18bd9f5..ab00077c43a 100644 --- a/storage/rocksdb/ha_rocksdb.cc +++ b/storage/rocksdb/ha_rocksdb.cc @@ -5088,6 +5088,12 @@ static ulonglong rdb_get_int_col_max_value(const Field *field) { case HA_KEYTYPE_LONGLONG: max_value = 0x7FFFFFFFFFFFFFFFULL; break; + case HA_KEYTYPE_FLOAT: + max_value = 0x1000000ULL; + break; + case HA_KEYTYPE_DOUBLE: + max_value = 0x20000000000000ULL; + break; default: abort(); } @@ -9296,8 +9302,16 @@ int ha_rocksdb::check_and_lock_sk(const uint &key_id, The bloom filter may need to be disabled for this lookup. */ + uchar min_bound_buf[MAX_KEY_LENGTH]; + uchar max_bound_buf[MAX_KEY_LENGTH]; + rocksdb::Slice min_bound_slice; + rocksdb::Slice max_bound_slice; const bool total_order_seek = !check_bloom_and_set_bounds( - ha_thd(), kd, new_slice, all_parts_used); + ha_thd(), kd, new_slice, all_parts_used, + min_bound_buf, + max_bound_buf, + &min_bound_slice, + &max_bound_slice); const bool fill_cache = !THDVAR(ha_thd(), skip_fill_cache); const rocksdb::Status s = @@ -9309,7 +9323,7 @@ int ha_rocksdb::check_and_lock_sk(const uint &key_id, rocksdb::Iterator *const iter = row_info.tx->get_iterator( kd.get_cf(), total_order_seek, fill_cache, - m_eq_cond_lower_bound_slice, m_eq_cond_upper_bound_slice, + min_bound_slice, max_bound_slice, true /* read current data */, false /* acquire snapshot */); /* @@ -9717,25 +9731,34 @@ int ha_rocksdb::update_write_row(const uchar *const old_data, If the index was reverse order, upper bound would be 0x0000b3eb003f65c5e78857, and lower bound would be 0x0000b3eb003f65c5e78859. These cover given eq condition range. + + @param lower_bound_buf IN Buffer for lower bound + @param upper_bound_buf IN Buffer for upper bound + + @param outer_u */ void ha_rocksdb::setup_iterator_bounds(const Rdb_key_def &kd, - const rocksdb::Slice &eq_cond) { + const rocksdb::Slice &eq_cond, + uchar *lower_bound_buf, + uchar *upper_bound_buf, + rocksdb::Slice *out_lower_bound, + rocksdb::Slice *out_upper_bound) { uint eq_cond_len = eq_cond.size(); - memcpy(m_eq_cond_upper_bound, eq_cond.data(), eq_cond_len); - kd.successor(m_eq_cond_upper_bound, eq_cond_len); - memcpy(m_eq_cond_lower_bound, eq_cond.data(), eq_cond_len); - kd.predecessor(m_eq_cond_lower_bound, eq_cond_len); + memcpy(upper_bound_buf, eq_cond.data(), eq_cond_len); + kd.successor(upper_bound_buf, eq_cond_len); + memcpy(lower_bound_buf, eq_cond.data(), eq_cond_len); + kd.predecessor(lower_bound_buf, eq_cond_len); if (kd.m_is_reverse_cf) { - m_eq_cond_upper_bound_slice = - rocksdb::Slice((const char *)m_eq_cond_lower_bound, eq_cond_len); - m_eq_cond_lower_bound_slice = - rocksdb::Slice((const char *)m_eq_cond_upper_bound, eq_cond_len); + *out_upper_bound = + rocksdb::Slice((const char *)lower_bound_buf, eq_cond_len); + *out_lower_bound = + rocksdb::Slice((const char *)upper_bound_buf, eq_cond_len); } else { - m_eq_cond_upper_bound_slice = - rocksdb::Slice((const char *)m_eq_cond_upper_bound, eq_cond_len); - m_eq_cond_lower_bound_slice = - rocksdb::Slice((const char *)m_eq_cond_lower_bound, eq_cond_len); + *out_upper_bound = + rocksdb::Slice((const char *)upper_bound_buf, eq_cond_len); + *out_lower_bound = + rocksdb::Slice((const char *)lower_bound_buf, eq_cond_len); } } @@ -9755,7 +9778,11 @@ void ha_rocksdb::setup_scan_iterator(const Rdb_key_def &kd, bool skip_bloom = true; const rocksdb::Slice eq_cond(slice->data(), eq_cond_len); - if (check_bloom_and_set_bounds(ha_thd(), kd, eq_cond, use_all_keys)) { + if (check_bloom_and_set_bounds(ha_thd(), kd, eq_cond, use_all_keys, + m_eq_cond_lower_bound, + m_eq_cond_upper_bound, + &m_eq_cond_lower_bound_slice, + &m_eq_cond_upper_bound_slice)) { skip_bloom = false; } @@ -10938,7 +10965,11 @@ int ha_rocksdb::remove_rows(Rdb_tbl_def *const tbl) { kd.get_infimum_key(reinterpret_cast<uchar *>(key_buf), &key_len); rocksdb::ColumnFamilyHandle *cf = kd.get_cf(); const rocksdb::Slice table_key(key_buf, key_len); - setup_iterator_bounds(kd, table_key); + setup_iterator_bounds(kd, table_key, + m_eq_cond_lower_bound, + m_eq_cond_upper_bound, + &m_eq_cond_lower_bound_slice, + &m_eq_cond_upper_bound_slice); opts.iterate_lower_bound = &m_eq_cond_lower_bound_slice; opts.iterate_upper_bound = &m_eq_cond_upper_bound_slice; std::unique_ptr<rocksdb::Iterator> it(rdb->NewIterator(opts, cf)); @@ -11734,7 +11765,9 @@ bool ha_rocksdb::prepare_inplace_alter_table( if (!new_tdef) { new_tdef = m_tbl_def; } - max_auto_incr = load_auto_incr_value_from_index(); + if (table->found_next_number_field) { + max_auto_incr = load_auto_incr_value_from_index(); + } } ha_alter_info->handler_ctx = new Rdb_inplace_alter_ctx( @@ -12714,10 +12747,16 @@ void Rdb_background_thread::run() { bool ha_rocksdb::check_bloom_and_set_bounds(THD *thd, const Rdb_key_def &kd, const rocksdb::Slice &eq_cond, - const bool use_all_keys) { + const bool use_all_keys, + uchar *lower_bound_buf, + uchar *upper_bound_buf, + rocksdb::Slice *out_lower_bound, + rocksdb::Slice *out_upper_bound) { bool can_use_bloom = can_use_bloom_filter(thd, kd, eq_cond, use_all_keys); if (!can_use_bloom) { - setup_iterator_bounds(kd, eq_cond); + setup_iterator_bounds(kd, eq_cond, + lower_bound_buf, upper_bound_buf, + out_lower_bound, out_upper_bound); } return can_use_bloom; } diff --git a/storage/rocksdb/ha_rocksdb.h b/storage/rocksdb/ha_rocksdb.h index 0f922c22bc7..171c303ac4a 100644 --- a/storage/rocksdb/ha_rocksdb.h +++ b/storage/rocksdb/ha_rocksdb.h @@ -653,13 +653,21 @@ class ha_rocksdb : public my_core::handler { enum ha_rkey_function find_flag) const MY_ATTRIBUTE((__nonnull__, __warn_unused_result__)); void setup_iterator_bounds(const Rdb_key_def &kd, - const rocksdb::Slice &eq_cond); + const rocksdb::Slice &eq_cond, + uchar *lower_bound_buf, + uchar *upper_bound_buf, + rocksdb::Slice *out_lower_bound, + rocksdb::Slice *out_upper_bound); bool can_use_bloom_filter(THD *thd, const Rdb_key_def &kd, const rocksdb::Slice &eq_cond, const bool use_all_keys); bool check_bloom_and_set_bounds(THD *thd, const Rdb_key_def &kd, const rocksdb::Slice &eq_cond, - const bool use_all_keys); + const bool use_all_keys, + uchar *lower_bound_buf, + uchar *upper_bound_buf, + rocksdb::Slice *out_lower_bound, + rocksdb::Slice *out_upper_bound); void setup_scan_iterator(const Rdb_key_def &kd, rocksdb::Slice *slice, const bool use_all_keys, const uint eq_cond_len) MY_ATTRIBUTE((__nonnull__)); diff --git a/storage/rocksdb/mysql-test/rocksdb/include/bulk_load.inc b/storage/rocksdb/mysql-test/rocksdb/include/bulk_load.inc index 6472b969ce6..8ec97510dbd 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/bulk_load.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/bulk_load.inc @@ -50,7 +50,7 @@ eval CREATE TABLE t3( perl; my $fn = $ENV{'ROCKSDB_INFILE'}; open(my $fh, '>', $fn) || die "perl open($fn): $!"; -my $max = 5000000; +my $max = 2500000; my $desc = $ENV{'MTR_DATA_ORDER_DESC'}; my @chars = ("A".."Z", "a".."z", "0".."9"); my @powers_of_26 = (26 * 26 * 26 * 26, 26 * 26 * 26, 26 * 26, 26, 1); @@ -118,15 +118,12 @@ ANALYZE TABLE t1, t2, t3; SHOW TABLE STATUS WHERE name LIKE 't%'; # Make sure all the data is there. -select count(pk) from t1; -select count(a) from t1; -select count(b) from t1; -select count(pk) from t2; -select count(a) from t2; -select count(b) from t2; -select count(pk) from t3; -select count(a) from t3; -select count(b) from t3; +select count(pk),count(a) from t1; +select count(b) from t1; +select count(pk),count(a) from t2; +select count(b) from t2; +select count(pk),count(a) from t3; +select count(b) from t3; # Create a dummy file with a bulk load extesion. It should be removed when # the server starts diff --git a/storage/rocksdb/mysql-test/rocksdb/include/bulk_load_unsorted.inc b/storage/rocksdb/mysql-test/rocksdb/include/bulk_load_unsorted.inc index 151ec0ccf14..5f808087e3e 100644 --- a/storage/rocksdb/mysql-test/rocksdb/include/bulk_load_unsorted.inc +++ b/storage/rocksdb/mysql-test/rocksdb/include/bulk_load_unsorted.inc @@ -84,7 +84,7 @@ perl; my $fn = $ENV{'ROCKSDB_INFILE'}; open(my $fh, '>', $fn) || die "perl open($fn): $!"; binmode $fh; -my $max = 5000000; +my $max = 2500000; my $sign = 1; for (my $ii = 0; $ii < $max; $ii++) { @@ -129,12 +129,9 @@ ANALYZE TABLE t1, t2, t3; SHOW TABLE STATUS WHERE name LIKE 't%'; # Make sure all the data is there. -select count(a) from t1; -select count(b) from t1; -select count(a) from t2; -select count(b) from t2; -select count(a) from t3; -select count(b) from t3; +select count(a),count(b) from t1; +select count(a),count(b) from t2; +select count(a),count(b) from t3; SELECT * FROM t1 FORCE INDEX (PRIMARY) LIMIT 3; SELECT * FROM t2 FORCE INDEX (PRIMARY) LIMIT 3; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result b/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result index 18365338d0c..08f2329f688 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/add_index_inplace_sstfilewriter.result @@ -14,11 +14,14 @@ select count(b) from t1; count(b) 300000 ALTER TABLE t1 ADD INDEX kb(b), ALGORITHM=INPLACE; +set @tmp= @@rocksdb_max_row_locks; +set session rocksdb_max_row_locks=1000; ALTER TABLE t1 ADD INDEX kb_copy(b), ALGORITHM=COPY; ERROR HY000: Status error 10 received from RocksDB: Operation aborted: Failed to acquire lock due to max_num_locks limit set session rocksdb_bulk_load=1; ALTER TABLE t1 ADD INDEX kb_copy(b), ALGORITHM=COPY; set session rocksdb_bulk_load=0; +set session rocksdb_max_row_locks=@tmp; SELECT COUNT(*) as c FROM (SELECT COALESCE(LOWER(CONV(BIT_XOR(CAST(CRC32(CONCAT_WS('#', `b`, CONCAT(ISNULL(`b`)))) AS UNSIGNED)), 10, 16)), 0) AS crc FROM `t1` FORCE INDEX(`kb`) UNION DISTINCT diff --git a/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result b/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result index 5da9a7e7e1c..8cc9b070a70 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/autoinc_vars.result @@ -140,3 +140,13 @@ SELECT * FROM t1; a b 18446744073709551613 a DROP TABLE t1; +#---------------------------------- +# Issue #792 Crash in autoincrement +#---------------------------------- +CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY,C2 CHAR) ENGINE=ROCKSDB; +INSERT INTO t1 VALUES(2177,0); +DROP TABLE t1; +CREATE TABLE t0(c0 BLOB) ENGINE=ROCKSDB; +INSERT INTO t0 VALUES(0); +ALTER TABLE t0 AUTO_INCREMENT=0; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter.result index 5f1083a1bb2..bc5d685f89b 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter.result @@ -43,6 +43,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -443,6 +444,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -843,6 +845,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -1243,6 +1246,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -1643,6 +1647,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter_skip.result b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter_skip.result index 2496f349427..28475630564 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter_skip.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bloomfilter_skip.result @@ -43,6 +43,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -443,6 +444,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -843,6 +845,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -1243,6 +1246,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) @@ -1643,6 +1647,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; call bloom_start(); select count(*) from t1; count(*) diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load.result index d13ffa3ac24..21417caf760 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load.result @@ -38,9 +38,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3; set rocksdb_bulk_load=0; SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -48,36 +48,27 @@ test.t2 analyze status OK test.t3 analyze status OK SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N -select count(pk) from t1; -count(pk) -5000000 -select count(a) from t1; -count(a) -5000000 -select count(b) from t1; +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +select count(pk),count(a) from t1; +count(pk) count(a) +2500000 2500000 +select count(b) from t1; count(b) -5000000 -select count(pk) from t2; -count(pk) -5000000 -select count(a) from t2; -count(a) -5000000 -select count(b) from t2; +2500000 +select count(pk),count(a) from t2; +count(pk) count(a) +2500000 2500000 +select count(b) from t2; count(b) -5000000 -select count(pk) from t3; -count(pk) -5000000 -select count(a) from t3; -count(a) -5000000 -select count(b) from t3; +2500000 +select count(pk),count(a) from t3; +count(pk) count(a) +2500000 2500000 +select count(b) from t3; count(b) -5000000 +2500000 longfilenamethatvalidatesthatthiswillgetdeleted.bulk_load.tmp test.bulk_load.tmp disconnect other; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf.result index bb8097867c0..484c2a89c3a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf.result @@ -38,9 +38,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3; set rocksdb_bulk_load=0; SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -48,36 +48,27 @@ test.t2 analyze status OK test.t3 analyze status OK SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N -select count(pk) from t1; -count(pk) -5000000 -select count(a) from t1; -count(a) -5000000 -select count(b) from t1; +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +select count(pk),count(a) from t1; +count(pk) count(a) +2500000 2500000 +select count(b) from t1; count(b) -5000000 -select count(pk) from t2; -count(pk) -5000000 -select count(a) from t2; -count(a) -5000000 -select count(b) from t2; +2500000 +select count(pk),count(a) from t2; +count(pk) count(a) +2500000 2500000 +select count(b) from t2; count(b) -5000000 -select count(pk) from t3; -count(pk) -5000000 -select count(a) from t3; -count(a) -5000000 -select count(b) from t3; +2500000 +select count(pk),count(a) from t3; +count(pk) count(a) +2500000 2500000 +select count(b) from t3; count(b) -5000000 +2500000 longfilenamethatvalidatesthatthiswillgetdeleted.bulk_load.tmp test.bulk_load.tmp disconnect other; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf_and_data.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf_and_data.result index 7519796a64f..35a2845cb42 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf_and_data.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_cf_and_data.result @@ -38,9 +38,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3; set rocksdb_bulk_load=0; SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -48,36 +48,27 @@ test.t2 analyze status OK test.t3 analyze status OK SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N -select count(pk) from t1; -count(pk) -5000000 -select count(a) from t1; -count(a) -5000000 -select count(b) from t1; +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +select count(pk),count(a) from t1; +count(pk) count(a) +2500000 2500000 +select count(b) from t1; count(b) -5000000 -select count(pk) from t2; -count(pk) -5000000 -select count(a) from t2; -count(a) -5000000 -select count(b) from t2; +2500000 +select count(pk),count(a) from t2; +count(pk) count(a) +2500000 2500000 +select count(b) from t2; count(b) -5000000 -select count(pk) from t3; -count(pk) -5000000 -select count(a) from t3; -count(a) -5000000 -select count(b) from t3; +2500000 +select count(pk),count(a) from t3; +count(pk) count(a) +2500000 2500000 +select count(b) from t3; count(b) -5000000 +2500000 longfilenamethatvalidatesthatthiswillgetdeleted.bulk_load.tmp test.bulk_load.tmp disconnect other; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_data.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_data.result index ce6accbe664..12013539017 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_data.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_rev_data.result @@ -38,9 +38,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3; set rocksdb_bulk_load=0; SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -48,36 +48,27 @@ test.t2 analyze status OK test.t3 analyze status OK SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N -select count(pk) from t1; -count(pk) -5000000 -select count(a) from t1; -count(a) -5000000 -select count(b) from t1; +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_bin NULL partitioned 0 N +select count(pk),count(a) from t1; +count(pk) count(a) +2500000 2500000 +select count(b) from t1; count(b) -5000000 -select count(pk) from t2; -count(pk) -5000000 -select count(a) from t2; -count(a) -5000000 -select count(b) from t2; +2500000 +select count(pk),count(a) from t2; +count(pk) count(a) +2500000 2500000 +select count(b) from t2; count(b) -5000000 -select count(pk) from t3; -count(pk) -5000000 -select count(a) from t3; -count(a) -5000000 -select count(b) from t3; +2500000 +select count(pk),count(a) from t3; +count(pk) count(a) +2500000 2500000 +select count(b) from t3; count(b) -5000000 +2500000 longfilenamethatvalidatesthatthiswillgetdeleted.bulk_load.tmp test.bulk_load.tmp disconnect other; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result index 71b42a84338..444f997bf48 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted.result @@ -75,9 +75,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3; set rocksdb_bulk_load=0; SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -85,36 +85,27 @@ test.t2 analyze status OK test.t3 analyze status OK SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N -select count(a) from t1; -count(a) -5000000 -select count(b) from t1; -count(b) -5000000 -select count(a) from t2; -count(a) -5000000 -select count(b) from t2; -count(b) -5000000 -select count(a) from t3; -count(a) -5000000 -select count(b) from t3; -count(b) -5000000 +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N +select count(a),count(b) from t1; +count(a) count(b) +2500000 2500000 +select count(a),count(b) from t2; +count(a) count(b) +2500000 2500000 +select count(a),count(b) from t3; +count(a) count(b) +2500000 2500000 SELECT * FROM t1 FORCE INDEX (PRIMARY) LIMIT 3; a b --4999998 5000000 --4999996 4999998 --4999994 4999996 +-2499998 2500000 +-2499996 2499998 +-2499994 2499996 SELECT * FROM t2 FORCE INDEX (PRIMARY) LIMIT 3; a b -4999999 -4999997 -4999997 -4999995 -4999995 -4999993 +2499999 -2499997 +2499997 -2499995 +2499995 -2499993 disconnect other; DROP TABLE t1, t2, t3; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_rev.result b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_rev.result index 5d224de51a6..dea69b3b089 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_rev.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/bulk_load_unsorted_rev.result @@ -75,9 +75,9 @@ LOAD DATA INFILE <input_file> INTO TABLE t3; set rocksdb_bulk_load=0; SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N ANALYZE TABLE t1, t2, t3; Table Op Msg_type Msg_text test.t1 analyze status OK @@ -85,36 +85,27 @@ test.t2 analyze status OK test.t3 analyze status OK SHOW TABLE STATUS WHERE name LIKE 't%'; Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment Max_index_length Temporary -t1 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t2 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N -t3 ROCKSDB 10 Fixed 5000000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N -select count(a) from t1; -count(a) -5000000 -select count(b) from t1; -count(b) -5000000 -select count(a) from t2; -count(a) -5000000 -select count(b) from t2; -count(b) -5000000 -select count(a) from t3; -count(a) -5000000 -select count(b) from t3; -count(b) -5000000 +t1 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t2 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL 0 N +t3 ROCKSDB 10 Fixed 2500000 # # # # 0 NULL NULL NULL NULL latin1_swedish_ci NULL partitioned 0 N +select count(a),count(b) from t1; +count(a) count(b) +2500000 2500000 +select count(a),count(b) from t2; +count(a) count(b) +2500000 2500000 +select count(a),count(b) from t3; +count(a) count(b) +2500000 2500000 SELECT * FROM t1 FORCE INDEX (PRIMARY) LIMIT 3; a b -4999999 -4999997 -4999997 -4999995 -4999995 -4999993 +2499999 -2499997 +2499997 -2499995 +2499995 -2499993 SELECT * FROM t2 FORCE INDEX (PRIMARY) LIMIT 3; a b --4999998 5000000 --4999996 4999998 --4999994 4999996 +-2499998 2500000 +-2499996 2499998 +-2499994 2499996 disconnect other; DROP TABLE t1, t2, t3; diff --git a/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result b/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result index 98c5ebe9f4c..e9fe026120a 100644 --- a/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result +++ b/storage/rocksdb/mysql-test/rocksdb/r/mariadb_port_fixes.result @@ -89,3 +89,21 @@ CREATE TABLE t1 (i INT) ENGINE=RocksDB; FLUSH TABLE t1 FOR EXPORT; ERROR HY000: Storage engine ROCKSDB of the table `test`.`t1` doesn't have this option DROP TABLE t1; +# +# MDEV-16154 Server crashes in in myrocks::ha_rocksdb::load_auto_incr_value_from_inde +# +CREATE TABLE t1 (a INT) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 AUTO_INCREMENT 10; +DROP TABLE t1; +# +# MDEV-16155: UPDATE on RocksDB table with unique constraint does not work +# +CREATE TABLE t1 (a INT, b CHAR(8), UNIQUE INDEX(a)) ENGINE=RocksDB; +INSERT INTO t1 (a,b) VALUES (1,'foo'),(2,'bar'); +UPDATE t1 SET a=a+100; +SELECT * FROM t1; +a b +101 foo +102 bar +DROP TABLE t1; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/add_index_inplace_sstfilewriter.test b/storage/rocksdb/mysql-test/rocksdb/t/add_index_inplace_sstfilewriter.test index 3977b38d725..5eac8595f7b 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/add_index_inplace_sstfilewriter.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/add_index_inplace_sstfilewriter.test @@ -66,12 +66,15 @@ ALTER TABLE t1 ADD INDEX kb(b), ALGORITHM=INPLACE; --disable_warnings # now do same index using copy algorithm # hitting max row locks (1M) +set @tmp= @@rocksdb_max_row_locks; +set session rocksdb_max_row_locks=1000; --error ER_RDB_STATUS_GENERAL ALTER TABLE t1 ADD INDEX kb_copy(b), ALGORITHM=COPY; set session rocksdb_bulk_load=1; ALTER TABLE t1 ADD INDEX kb_copy(b), ALGORITHM=COPY; set session rocksdb_bulk_load=0; --enable_warnings +set session rocksdb_max_row_locks=@tmp; # checksum testing SELECT COUNT(*) as c FROM diff --git a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test index b8968590155..9d7f0365d07 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/autoinc_vars.test @@ -103,3 +103,16 @@ SHOW CREATE TABLE t1; INSERT INTO t1 VALUES (NULL, 'c'); SELECT * FROM t1; DROP TABLE t1; + +--echo #---------------------------------- +--echo # Issue #792 Crash in autoincrement +--echo #---------------------------------- + +CREATE TABLE t1(C1 DOUBLE AUTO_INCREMENT KEY,C2 CHAR) ENGINE=ROCKSDB; +INSERT INTO t1 VALUES(2177,0); +DROP TABLE t1; + +CREATE TABLE t0(c0 BLOB) ENGINE=ROCKSDB; +INSERT INTO t0 VALUES(0); +ALTER TABLE t0 AUTO_INCREMENT=0; +DROP TABLE t0; diff --git a/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter_load_select.inc b/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter_load_select.inc index 5c122d6bd19..cf8b26847f0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter_load_select.inc +++ b/storage/rocksdb/mysql-test/rocksdb/t/bloomfilter_load_select.inc @@ -5,6 +5,7 @@ insert t1 select (seq+9) div 10, (seq+4) div 5, (seq+4) div 5, seq, seq, 1000, "aaabbbccc" from seq_1_to_10000; insert t2 select * from t1; +set global rocksdb_force_flush_memtable_now=1; # BF conditions (prefix short(4B)|medium(20B)|long(240B)) #0 no eq condition (o, x, x) diff --git a/storage/rocksdb/mysql-test/rocksdb/t/disabled.def b/storage/rocksdb/mysql-test/rocksdb/t/disabled.def index 47005c1baff..3b9726986f0 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/disabled.def +++ b/storage/rocksdb/mysql-test/rocksdb/t/disabled.def @@ -70,7 +70,6 @@ blind_delete_without_tx_api: MDEV-12286: rocksdb.blind_delete_without_tx_api tes unique_check: wrong error number autoinc_vars_thread: debug sync point wait timed out information_schema: MDEV-14372: unstable testcase -bloomfilter: MDEV-14562 ## ## Tests that fail for some other reason diff --git a/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test b/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test index 681e3d2d63f..7282ec237c2 100644 --- a/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test +++ b/storage/rocksdb/mysql-test/rocksdb/t/mariadb_port_fixes.test @@ -83,3 +83,21 @@ CREATE TABLE t1 (i INT) ENGINE=RocksDB; FLUSH TABLE t1 FOR EXPORT; DROP TABLE t1; +--echo # +--echo # MDEV-16154 Server crashes in in myrocks::ha_rocksdb::load_auto_incr_value_from_inde +--echo # +CREATE TABLE t1 (a INT) ENGINE=RocksDB; +INSERT INTO t1 VALUES (1); +ALTER TABLE t1 AUTO_INCREMENT 10; + +DROP TABLE t1; + +--echo # +--echo # MDEV-16155: UPDATE on RocksDB table with unique constraint does not work +--echo # +CREATE TABLE t1 (a INT, b CHAR(8), UNIQUE INDEX(a)) ENGINE=RocksDB; +INSERT INTO t1 (a,b) VALUES (1,'foo'),(2,'bar'); +UPDATE t1 SET a=a+100; +SELECT * FROM t1; +DROP TABLE t1; + diff --git a/tests/mysql_client_fw.c b/tests/mysql_client_fw.c index 73dc443f782..2a529c12c63 100644 --- a/tests/mysql_client_fw.c +++ b/tests/mysql_client_fw.c @@ -768,7 +768,7 @@ static void do_verify_prepare_field(MYSQL_RES *result, { MYSQL_FIELD *field; CHARSET_INFO *cs; - ulonglong expected_field_length; + ulonglong expected_field_length= length; if (!(field= mysql_fetch_field_direct(result, no))) { @@ -777,7 +777,7 @@ static void do_verify_prepare_field(MYSQL_RES *result, } cs= get_charset(field->charsetnr, 0); DIE_UNLESS(cs); - if ((expected_field_length= length * cs->mbmaxlen) > UINT_MAX32) + if ((expected_field_length*= cs->mbmaxlen) > UINT_MAX32) expected_field_length= UINT_MAX32; if (!opt_silent) { |