diff options
Diffstat (limited to 'mysql-test')
37 files changed, 674 insertions, 127 deletions
diff --git a/mysql-test/include/wait_until_connected_again.inc b/mysql-test/include/wait_until_connected_again.inc index 15a1e5bf847..f644ec8645f 100644 --- a/mysql-test/include/wait_until_connected_again.inc +++ b/mysql-test/include/wait_until_connected_again.inc @@ -11,7 +11,7 @@ let $counter= 5000; let $mysql_errno= 9999; while ($mysql_errno) { - --error 0,ER_ACCESS_DENIED_ERROR,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,ER_LOCK_WAIT_TIMEOUT,2002,2006,2013 + --error 0,ER_ACCESS_DENIED_ERROR,ER_SERVER_SHUTDOWN,ER_CONNECTION_KILLED,ER_LOCK_WAIT_TIMEOUT,2002,2006,2013,HA_ERR_NO_ENCRYPTION show status; dec $counter; @@ -30,6 +30,10 @@ while ($mysql_errno) { let $mysql_errno=0; } + if ($mysql_errno == 199) + { + let $mysql_errno=0; + } --sleep 0.1 } --enable_query_log diff --git a/mysql-test/main/explain_innodb.result b/mysql-test/main/explain_innodb.result index 255299cedb9..d49b29d940b 100644 --- a/mysql-test/main/explain_innodb.result +++ b/mysql-test/main/explain_innodb.result @@ -19,20 +19,5 @@ id select_type table type possible_keys key key_len ref rows Extra SET GLOBAL slow_query_log = @sql_tmp; drop table t1; # -# MDEV-31181: Server crash in subselect_uniquesubquery_engine::print -# upon EXPLAIN EXTENDED DELETE -# -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (pk INT PRIMARY KEY); -INSERT INTO t2 VALUES (1),(2); -EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2); -id select_type table type possible_keys key key_len ref rows filtered Extra -1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where -2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index -Warnings: -Note 1003 /* select#1 */ delete from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`)))) -drop table t1, t2; -# # End of 10.4 tests # diff --git a/mysql-test/main/explain_innodb.test b/mysql-test/main/explain_innodb.test index 3dcad4c2d49..e72cae2cf23 100644 --- a/mysql-test/main/explain_innodb.test +++ b/mysql-test/main/explain_innodb.test @@ -19,21 +19,6 @@ SELECT * FROM (SELECT id FROM t1 GROUP BY id) dt WHERE 1=0; SET GLOBAL slow_query_log = @sql_tmp; drop table t1; - ---echo # ---echo # MDEV-31181: Server crash in subselect_uniquesubquery_engine::print ---echo # upon EXPLAIN EXTENDED DELETE ---echo # - -CREATE TABLE t1 (a INT); -INSERT INTO t1 VALUES (1),(2); -CREATE TABLE t2 (pk INT PRIMARY KEY); -INSERT INTO t2 VALUES (1),(2); - -EXPLAIN EXTENDED DELETE FROM t1 WHERE a IN (SELECT pk FROM t2); - -drop table t1, t2; - --echo # --echo # End of 10.4 tests --echo # diff --git a/mysql-test/main/explain_non_select.result b/mysql-test/main/explain_non_select.result index d60f10f85c8..009a568e2c2 100644 --- a/mysql-test/main/explain_non_select.result +++ b/mysql-test/main/explain_non_select.result @@ -277,3 +277,53 @@ EXECUTE stmt; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t2 ALL NULL NULL NULL NULL 2 drop table t1,t2; +# +# MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand +# +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (4); +create table t2 (pk int primary key); +insert into t2 values (3), (5), (1); +create table t3 (a int, key(a)); +insert into t3 values (7), (5), (7), (3); +explain extended delete from t1 where a in (select pk from t2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 5 100.00 Using where +2 DEPENDENT SUBQUERY t2 unique_subquery PRIMARY PRIMARY 4 func 1 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<primary_index_lookup>(<cache>(`test`.`t1`.`a`) in t2 on PRIMARY))) +delete from t1 where a in (select pk from t2); +select * from t1; +a +7 +4 +explain extended delete from t1 where a in (select a from t3); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 2 100.00 Using where +2 DEPENDENT SUBQUERY t3 index_subquery a a 5 func 2 100.00 Using index +Warnings: +Note 1003 /* select#1 */ delete from `test`.`t1` where <in_optimizer>(`test`.`t1`.`a`,<exists>(<index_lookup>(<cache>(`test`.`t1`.`a`) in t3 on a))) +delete from t1 where a in (select a from t3); +select * from t1; +a +4 +drop table t1,t2,t3; +# +# MDEV-31224: EXPLAIN EXTENDED for multi-table update of system table +# +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); +EXPLAIN EXTENDED UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); +id select_type table type possible_keys key key_len ref rows filtered Extra +1 SIMPLE t2 system NULL NULL NULL NULL 1 100.00 +1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where +Warnings: +Note 1003 update `test`.`t1` set `test`.`t2`.`b` = 4 where `test`.`t1`.`a` in (6,2) +UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); +SELECT * from t2; +b +4 +DROP TABLE t1, t2; +# End of 10.4 tests diff --git a/mysql-test/main/explain_non_select.test b/mysql-test/main/explain_non_select.test index d9ff0fb7245..e861955b3f1 100644 --- a/mysql-test/main/explain_non_select.test +++ b/mysql-test/main/explain_non_select.test @@ -250,3 +250,50 @@ PREPARE stmt FROM 'EXPLAIN INSERT INTO t1 SELECT * FROM t2'; EXECUTE stmt; drop table t1,t2; +--echo # +--echo # MDEV-31181: EXPLAIN EXTENDED for single-table DELETE with IN predicand +--echo # + +create table t1 (a int); +insert into t1 values (3), (7), (1), (3), (4); +create table t2 (pk int primary key); +insert into t2 values (3), (5), (1); +create table t3 (a int, key(a)); +insert into t3 values (7), (5), (7), (3); + +let $q1= +delete from t1 where a in (select pk from t2); + +eval explain extended $q1; +eval $q1; +select * from t1; + +let $q2= +delete from t1 where a in (select a from t3); + +eval explain extended $q2; +eval $q2; +select * from t1; + +drop table t1,t2,t3; + +--echo # +--echo # MDEV-31224: EXPLAIN EXTENDED for multi-table update of system table +--echo # + +CREATE TABLE t1 (a INT); +INSERT INTO t1 VALUES (1),(2); + +CREATE TABLE t2 (b INT) ENGINE=MyISAM; +INSERT INTO t2 VALUES (3); + +let $q= +UPDATE t1, t2 SET b = 4 WHERE a IN (6,2); + +eval EXPLAIN EXTENDED $q; +eval $q; +SELECT * from t2; + +DROP TABLE t1, t2; + +--echo # End of 10.4 tests diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index fd9b5be4260..4c2693e5e4c 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4025,3 +4025,116 @@ drop table t1; # # End of 10.1 tests # +# +# MDEV-6768 Wrong result with agregate with join with no resultset +# +create table t1 +( +PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, +PARENT_FIELD VARCHAR(10), +PRIMARY KEY (PARENT_ID) +) engine=innodb; +create table t2 +( +CHILD_ID INT NOT NULL AUTO_INCREMENT, +PARENT_ID INT NOT NULL, +CHILD_FIELD varchar(10), +PRIMARY KEY (CHILD_ID) +)engine=innodb; +INSERT INTO t1 (PARENT_FIELD) +SELECT 'AAAA'; +INSERT INTO t2 (PARENT_ID, CHILD_FIELD) +SELECT 1, 'BBBB'; +explain select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 const PRIMARY PRIMARY 4 const 1 Using index +1 SIMPLE t2 ALL NULL NULL NULL NULL 1 Using where +select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +PARENT_ID min(CHILD_FIELD) +NULL NULL +select +1, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +1 min(CHILD_FIELD) +1 NULL +select +IFNULL(t1.PARENT_ID,1), +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +IFNULL(t1.PARENT_ID,1) min(CHILD_FIELD) +1 NULL +# Check that things works with MyISAM (which has different explain) +alter table t1 engine=myisam; +alter table t2 engine=myisam; +explain select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE NULL NULL NULL NULL NULL NULL NULL Impossible WHERE noticed after reading const tables +select +t1.PARENT_ID, +min(CHILD_FIELD) +from t1 straight_join t2 +where t1.PARENT_ID = 1 +and t1.PARENT_ID = t2.PARENT_ID +and t2.CHILD_FIELD = "ZZZZ"; +PARENT_ID min(CHILD_FIELD) +NULL NULL +drop table t1,t2; +# Check that things works if sub queries are re-executed +create table t1 (a int primary key, b int); +create table t2 (a int primary key, b int); +create table t3 (a int primary key, b int); +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,1),(2,2),(3,3); +insert into t3 values (1,1),(3,3); +explain +select *, +(select +CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', +'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) +from t2,t3 +where t2.a=1 and t1.b = t3.a) as s1 +from t1; +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 ALL NULL NULL NULL NULL 3 +2 DEPENDENT SUBQUERY t2 const PRIMARY PRIMARY 4 const 1 Using index +2 DEPENDENT SUBQUERY t3 eq_ref PRIMARY PRIMARY 4 test.t1.b 1 +select *, +(select +CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', +'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) +from t2,t3 +where t2.a=1 and t1.b = t3.a) as s1 +from t1; +a b s1 +1 1 t2:1;min_t3_b:1 +2 2 t2:t2a-null;min_t3_b:t3b-null +3 3 t2:1;min_t3_b:3 +drop table t1,t2,t3; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 506323599cb..3c5c1b5bb9b 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -5,6 +5,7 @@ --source include/no_valgrind_without_big.inc --source include/default_optimizer_switch.inc +--source include/have_innodb.inc # # TODO: @@ -1688,3 +1689,117 @@ drop table t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # MDEV-6768 Wrong result with agregate with join with no resultset +--echo # + +create table t1 +( + PARENT_ID int(10) unsigned NOT NULL AUTO_INCREMENT, + PARENT_FIELD VARCHAR(10), + PRIMARY KEY (PARENT_ID) +) engine=innodb; + +create table t2 +( + CHILD_ID INT NOT NULL AUTO_INCREMENT, + PARENT_ID INT NOT NULL, + CHILD_FIELD varchar(10), + PRIMARY KEY (CHILD_ID) +)engine=innodb; + +INSERT INTO t1 (PARENT_FIELD) +SELECT 'AAAA'; + +INSERT INTO t2 (PARENT_ID, CHILD_FIELD) +SELECT 1, 'BBBB'; + +explain select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + 1, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + IFNULL(t1.PARENT_ID,1), + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + + +--echo # Check that things works with MyISAM (which has different explain) + +alter table t1 engine=myisam; +alter table t2 engine=myisam; + +explain select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +select + t1.PARENT_ID, + min(CHILD_FIELD) + from t1 straight_join t2 + where t1.PARENT_ID = 1 + and t1.PARENT_ID = t2.PARENT_ID + and t2.CHILD_FIELD = "ZZZZ"; + +drop table t1,t2; + +--echo # Check that things works if sub queries are re-executed + +create table t1 (a int primary key, b int); +create table t2 (a int primary key, b int); +create table t3 (a int primary key, b int); + +insert into t1 values (1,1),(2,2),(3,3); +insert into t2 values (1,1),(2,2),(3,3); +insert into t3 values (1,1),(3,3); + +explain +select *, + (select + CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', + 'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) + from t2,t3 + where t2.a=1 and t1.b = t3.a) as s1 +from t1; + +select *, + (select + CONCAT('t2:', IFNULL(t2.a, 't2a-null'), ';', + 'min_t3_b:', IFNULL(min(t3.b), 't3b-null')) + from t2,t3 + where t2.a=1 and t1.b = t3.a) as s1 +from t1; + +drop table t1,t2,t3; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/join_cache.result b/mysql-test/main/join_cache.result index 1837576e719..6b39f936628 100644 --- a/mysql-test/main/join_cache.result +++ b/mysql-test/main/join_cache.result @@ -3781,9 +3781,9 @@ id1 num3 text1 id4 id3 dummy 228808822 6 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 18 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 1 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 -228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 17 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 50 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 +228808822 3 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 4 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 826928662 935693782 0 228808822 89 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 228808822 19 CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC 2381969632 2482416112 0 @@ -5655,13 +5655,13 @@ EXPLAIN SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 -1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using where -1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where +1 SIMPLE t2 ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join) +1 SIMPLE t3 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (incremental, BNL join) SELECT * FROM t1, t2 LEFT JOIN t3 ON t2.b=t3.b WHERE t1.a=t2.a; a a b b c +3 3 32 32 302 3 3 30 30 300 3 3 31 NULL NULL -3 3 32 32 302 set join_buffer_space_limit=@save_join_buffer_space_limit; set join_buffer_size=@save_join_buffer_size; set join_cache_level=@save_join_cache_level; diff --git a/mysql-test/main/join_cache_notasan.result b/mysql-test/main/join_cache_notasan.result new file mode 100644 index 00000000000..3cec949f5c6 --- /dev/null +++ b/mysql-test/main/join_cache_notasan.result @@ -0,0 +1,27 @@ +# +# MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +# +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +SET SESSION join_buffer_size= X; +Warnings: +Warning X Truncated incorrect join_buffer_size value: 'X' +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i +SET optimizer_switch='optimize_join_buffer_size=off'; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +ERROR HYX: Could not create a join buffer. Please check and adjust the value of the variables 'JOIN_BUFFER_SIZE (X)' and 'JOIN_BUFFER_SPACE_LIMIT (X)' +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +i i +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; +# +# End of 10.4 tests +# diff --git a/mysql-test/main/join_cache_notasan.test b/mysql-test/main/join_cache_notasan.test new file mode 100644 index 00000000000..7fd5e4e80b1 --- /dev/null +++ b/mysql-test/main/join_cache_notasan.test @@ -0,0 +1,35 @@ +# +# Tests that should be in join_cache but cannot be run with ASAN + +--source include/not_asan.inc +--source include/have_innodb.inc + +--echo # +--echo # MDEV-28217 Incorrect Join Execution When Controlling Join Buffer Size +--echo # + +# This test tries to allocate a too big bufffer, for which ASAN gives an error + +CREATE TABLE t1 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t1 VALUES (1332945389); +CREATE TABLE t2 (i int PRIMARY KEY)engine=innodb; +INSERT INTO t2 VALUES (1180244875), (1951338178); +--replace_regex /[0-9][0-9]+/X/ +SET SESSION join_buffer_size= 5250229460064350213; +SET SESSION join_cache_level = 4; +SET optimizer_switch='optimize_join_buffer_size=on'; +SELECT t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET optimizer_switch='optimize_join_buffer_size=off'; +--replace_regex /[0-9][0-9]+/X/ +--error ER_OUTOFMEMORY +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION join_buffer_size= 10000000; +SELECT t1.i,t2.i FROM t2 LEFT JOIN t1 ON t1.i = t2.i WHERE t1.i; +SET SESSION optimizer_switch= default; +SET SESSION join_buffer_size= default; +SET SESSION join_cache_level= default; +drop table t1,t2; + +--echo # +--echo # End of 10.4 tests +--echo # diff --git a/mysql-test/main/join_optimizer.test b/mysql-test/main/join_optimizer.test index 3afe82113b9..e5f6181944d 100644 --- a/mysql-test/main/join_optimizer.test +++ b/mysql-test/main/join_optimizer.test @@ -2,6 +2,8 @@ drop table if exists t0,t1,t2,t3; --enable_warnings +--source include/have_innodb.inc + --echo # --echo # BUG#38049 incorrect rows estimations with references from preceding table --echo # diff --git a/mysql-test/main/lowercase_table2.opt b/mysql-test/main/lowercase_table2.opt new file mode 100644 index 00000000000..ac4d3211e89 --- /dev/null +++ b/mysql-test/main/lowercase_table2.opt @@ -0,0 +1 @@ +--lower-case-table-names=2 diff --git a/mysql-test/main/lowercase_table2.result b/mysql-test/main/lowercase_table2.result index 9194638a4d2..fe06fb671a2 100644 --- a/mysql-test/main/lowercase_table2.result +++ b/mysql-test/main/lowercase_table2.result @@ -14,7 +14,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) @@ -70,7 +70,7 @@ SHOW CREATE TABLE T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=InnoDB DEFAULT CHARSET=latin1 +) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci RENAME TABLE T1 TO T2; SHOW TABLES LIKE "T2"; Tables_in_test (T2) @@ -319,18 +319,42 @@ Database (mysql_t%) mysql_TEST show create database mysql_test; Database Create Database -mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysql_test CREATE DATABASE `mysql_test` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ show create database mysql_TEST; Database Create Database -mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 */ +mysql_TEST CREATE DATABASE `mysql_TEST` /*!40100 DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci */ show create table mysql_TEST.T1; Table Create Table T1 CREATE TABLE `T1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci show create table mysql_test.t1; Table Create Table t1 CREATE TABLE `t1` ( `a` int(11) DEFAULT NULL -) ENGINE=MyISAM DEFAULT CHARSET=latin1 +) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci drop database mysql_TEST; +# MDEV-30765 SHOW TABLES not working properly with +# lower_case_table_names=2 +# +create database db1; +use db1; +# lowercase table name +create table `a` (a int); +# uppercase table name +create table `B` (a int); +create user 'mysqltest_1'@'localhost' identified by 'password'; +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; +connect conn1, localhost, mysqltest_1, password, test; +connection conn1; +use db1; +show tables; +Tables_in_db1 +B +a +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1; diff --git a/mysql-test/main/lowercase_table2.test b/mysql-test/main/lowercase_table2.test index 601089ca760..82c07bf9345 100644 --- a/mysql-test/main/lowercase_table2.test +++ b/mysql-test/main/lowercase_table2.test @@ -288,3 +288,29 @@ show create database mysql_TEST; show create table mysql_TEST.T1; show create table mysql_test.t1; drop database mysql_TEST; + +--echo # MDEV-30765 SHOW TABLES not working properly with +--echo # lower_case_table_names=2 +--echo # +create database db1; +use db1; +--echo # lowercase table name +create table `a` (a int); +--echo # uppercase table name +create table `B` (a int); + +create user 'mysqltest_1'@'localhost' identified by 'password'; + +grant select, show view on db1.`a` to 'mysqltest_1'@'localhost'; +grant select, show view on db1.`B` to 'mysqltest_1'@'localhost'; + +connect (conn1, localhost, mysqltest_1, password, test); +connection conn1; +use db1; +show tables; + +connection default; +disconnect conn1; +drop user 'mysqltest_1'@'localhost'; +drop tables a, B; +drop database db1;
\ No newline at end of file diff --git a/mysql-test/main/myisam_explain_non_select_all.result b/mysql-test/main/myisam_explain_non_select_all.result index 3ca9629d027..b515cb4fd83 100644 --- a/mysql-test/main/myisam_explain_non_select_all.result +++ b/mysql-test/main/myisam_explain_non_select_all.result @@ -2689,7 +2689,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Warnings: -Note 1003 update `test`.`t1` set NULL = 10 +Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 @@ -2734,7 +2734,7 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE t2 system NULL NULL NULL NULL 0 0.00 Const row not found 1 SIMPLE t1 ALL NULL NULL NULL NULL 2 100.00 Using where Warnings: -Note 1003 update `test`.`t1` set NULL = 10 where `test`.`t1`.`c3` = 10 +Note 1003 update `test`.`t1` set `test`.`t2`.`c2` = 10 where `test`.`t1`.`c3` = 10 # Status of EXPLAIN EXTENDED query Variable_name Value Handler_read_key 7 diff --git a/mysql-test/main/type_timestamp.result b/mysql-test/main/type_timestamp.result index 58cb12ae267..dbccee08ada 100644 --- a/mysql-test/main/type_timestamp.result +++ b/mysql-test/main/type_timestamp.result @@ -1230,6 +1230,8 @@ SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); c1 Warnings: Warning 1292 Truncated incorrect datetime value: 'r' +SELECT * FROM t1 HAVING MIN(t1.c1) > 0; +c1 DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); INSERT INTO t1 VALUES ('2010-01-01 00:00:00'); diff --git a/mysql-test/main/type_timestamp.test b/mysql-test/main/type_timestamp.test index f12cc2a4bc3..c8517656071 100644 --- a/mysql-test/main/type_timestamp.test +++ b/mysql-test/main/type_timestamp.test @@ -810,6 +810,7 @@ DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); SELECT MIN(t1.c1) AS k1 FROM t1 HAVING (k1 >= ALL(SELECT 'a' UNION SELECT 'r')); SELECT * FROM t1 HAVING MIN(t1.c1) >= ALL(SELECT 'a' UNION SELECT 'r'); +SELECT * FROM t1 HAVING MIN(t1.c1) > 0; DROP TABLE t1; CREATE TABLE t1 (c1 timestamp); diff --git a/mysql-test/suite/galera/r/galera_log_bin.result b/mysql-test/suite/galera/r/galera_log_bin.result index df7ace9d95a..31f953bfd36 100644 --- a/mysql-test/suite/galera/r/galera_log_bin.result +++ b/mysql-test/suite/galera/r/galera_log_bin.result @@ -1,10 +1,10 @@ connection node_2; connection node_1; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/r/galera_log_bin_ext.result b/mysql-test/suite/galera/r/galera_log_bin_ext.result index b110cb4dba3..9d7ea473241 100644 --- a/mysql-test/suite/galera/r/galera_log_bin_ext.result +++ b/mysql-test/suite/galera/r/galera_log_bin_ext.result @@ -2,11 +2,11 @@ connection node_2; connection node_1; connection node_1; connection node_2; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result b/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result index b110cb4dba3..9d7ea473241 100644 --- a/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result +++ b/mysql-test/suite/galera/r/galera_log_bin_ext_mariabackup.result @@ -2,11 +2,11 @@ connection node_2; connection node_1; connection node_1; connection node_2; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/r/galera_log_bin_opt.result b/mysql-test/suite/galera/r/galera_log_bin_opt.result index df7ace9d95a..31f953bfd36 100644 --- a/mysql-test/suite/galera/r/galera_log_bin_opt.result +++ b/mysql-test/suite/galera/r/galera_log_bin_opt.result @@ -1,10 +1,10 @@ connection node_2; connection node_1; -connection node_1; +connection node_2; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; -connection node_2; +connection node_1; set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/t/galera_log_bin.inc b/mysql-test/suite/galera/t/galera_log_bin.inc index 4c245846752..3cac28047d4 100644 --- a/mysql-test/suite/galera/t/galera_log_bin.inc +++ b/mysql-test/suite/galera/t/galera_log_bin.inc @@ -1,11 +1,11 @@ --source include/galera_cluster.inc --source include/force_restart.inc ---connection node_1 +--connection node_2 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; ---connection node_2 +--connection node_1 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/galera/t/galera_log_bin_sst.inc b/mysql-test/suite/galera/t/galera_log_bin_sst.inc index 3d20add6d9e..87420ad539f 100644 --- a/mysql-test/suite/galera/t/galera_log_bin_sst.inc +++ b/mysql-test/suite/galera/t/galera_log_bin_sst.inc @@ -6,11 +6,11 @@ --let $node_2=node_2 --source include/auto_increment_offset_save.inc ---connection node_1 +--connection node_2 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; ---connection node_2 +--connection node_1 set global wsrep_on=OFF; reset master; set global wsrep_on=ON; diff --git a/mysql-test/suite/maria/encrypt-no-key.result b/mysql-test/suite/maria/encrypt-no-key.result index 6745670dfac..32a280d8cbc 100644 --- a/mysql-test/suite/maria/encrypt-no-key.result +++ b/mysql-test/suite/maria/encrypt-no-key.result @@ -1,15 +1,27 @@ -call mtr.add_suppression('Unknown key id 1. Can''t continue'); +call mtr.add_suppression("Initialization of encryption failed.*"); set global aria_encrypt_tables= 1; create table t1 (pk int primary key, a int, key(a)) engine=aria transactional=1; -alter table t1 disable keys; -insert into t1 values (1,1); -alter table t1 enable keys; -ERROR HY000: Unknown key id 1. Can't continue! -repair table t1 use_frm; -Table Op Msg_type Msg_text -test.t1 repair warning Number of rows changed from 0 to 1 -test.t1 repair Error Unknown key id 1. Can't continue! -test.t1 repair Error Unknown key id 1. Can't continue! -test.t1 repair status OK -drop table t1; +ERROR HY000: Initialization of encryption failed for ./test/t1 set global aria_encrypt_tables= default; +# +# MDEV-26258 Various crashes/asserts/corruptions when Aria encryption is +# enabled/used, but the encryption plugin is not loaded +# +SET GLOBAL aria_encrypt_tables=ON; +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; +ERROR HY000: Initialization of encryption failed for ./test/t1 +# Restart with encryption enabled +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; +INSERT INTO t1 VALUES (4,0); +LOAD INDEX INTO CACHE t1 IGNORE LEAVES; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +LOAD INDEX INTO CACHE t1; +Table Op Msg_type Msg_text +test.t1 preload_keys status OK +SELECT * FROM t1; +ERROR HY000: Initialization of encryption failed for ./test/t1.MAD +DROP TABLE t1; +Warnings: +Warning 199 Initialization of encryption failed for ./test/t1.MAD +Cleanup diff --git a/mysql-test/suite/maria/encrypt-no-key.test b/mysql-test/suite/maria/encrypt-no-key.test index 2d586c50695..89c50d79a54 100644 --- a/mysql-test/suite/maria/encrypt-no-key.test +++ b/mysql-test/suite/maria/encrypt-no-key.test @@ -1,14 +1,56 @@ +--source include/not_embedded.inc # # MDEV-18496 Crash when Aria encryption is enabled but plugin not available # -call mtr.add_suppression('Unknown key id 1. Can''t continue'); +call mtr.add_suppression("Initialization of encryption failed.*"); set global aria_encrypt_tables= 1; +--error HA_ERR_NO_ENCRYPTION create table t1 (pk int primary key, a int, key(a)) engine=aria transactional=1; -alter table t1 disable keys; -insert into t1 values (1,1); -error 192; -alter table t1 enable keys; -repair table t1 use_frm; -drop table t1; set global aria_encrypt_tables= default; + +--echo # +--echo # MDEV-26258 Various crashes/asserts/corruptions when Aria encryption is +--echo # enabled/used, but the encryption plugin is not loaded +--echo # + +SET GLOBAL aria_encrypt_tables=ON; + +--write_file $MYSQLTEST_VARDIR/keys1.txt +1;770A8A65DA156D24EE2A093277530142 +EOF + +--replace_result \\ / +--error HA_ERR_NO_ENCRYPTION +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; + +--echo # Restart with encryption enabled + +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--exec echo "restart:--aria-encrypt-tables=1 --plugin-load-add=file_key_management --file-key-management --file-key-management-filename=$MYSQLTEST_VARDIR/keys1.txt" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +CREATE TABLE t1 (a INT KEY,b INT,KEY(b)) ENGINE=Aria; +INSERT INTO t1 VALUES (4,0); +LOAD INDEX INTO CACHE t1 IGNORE LEAVES; +LOAD INDEX INTO CACHE t1; + +# Restart without encryption. Above table should be unreadable + +--exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--shutdown_server +--source include/wait_until_disconnected.inc +--exec echo "restart:--aria-encrypt-tables=0" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect +--enable_reconnect +--source include/wait_until_connected_again.inc + +--replace_result \\ / +--error HA_ERR_NO_ENCRYPTION +SELECT * FROM t1; +DROP TABLE t1; + +--echo Cleanup +--remove_file $MYSQLTEST_VARDIR/keys1.txt diff --git a/mysql-test/suite/maria/encrypt-wrong-key.result b/mysql-test/suite/maria/encrypt-wrong-key.result index bc22481296d..4ff057957da 100644 --- a/mysql-test/suite/maria/encrypt-wrong-key.result +++ b/mysql-test/suite/maria/encrypt-wrong-key.result @@ -1,16 +1,16 @@ call mtr.add_suppression("file_key_management"); call mtr.add_suppression("System key id 1 is missing"); call mtr.add_suppression("Unknown key id 1"); -call mtr.add_suppression("Failed to decrypt"); +call mtr.add_suppression("Initialization of encryption failed.*"); CREATE TABLE t1 (i INT, KEY(i)) ENGINE=Aria; INSERT INTO t1 VALUES (1); repair table t1; Table Op Msg_type Msg_text -test.t1 repair info Wrong CRC on datapage at 1 -test.t1 repair warning Number of rows changed from 1 to 0 -test.t1 repair status OK +test.t1 repair Error Initialization of encryption failed for ./test/t1.MAD +test.t1 repair error Corrupt INSERT INTO t1 VALUES (2); +ERROR HY000: Initialization of encryption failed for ./test/t1.MAD select * from t1; -ERROR HY000: failed to decrypt './test/t1' rc: -1 dstlen: 0 size: 8172 - +i +1 drop table t1; diff --git a/mysql-test/suite/maria/encrypt-wrong-key.test b/mysql-test/suite/maria/encrypt-wrong-key.test index ca65e1018d0..ac060c4e9dd 100644 --- a/mysql-test/suite/maria/encrypt-wrong-key.test +++ b/mysql-test/suite/maria/encrypt-wrong-key.test @@ -7,7 +7,7 @@ call mtr.add_suppression("file_key_management"); call mtr.add_suppression("System key id 1 is missing"); call mtr.add_suppression("Unknown key id 1"); -call mtr.add_suppression("Failed to decrypt"); +call mtr.add_suppression("Initialization of encryption failed.*"); --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect --shutdown_server @@ -36,8 +36,11 @@ EOF --enable_reconnect --source include/wait_until_connected_again.inc +--replace_result \\ / repair table t1; +--replace_result \\ / +--error HA_ERR_NO_ENCRYPTION INSERT INTO t1 VALUES (2); --exec echo "wait" > $MYSQLTEST_VARDIR/tmp/mysqld.1.expect @@ -48,8 +51,6 @@ INSERT INTO t1 VALUES (2); --enable_reconnect --source include/wait_until_connected_again.inc ---replace_result \\ / ---error 192 select * from t1; drop table t1; --remove_file $MYSQLTEST_VARDIR/keys1.txt diff --git a/mysql-test/suite/maria/maria.result b/mysql-test/suite/maria/maria.result index 925c5121bfb..f8911bdde2b 100644 --- a/mysql-test/suite/maria/maria.result +++ b/mysql-test/suite/maria/maria.result @@ -2814,7 +2814,7 @@ DROP TABLE t1; # cardinalities=1 # SET aria_repair_threads=2; -SET aria_sort_buffer_size=8192; +SET aria_sort_buffer_size=16384; CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); Warnings: Note 1831 Duplicate index `a_2`. This is deprecated and will be disallowed in a future release @@ -2839,19 +2839,8 @@ SET aria_repair_threads=@@global.aria_repair_threads; # low myisam_sort_buffer_size # CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); -INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), -(6,'0'),(7,'0'); -INSERT INTO t1 SELECT a+10,b FROM t1; -INSERT INTO t1 SELECT a+20,b FROM t1; -INSERT INTO t1 SELECT a+40,b FROM t1; -INSERT INTO t1 SELECT a+80,b FROM t1; -INSERT INTO t1 SELECT a+160,b FROM t1; -INSERT INTO t1 SELECT a+320,b FROM t1; -INSERT INTO t1 SELECT a+640,b FROM t1; -INSERT INTO t1 SELECT a+1280,b FROM t1; -INSERT INTO t1 SELECT a+2560,b FROM t1; -INSERT INTO t1 SELECT a+5120,b FROM t1; -SET aria_sort_buffer_size=4096; +INSERT INTO t1 select seq,'0' from seq_1_to_65536; +SET aria_sort_buffer_size=16384; REPAIR TABLE t1; Table Op Msg_type Msg_text test.t1 repair error aria_sort_buffer_size is too small. X diff --git a/mysql-test/suite/maria/maria.test b/mysql-test/suite/maria/maria.test index a326bb12d2a..f7a5b5c35b2 100644 --- a/mysql-test/suite/maria/maria.test +++ b/mysql-test/suite/maria/maria.test @@ -5,6 +5,7 @@ -- source include/have_maria.inc -- source include/have_partition.inc +-- source include/have_sequence.inc call mtr.add_suppression("Can't find record in '.*'"); @@ -2034,7 +2035,7 @@ DROP TABLE t1; --echo # cardinalities=1 --echo # SET aria_repair_threads=2; -SET aria_sort_buffer_size=8192; +SET aria_sort_buffer_size=16384; CREATE TABLE t1(a CHAR(255), KEY(a), KEY(a), KEY(a)); INSERT INTO t1 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9),(0),(1),(2),(3); --replace_regex /Current aria_sort_buffer_size.*/X/ @@ -2050,19 +2051,8 @@ SET aria_repair_threads=@@global.aria_repair_threads; --echo # low myisam_sort_buffer_size --echo # CREATE TABLE t1(a INT, b CHAR(10), KEY(a), KEY(b)); -INSERT INTO t1 VALUES(1,'0'),(2,'0'),(3,'0'),(4,'0'),(5,'0'), - (6,'0'),(7,'0'); -INSERT INTO t1 SELECT a+10,b FROM t1; -INSERT INTO t1 SELECT a+20,b FROM t1; -INSERT INTO t1 SELECT a+40,b FROM t1; -INSERT INTO t1 SELECT a+80,b FROM t1; -INSERT INTO t1 SELECT a+160,b FROM t1; -INSERT INTO t1 SELECT a+320,b FROM t1; -INSERT INTO t1 SELECT a+640,b FROM t1; -INSERT INTO t1 SELECT a+1280,b FROM t1; -INSERT INTO t1 SELECT a+2560,b FROM t1; -INSERT INTO t1 SELECT a+5120,b FROM t1; -SET aria_sort_buffer_size=4096; +INSERT INTO t1 select seq,'0' from seq_1_to_65536; +SET aria_sort_buffer_size=16384; --replace_regex /Current aria_sort_buffer_size.*/X/ REPAIR TABLE t1; CHECK TABLE t1; diff --git a/mysql-test/suite/maria/repair-big-sort.result b/mysql-test/suite/maria/repair-big-sort.result new file mode 100644 index 00000000000..d1dfe3f7c2f --- /dev/null +++ b/mysql-test/suite/maria/repair-big-sort.result @@ -0,0 +1,81 @@ +SET sql_mode=''; +CREATE TEMPORARY TABLE t1 (a tinyINT,b CHAR(1)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,1),(3,3),(2,2); +SET SESSION tmp_table_size=True; +Warnings: +Warning 1292 Truncated incorrect tmp_table_size value: '1' +CREATE TABLE t2 (c INT, d DATE) ENGINE=InnoDB PARTITION BY RANGE (YEAR (d)) SUBPARTITION BY HASH (TO_DAYS (d)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION s4, SUBPARTITION s5)); +SET SESSION aria_sort_buffer_size=CAST(-1 AS UNSIGNED INT); +Warnings: +Note 1105 Cast to unsigned converted negative integer to it's positive complement +Note 1105 Cast to unsigned converted negative integer to it's positive complement +Warning 1292 Truncated incorrect aria_sort_buffer_size value: '18446744073709551615' +INSERT INTO t1 SELECT '', SEQ FROM seq_1_to_258; +Warnings: +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 1 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 2 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 3 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 4 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 5 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 6 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 7 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 8 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 9 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 10 +Warning 1265 Data truncated for column 'b' at row 10 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 11 +Warning 1265 Data truncated for column 'b' at row 11 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 12 +Warning 1265 Data truncated for column 'b' at row 12 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 13 +Warning 1265 Data truncated for column 'b' at row 13 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 14 +Warning 1265 Data truncated for column 'b' at row 14 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 15 +Warning 1265 Data truncated for column 'b' at row 15 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 16 +Warning 1265 Data truncated for column 'b' at row 16 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 17 +Warning 1265 Data truncated for column 'b' at row 17 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 18 +Warning 1265 Data truncated for column 'b' at row 18 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 19 +Warning 1265 Data truncated for column 'b' at row 19 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 20 +Warning 1265 Data truncated for column 'b' at row 20 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 21 +Warning 1265 Data truncated for column 'b' at row 21 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 22 +Warning 1265 Data truncated for column 'b' at row 22 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 23 +Warning 1265 Data truncated for column 'b' at row 23 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 24 +Warning 1265 Data truncated for column 'b' at row 24 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 25 +Warning 1265 Data truncated for column 'b' at row 25 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 26 +Warning 1265 Data truncated for column 'b' at row 26 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 27 +Warning 1265 Data truncated for column 'b' at row 27 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 28 +Warning 1265 Data truncated for column 'b' at row 28 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 29 +Warning 1265 Data truncated for column 'b' at row 29 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 30 +Warning 1265 Data truncated for column 'b' at row 30 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 31 +Warning 1265 Data truncated for column 'b' at row 31 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 32 +Warning 1265 Data truncated for column 'b' at row 32 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 33 +Warning 1265 Data truncated for column 'b' at row 33 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 34 +Warning 1265 Data truncated for column 'b' at row 34 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 35 +Warning 1265 Data truncated for column 'b' at row 35 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 36 +Warning 1265 Data truncated for column 'b' at row 36 +Warning 1366 Incorrect integer value: '' for column `test`.`t1`.`a` at row 37 +SET SESSION aria_repair_threads=4; +UPDATE t1 SET a=( (SELECT MAX(a) FROM t1)); +drop table t1,t2; diff --git a/mysql-test/suite/maria/repair-big-sort.test b/mysql-test/suite/maria/repair-big-sort.test new file mode 100644 index 00000000000..f2f4f84c29b --- /dev/null +++ b/mysql-test/suite/maria/repair-big-sort.test @@ -0,0 +1,15 @@ + +--source include/have_innodb.inc +--source include/have_partition.inc +--source include/have_sequence.inc + +SET sql_mode=''; +CREATE TEMPORARY TABLE t1 (a tinyINT,b CHAR(1)) ENGINE=InnoDB ROW_FORMAT=REDUNDANT; +INSERT INTO t1 VALUES (1,1),(3,3),(2,2); +SET SESSION tmp_table_size=True; +CREATE TABLE t2 (c INT, d DATE) ENGINE=InnoDB PARTITION BY RANGE (YEAR (d)) SUBPARTITION BY HASH (TO_DAYS (d)) (PARTITION p0 VALUES LESS THAN (1990) (SUBPARTITION s0, SUBPARTITION s1), PARTITION p1 VALUES LESS THAN MAXVALUE (SUBPARTITION s4, SUBPARTITION s5)); +SET SESSION aria_sort_buffer_size=CAST(-1 AS UNSIGNED INT); +INSERT INTO t1 SELECT '', SEQ FROM seq_1_to_258; +SET SESSION aria_repair_threads=4; +UPDATE t1 SET a=( (SELECT MAX(a) FROM t1)); +drop table t1,t2; diff --git a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result b/mysql-test/suite/rpl/r/rpl_loaddata_local.result index f0d24df2cb2..f0d24df2cb2 100644 --- a/mysql-test/suite/rpl/r/rpl_loaddatalocal.result +++ b/mysql-test/suite/rpl/r/rpl_loaddata_local.result diff --git a/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result b/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result index 56522566ec9..6da4c9bfe97 100644 --- a/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result +++ b/mysql-test/suite/sys_vars/r/aria_sort_buffer_size_basic.result @@ -22,13 +22,13 @@ Warnings: Warning 1292 Truncated incorrect aria_sort_buffer_size value: '10' select @@global.aria_sort_buffer_size; @@global.aria_sort_buffer_size -4096 +16376 set session aria_sort_buffer_size=10; Warnings: Warning 1292 Truncated incorrect aria_sort_buffer_size value: '10' select @@session.aria_sort_buffer_size; @@session.aria_sort_buffer_size -4096 +16376 set global aria_sort_buffer_size=1.1; ERROR 42000: Incorrect argument type to variable 'aria_sort_buffer_size' set session aria_sort_buffer_size=1e1; @@ -40,9 +40,9 @@ Warnings: Warning 1292 Truncated incorrect aria_sort_buffer_size value: '0' select @@global.aria_sort_buffer_size; @@global.aria_sort_buffer_size -4096 +16376 set session aria_sort_buffer_size=cast(-1 as unsigned int); select @@session.aria_sort_buffer_size; @@session.aria_sort_buffer_size -18446744073709551615 +1152921504606846975 SET @@global.aria_sort_buffer_size = @start_global_value; diff --git a/mysql-test/suite/sys_vars/r/sysvars_aria.result b/mysql-test/suite/sys_vars/r/sysvars_aria.result index 9f5585668b6..3e9130db9db 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_aria.result +++ b/mysql-test/suite/sys_vars/r/sysvars_aria.result @@ -223,8 +223,8 @@ DEFAULT_VALUE 268434432 VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. -NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MIN_VALUE 16376 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff index e0aad7fa6d0..6fa25546ad1 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded,32bit.rdiff @@ -97,9 +97,9 @@ @@ -207,7 +207,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. - NUMERIC_MIN_VALUE 4096 + NUMERIC_MIN_VALUE 16376 -NUMERIC_MAX_VALUE 18446744073709551615 -+NUMERIC_MAX_VALUE 4294967295 ++NUMERIC_MAX_VALUE 268435455 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -684,8 +684,8 @@ VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE NUMERIC_MIN_VALUE 4096 --NUMERIC_MAX_VALUE 18446744073709551615 -+NUMERIC_MAX_VALUE 4294967295 +-NUMERIC_MAX_VALUE 1152921504606846975 ++NUMERIC_MAX_VALUE 268435455 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result index 1ed5b5ce3bd..c03181d0c63 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_embedded.result @@ -216,8 +216,8 @@ VARIABLE_NAME ARIA_SORT_BUFFER_SIZE VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. -NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MIN_VALUE 16376 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -2127,7 +2127,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO diff --git a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result index 125d629ac87..48b481b200c 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result +++ b/mysql-test/suite/sys_vars/r/sysvars_server_notembedded.result @@ -216,8 +216,8 @@ VARIABLE_NAME ARIA_SORT_BUFFER_SIZE VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. -NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MIN_VALUE 16376 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO @@ -2287,7 +2287,7 @@ VARIABLE_SCOPE SESSION VARIABLE_TYPE BIGINT UNSIGNED VARIABLE_COMMENT The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE NUMERIC_MIN_VALUE 4096 -NUMERIC_MAX_VALUE 18446744073709551615 +NUMERIC_MAX_VALUE 1152921504606846975 NUMERIC_BLOCK_SIZE 1 ENUM_VALUE_LIST NULL READ_ONLY NO |