diff options
Diffstat (limited to 'mysql-test/main')
33 files changed, 120 insertions, 124 deletions
diff --git a/mysql-test/main/compress.result b/mysql-test/main/compress.result index 8fbbb324b16..788eb7ab13b 100644 --- a/mysql-test/main/compress.result +++ b/mysql-test/main/compress.result @@ -515,7 +515,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -528,7 +528,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -565,7 +565,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -578,7 +578,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/cte_recursive.result b/mysql-test/main/cte_recursive.result index b88f0ff6255..de0f515d0ad 100644 --- a/mysql-test/main/cte_recursive.result +++ b/mysql-test/main/cte_recursive.result @@ -3110,7 +3110,7 @@ SELECT * FROM cte; # # MDEV-15575: using recursive cte with big_tables enabled # -set big_tables=1; +set tmp_memory_table_size=0; with recursive qn as (select 123 as a union all select 1+a from qn where a<130) select * from qn; @@ -3123,13 +3123,13 @@ a 128 129 130 -set big_tables=default; +set tmp_memory_table_size=default; # # MDEV-15571: using recursive cte with big_tables enabled # create table t1 (a bigint); insert into t1 values(1); -set big_tables=1; +set tmp_memory_table_size=0; with recursive qn as ( select a from t1 @@ -3138,13 +3138,13 @@ select a*2000 from qn where a<10000000000000000000 ) select * from qn; ERROR 22003: BIGINT value is out of range in '`qn`.`a` * 2000' -set big_tables=default; +set tmp_memory_table_size=default; drop table t1; # # MDEV-15556: using recursive cte with big_tables enabled # when recursive tables are accessed by key # -SET big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); INSERT INTO t1 VALUES (1, "A", 2, 3), (2, "LA", 4, 5), (4, "LLA", 6, 7), @@ -3195,7 +3195,7 @@ id select_type table type possible_keys key key_len ref rows Extra 4 RECURSIVE UNION <derived2> ref key0 key0 5 test.t2.id 2 NULL UNION RESULT <union2,3,4> ALL NULL NULL NULL NULL NULL DROP TABLE t1,t2; -SET big_tables=0; +set tmp_memory_table_size=default; # # MDEV-15840: recursive tables are accessed by key # (the same problem as for MDEV-15556) diff --git a/mysql-test/main/cte_recursive.test b/mysql-test/main/cte_recursive.test index 6a4f55cd408..ce3b817a937 100644 --- a/mysql-test/main/cte_recursive.test +++ b/mysql-test/main/cte_recursive.test @@ -2138,13 +2138,13 @@ SELECT * FROM cte; --echo # MDEV-15575: using recursive cte with big_tables enabled --echo # -set big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table with recursive qn as (select 123 as a union all select 1+a from qn where a<130) select * from qn; -set big_tables=default; +set tmp_memory_table_size=default; --echo # --echo # MDEV-15571: using recursive cte with big_tables enabled @@ -2153,7 +2153,7 @@ set big_tables=default; create table t1 (a bigint); insert into t1 values(1); -set big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table --error ER_DATA_OUT_OF_RANGE with recursive qn as @@ -2164,7 +2164,7 @@ with recursive qn as ) select * from qn; -set big_tables=default; +set tmp_memory_table_size=default; drop table t1; @@ -2173,7 +2173,7 @@ drop table t1; --echo # when recursive tables are accessed by key --echo # -SET big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table CREATE TABLE t1 (id int, name char(10), leftpar int, rightpar int); INSERT INTO t1 VALUES @@ -2202,7 +2202,7 @@ eval EXPLAIN $q; DROP TABLE t1,t2; -SET big_tables=0; +set tmp_memory_table_size=default; --echo # --echo # MDEV-15840: recursive tables are accessed by key diff --git a/mysql-test/main/group_by.result b/mysql-test/main/group_by.result index d1b0898e6b2..3108f2f4e65 100644 --- a/mysql-test/main/group_by.result +++ b/mysql-test/main/group_by.result @@ -321,6 +321,7 @@ a c count(distinct rand()) drop table t1; CREATE TABLE t1 (a char(1)); INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL); +flush status; SELECT a FROM t1 GROUP BY a; a NULL @@ -359,7 +360,11 @@ A 4 B 4 a 1 b 1 -SET BIG_TABLES=1; +show status like 'Created%tables'; +Variable_name Value +Created_tmp_disk_tables 0 +Created_tmp_tables 6 +set tmp_memory_table_size=0; SELECT a FROM t1 GROUP BY a; a NULL @@ -398,7 +403,11 @@ A 4 B 4 a 1 b 1 -SET BIG_TABLES=0; +show status like 'Created%tables'; +Variable_name Value +Created_tmp_disk_tables 6 +Created_tmp_tables 12 +set tmp_memory_table_size=default; drop table t1; CREATE TABLE t1 ( `a` char(193) default NULL, @@ -515,14 +524,14 @@ a count(*) NULL 9 3 b 1 -set big_tables=1; +set tmp_memory_table_size=0; select a,count(*) from t1 group by a; a count(*) NULL 9 3 b 1 drop table t1; -set big_tables=0; +set tmp_memory_table_size=default; SET @save_optimizer_use_condition_selectivity=@@optimizer_use_condition_selectivity,@save_optimizer_switch=@@optimizer_switch; SET optimizer_switch='outer_join_with_cache=off',@@optimizer_use_condition_selectivity=4; create table t1 (a int not null, b int not null); @@ -1964,7 +1973,7 @@ DROP TABLE t1; # Bug#11765254 (58200): Assertion failed: param.sort_length when grouping # by functions # -SET BIG_TABLES=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (0),(0); SELECT 1 FROM t1 GROUP BY IF(`a`,'',''); @@ -1991,7 +2000,7 @@ Warning 1292 Truncated incorrect INTEGER value: 'jxW<' Warning 1292 Truncated incorrect INTEGER value: 'K' Warning 1292 Truncated incorrect INTEGER value: 'jxW<' DROP TABLE t1; -SET BIG_TABLES=0; +set tmp_memory_table_size=default; # # MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause # Bug#11761078: 53534: INCORRECT 'SELECT SQL_BIG_RESULT...' diff --git a/mysql-test/main/group_by.test b/mysql-test/main/group_by.test index f9242b5b4ff..7ba0945cc72 100644 --- a/mysql-test/main/group_by.test +++ b/mysql-test/main/group_by.test @@ -280,21 +280,28 @@ drop table t1; CREATE TABLE t1 (a char(1)); INSERT INTO t1 VALUES ('A'),('B'),('A'),('B'),('A'),('B'),(NULL),('a'),('b'),(NULL),('A'),('B'),(NULL); +flush status; SELECT a FROM t1 GROUP BY a; SELECT a,count(*) FROM t1 GROUP BY a; SELECT a FROM t1 GROUP BY binary a; SELECT a,count(*) FROM t1 GROUP BY binary a; SELECT binary a FROM t1 GROUP BY 1; SELECT binary a,count(*) FROM t1 GROUP BY 1; -# Do the same tests with MyISAM temporary tables -SET BIG_TABLES=1; +--disable_ps_protocol +show status like 'Created%tables'; +--enable_ps_protocol +# Do the same tests with on-disk temporary tables +set tmp_memory_table_size=0; SELECT a FROM t1 GROUP BY a; SELECT a,count(*) FROM t1 GROUP BY a; SELECT a FROM t1 GROUP BY binary a; SELECT a,count(*) FROM t1 GROUP BY binary a; SELECT binary a FROM t1 GROUP BY 1; SELECT binary a,count(*) FROM t1 GROUP BY 1; -SET BIG_TABLES=0; +--disable_ps_protocol +show status like 'Created%tables'; +--enable_ps_protocol +set tmp_memory_table_size=default; drop table t1; # @@ -391,10 +398,10 @@ drop table t1,t2,t3; create table t1 (a blob null); insert into t1 values (NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(""),(""),(""),("b"); select a,count(*) from t1 group by a; -set big_tables=1; +set tmp_memory_table_size=0; select a,count(*) from t1 group by a; drop table t1; -set big_tables=0; +set tmp_memory_table_size=default; # # Test of GROUP BY ... ORDER BY NULL optimization @@ -1344,7 +1351,7 @@ DROP TABLE t1; --echo # by functions --echo # -SET BIG_TABLES=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a INT); INSERT INTO t1 VALUES (0),(0); SELECT 1 FROM t1 GROUP BY IF(`a`,'',''); @@ -1352,7 +1359,7 @@ SELECT 1 FROM t1 GROUP BY TRIM(LEADING RAND() FROM ''); SELECT 1 FROM t1 GROUP BY SUBSTRING('',SLEEP(0),''); SELECT 1 FROM t1 GROUP BY SUBSTRING(SYSDATE() FROM 'K' FOR 'jxW<'); DROP TABLE t1; -SET BIG_TABLES=0; +set tmp_memory_table_size=default; --echo # --echo # MDEV-641 LP:1002108 - Wrong result (or crash) from a query with duplicated field in the group list and a limit clause diff --git a/mysql-test/main/named_pipe.result b/mysql-test/main/named_pipe.result index f8c2acf98a0..9fc7abd79f5 100644 --- a/mysql-test/main/named_pipe.result +++ b/mysql-test/main/named_pipe.result @@ -509,7 +509,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -522,7 +522,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -559,7 +559,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -572,7 +572,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/pool_of_threads.result b/mysql-test/main/pool_of_threads.result index 9c02dfac9cd..fd3c848117c 100644 --- a/mysql-test/main/pool_of_threads.result +++ b/mysql-test/main/pool_of_threads.result @@ -509,7 +509,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -522,7 +522,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -559,7 +559,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -572,7 +572,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/select.result b/mysql-test/main/select.result index 0f84ed684da..ba5ec36430e 100644 --- a/mysql-test/main/select.result +++ b/mysql-test/main/select.result @@ -512,7 +512,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -525,7 +525,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -562,7 +562,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -575,7 +575,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/select.test b/mysql-test/main/select.test index 43984810c66..22baccc625c 100644 --- a/mysql-test/main/select.test +++ b/mysql-test/main/select.test @@ -1425,9 +1425,9 @@ drop table tmp; # big table done -SET BIG_TABLES=1; +set tmp_memory_table_size=0; # force on-disk tmp table select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; select distinct fld5 from t2 limit 10; @@ -1436,9 +1436,9 @@ select distinct fld5 from t2 limit 10; # select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; -SET BIG_TABLES=1; # Force use of MyISAM +set tmp_memory_table_size=0; # force on-disk tmp table select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; # diff --git a/mysql-test/main/select_jcl6.result b/mysql-test/main/select_jcl6.result index 7276814114f..2de5df60901 100644 --- a/mysql-test/main/select_jcl6.result +++ b/mysql-test/main/select_jcl6.result @@ -523,7 +523,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -536,7 +536,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -573,7 +573,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -586,7 +586,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/select_pkeycache.result b/mysql-test/main/select_pkeycache.result index 0f84ed684da..ba5ec36430e 100644 --- a/mysql-test/main/select_pkeycache.result +++ b/mysql-test/main/select_pkeycache.result @@ -512,7 +512,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -525,7 +525,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -562,7 +562,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -575,7 +575,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/ssl.result b/mysql-test/main/ssl.result index 00faea58fd2..40a32fdd1dd 100644 --- a/mysql-test/main/ssl.result +++ b/mysql-test/main/ssl.result @@ -518,7 +518,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -531,7 +531,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -568,7 +568,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -581,7 +581,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/ssl_compress.result b/mysql-test/main/ssl_compress.result index d298f8ec6e4..8c63c798afa 100644 --- a/mysql-test/main/ssl_compress.result +++ b/mysql-test/main/ssl_compress.result @@ -515,7 +515,7 @@ insert into tmp select * from t3; insert into t3 select * from tmp; alter table t3 add t2nr int not null auto_increment primary key first; drop table tmp; -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct concat(fld3," ",fld3) as namn from t2,t3 where t2.fld1=t3.t2nr order by namn limit 10; namn Abraham Abraham @@ -528,7 +528,7 @@ ammonium ammonium analyzable analyzable animals animals animized animized -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct concat(fld3," ",fld3) from t2,t3 where t2.fld1=t3.t2nr order by fld3 limit 10; concat(fld3," ",fld3) Abraham Abraham @@ -565,7 +565,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=1; +set tmp_memory_table_size=0; select distinct fld3,count(*) from t2 group by companynr,fld3 limit 10; fld3 count(*) affixed 1 @@ -578,7 +578,7 @@ attendants 1 bedlam 1 bedpost 1 boasted 1 -SET BIG_TABLES=0; +set tmp_memory_table_size=default; select distinct fld3,repeat("a",length(fld3)),count(*) from t2 group by companynr,fld3 limit 100,10; fld3 repeat("a",length(fld3)) count(*) circus aaaaaa 1 diff --git a/mysql-test/main/subselect.result b/mysql-test/main/subselect.result index 349e7dca129..2f78a77b10f 100644 --- a/mysql-test/main/subselect.result +++ b/mysql-test/main/subselect.result @@ -7134,7 +7134,7 @@ drop table t1,t2,t3,t4; # MDEV-7122 # Assertion `0' failed in subselect_hash_sj_engine::init # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); @@ -7143,7 +7143,7 @@ a 0 0 DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; # # MDEV-10776: Server crash on query # diff --git a/mysql-test/main/subselect.test b/mysql-test/main/subselect.test index be17254202e..8b34e32740b 100644 --- a/mysql-test/main/subselect.test +++ b/mysql-test/main/subselect.test @@ -5959,12 +5959,12 @@ drop table t1,t2,t3,t4; --echo # MDEV-7122 --echo # Assertion `0' failed in subselect_hash_sj_engine::init --echo # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; --echo # --echo # MDEV-10776: Server crash on query diff --git a/mysql-test/main/subselect_cache.result b/mysql-test/main/subselect_cache.result index d6bd8970750..0d6f459ce88 100644 --- a/mysql-test/main/subselect_cache.result +++ b/mysql-test/main/subselect_cache.result @@ -1861,7 +1861,7 @@ Subquery_cache_hit 0 Subquery_cache_miss 4 drop table t1; #test of big_tables switch and outer table reference in subquery with grouping -set big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1 (a INT PRIMARY KEY, b INT); INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer; @@ -1873,7 +1873,7 @@ SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_ 5 6 drop table t1; -set big_tables=0; +set tmp_memory_table_size=default; #test of function reference to outer query set local group_concat_max_len=400; create table t2 (a int, b int); diff --git a/mysql-test/main/subselect_cache.test b/mysql-test/main/subselect_cache.test index fa806ecdc9f..8fcecf1281b 100644 --- a/mysql-test/main/subselect_cache.test +++ b/mysql-test/main/subselect_cache.test @@ -429,12 +429,12 @@ show status like "subquery_cache%"; drop table t1; --echo #test of big_tables switch and outer table reference in subquery with grouping -set big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table CREATE TABLE t1 (a INT PRIMARY KEY, b INT); INSERT INTO t1 VALUES (1,1),(2,1),(3,2),(4,2),(5,3),(6,3); SELECT (SELECT t1_outer.a FROM t1 AS t1_inner GROUP BY b LIMIT 1) FROM t1 AS t1_outer; drop table t1; -set big_tables=0; +set tmp_memory_table_size=default; --echo #test of function reference to outer query set local group_concat_max_len=400; diff --git a/mysql-test/main/subselect_mat.result b/mysql-test/main/subselect_mat.result index 34b58daa50e..c5fa7ddc92c 100644 --- a/mysql-test/main/subselect_mat.result +++ b/mysql-test/main/subselect_mat.result @@ -2118,8 +2118,7 @@ DROP VIEW v2; # # MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON # -SET @tmp_mdev5811= @@big_tables; -SET big_tables = ON; +set tmp_memory_table_size=0; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT); @@ -2128,7 +2127,7 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); a a DROP TABLE t1,t2; -SET big_tables=@tmp_mdev5811; +set tmp_memory_table_size=default; # End of 5.3 tests # # MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries diff --git a/mysql-test/main/subselect_no_exists_to_in.result b/mysql-test/main/subselect_no_exists_to_in.result index 84c415d1ce1..e830c8bdc91 100644 --- a/mysql-test/main/subselect_no_exists_to_in.result +++ b/mysql-test/main/subselect_no_exists_to_in.result @@ -7134,7 +7134,7 @@ drop table t1,t2,t3,t4; # MDEV-7122 # Assertion `0' failed in subselect_hash_sj_engine::init # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); @@ -7143,7 +7143,7 @@ a 0 0 DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; # # MDEV-10776: Server crash on query # diff --git a/mysql-test/main/subselect_no_mat.result b/mysql-test/main/subselect_no_mat.result index 93035e235f7..8203f59f92b 100644 --- a/mysql-test/main/subselect_no_mat.result +++ b/mysql-test/main/subselect_no_mat.result @@ -7127,7 +7127,7 @@ drop table t1,t2,t3,t4; # MDEV-7122 # Assertion `0' failed in subselect_hash_sj_engine::init # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); @@ -7136,7 +7136,7 @@ a 0 0 DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; # # MDEV-10776: Server crash on query # diff --git a/mysql-test/main/subselect_no_opts.result b/mysql-test/main/subselect_no_opts.result index 09f664d3c28..ec4d0d99a6d 100644 --- a/mysql-test/main/subselect_no_opts.result +++ b/mysql-test/main/subselect_no_opts.result @@ -7125,7 +7125,7 @@ drop table t1,t2,t3,t4; # MDEV-7122 # Assertion `0' failed in subselect_hash_sj_engine::init # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); @@ -7134,7 +7134,7 @@ a 0 0 DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; # # MDEV-10776: Server crash on query # diff --git a/mysql-test/main/subselect_no_scache.result b/mysql-test/main/subselect_no_scache.result index 765bb15a3df..f4c850409da 100644 --- a/mysql-test/main/subselect_no_scache.result +++ b/mysql-test/main/subselect_no_scache.result @@ -7140,7 +7140,7 @@ drop table t1,t2,t3,t4; # MDEV-7122 # Assertion `0' failed in subselect_hash_sj_engine::init # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); @@ -7149,7 +7149,7 @@ a 0 0 DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; # # MDEV-10776: Server crash on query # diff --git a/mysql-test/main/subselect_no_semijoin.result b/mysql-test/main/subselect_no_semijoin.result index 97d2f3b058f..e1d147da761 100644 --- a/mysql-test/main/subselect_no_semijoin.result +++ b/mysql-test/main/subselect_no_semijoin.result @@ -7125,7 +7125,7 @@ drop table t1,t2,t3,t4; # MDEV-7122 # Assertion `0' failed in subselect_hash_sj_engine::init # -SET SESSION big_tables=1; +set tmp_memory_table_size=0; CREATE TABLE t1(a char(255) DEFAULT '', KEY(a(10))) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci; INSERT INTO t1 VALUES(0),(0),(0); SELECT * FROM t1 WHERE a IN(SELECT MIN(a) FROM t1); @@ -7134,7 +7134,7 @@ a 0 0 DROP TABLE t1; -SET SESSION big_tables=0; +set tmp_memory_table_size=default; # # MDEV-10776: Server crash on query # diff --git a/mysql-test/main/subselect_sj_mat.result b/mysql-test/main/subselect_sj_mat.result index afc75a22962..6615eb41425 100644 --- a/mysql-test/main/subselect_sj_mat.result +++ b/mysql-test/main/subselect_sj_mat.result @@ -2154,8 +2154,7 @@ DROP VIEW v2; # # MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON # -SET @tmp_mdev5811= @@big_tables; -SET big_tables = ON; +set tmp_memory_table_size=0; CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); CREATE TABLE t2 (b INT); @@ -2164,7 +2163,7 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); a a DROP TABLE t1,t2; -SET big_tables=@tmp_mdev5811; +set tmp_memory_table_size=default; # End of 5.3 tests # # MDEV-5056: Wrong result (extra rows) with materialization+semijoin, IN subqueries diff --git a/mysql-test/main/subselect_sj_mat.test b/mysql-test/main/subselect_sj_mat.test index 1de8701ecbb..8d5bbd8ef82 100644 --- a/mysql-test/main/subselect_sj_mat.test +++ b/mysql-test/main/subselect_sj_mat.test @@ -1760,8 +1760,7 @@ DROP VIEW v2; --echo # --echo # MDEV-5811: Server crashes in best_access_path with materialization+semijoin and big_tables=ON --echo # -SET @tmp_mdev5811= @@big_tables; -SET big_tables = ON; +set tmp_memory_table_size=0; # force on-disk tmp table CREATE TABLE t1 (a INT); INSERT INTO t1 VALUES (1),(2); @@ -1773,7 +1772,7 @@ SELECT * FROM t1 AS t1_1, t1 AS t1_2 WHERE ( t1_1.a, t1_2.a ) IN ( SELECT MAX(b), MIN(b) FROM t2 ); DROP TABLE t1,t2; -SET big_tables=@tmp_mdev5811; +set tmp_memory_table_size=default; --echo # End of 5.3 tests diff --git a/mysql-test/main/type_blob.result b/mysql-test/main/type_blob.result index cfb47f7b850..4dd416426c7 100644 --- a/mysql-test/main/type_blob.result +++ b/mysql-test/main/type_blob.result @@ -245,7 +245,7 @@ HELLO HELLO MY a hello -set big_tables=1; +set tmp_memory_table_size=0; select distinct t from t1; t NULL @@ -326,7 +326,7 @@ HELLO HELLO MY a hello -set big_tables=0; +set tmp_memory_table_size=default; select distinct * from t1; t c b d NULL NULL NULL NULL diff --git a/mysql-test/main/type_blob.test b/mysql-test/main/type_blob.test index df565b187b4..d81c2c644c4 100644 --- a/mysql-test/main/type_blob.test +++ b/mysql-test/main/type_blob.test @@ -112,7 +112,7 @@ select distinct t from t1 order by t; select distinct b from t1 order by b; select t from t1 group by t; select b from t1 group by b; -set big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table select distinct t from t1; select distinct b from t1; select distinct t from t1 order by t; @@ -123,7 +123,7 @@ select distinct c from t1 order by c; select distinct d from t1 order by d; select c from t1 group by c; select d from t1 group by d; -set big_tables=0; +set tmp_memory_table_size=default; select distinct * from t1; select t,count(*) from t1 group by t; select b,count(*) from t1 group by b; diff --git a/mysql-test/main/variables.result b/mysql-test/main/variables.result index 4fb40d718b5..66acb163661 100644 --- a/mysql-test/main/variables.result +++ b/mysql-test/main/variables.result @@ -162,7 +162,6 @@ id select_type table type possible_keys key key_len ref rows filtered Extra 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL No tables used Warnings: Note 1003 select @@IDENTITY AS `@@IDENTITY`,last_insert_id() AS `last_insert_id()`,@@identity AS `@@identity` -set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; set global concurrent_insert=2; show variables like 'concurrent_insert'; Variable_name Value @@ -422,18 +421,14 @@ SELECT @@version LIKE 'non-existent'; SELECT @@version_compile_os LIKE 'non-existent'; @@version_compile_os LIKE 'non-existent' 0 -set big_tables=OFFF; -ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF' -set big_tables="OFFF"; -ERROR 42000: Variable 'big_tables' can't be set to the value of 'OFFF' set unknown_variable=1; ERROR HY000: Unknown system variable 'unknown_variable' set max_join_size="hello"; ERROR 42000: Incorrect argument type to variable 'max_join_size' set default_storage_engine=UNKNOWN_TABLE_TYPE; ERROR 42000: Unknown storage engine 'UNKNOWN_TABLE_TYPE' -set default_storage_engine=MERGE, big_tables=2; -ERROR 42000: Variable 'big_tables' can't be set to the value of '2' +set default_storage_engine=MERGE, sql_warnings=NULL; +ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL' show local variables like 'default_storage_engine'; Variable_name Value default_storage_engine MEMORY @@ -456,10 +451,9 @@ ERROR HY000: Variable 'myisam_max_sort_file_size' is a GLOBAL variable and shoul set @@SQL_WARNINGS=NULL; ERROR 42000: Variable 'sql_warnings' can't be set to the value of 'NULL' set autocommit=1; -set big_tables=1; -select @@autocommit, @@big_tables; -@@autocommit @@big_tables -1 1 +select @@autocommit; +@@autocommit +1 set global binlog_cache_size=100; Warnings: Warning 1292 Truncated incorrect binlog_cache_size value: '100' diff --git a/mysql-test/main/variables.test b/mysql-test/main/variables.test index b303958dff8..eab323dda3d 100644 --- a/mysql-test/main/variables.test +++ b/mysql-test/main/variables.test @@ -113,8 +113,6 @@ explain extended select last_insert_id(345); select @@IDENTITY,last_insert_id(), @@identity; explain extended select @@IDENTITY,last_insert_id(), @@identity; -set big_tables=OFF, big_tables=ON, big_tables=0, big_tables=1, big_tables="OFF", big_tables="ON"; - set global concurrent_insert=2; show variables like 'concurrent_insert'; select * from information_schema.session_variables where variable_name like 'concurrent_insert'; @@ -241,10 +239,6 @@ SELECT @@version_compile_os LIKE 'non-existent'; # The following should give errors ---error ER_WRONG_VALUE_FOR_VAR -set big_tables=OFFF; ---error ER_WRONG_VALUE_FOR_VAR -set big_tables="OFFF"; --error ER_UNKNOWN_SYSTEM_VARIABLE set unknown_variable=1; --error ER_WRONG_TYPE_FOR_VAR @@ -252,7 +246,7 @@ set max_join_size="hello"; --error ER_UNKNOWN_STORAGE_ENGINE set default_storage_engine=UNKNOWN_TABLE_TYPE; --error ER_WRONG_VALUE_FOR_VAR -set default_storage_engine=MERGE, big_tables=2; +set default_storage_engine=MERGE, sql_warnings=NULL; show local variables like 'default_storage_engine'; --error ER_UNKNOWN_CHARACTER_SET set character_set_client=UNKNOWN_CHARACTER_SET; @@ -276,8 +270,7 @@ set @@SQL_WARNINGS=NULL; # Test setting all variables set autocommit=1; -set big_tables=1; -select @@autocommit, @@big_tables; +select @@autocommit; set global binlog_cache_size=100; set bulk_insert_buffer_size=100; set character set cp1251_koi8; diff --git a/mysql-test/main/view.result b/mysql-test/main/view.result index 13f3761cdee..496cea70d81 100644 --- a/mysql-test/main/view.result +++ b/mysql-test/main/view.result @@ -5738,8 +5738,7 @@ drop view v60; # # MDEV-15572: view.test, server crash with --big-tables=1 # -set @save_big_tables=@@big_tables; -set big_tables=ON; +set tmp_memory_table_size=0; CREATE TABLE t1 ( f1 int , f2 int , f3 int , f4 int); CREATE TABLE t2 ( f1 int , f2 int , f3 int , f4 int); CREATE VIEW v1 AS @@ -5749,7 +5748,7 @@ SELECT f1, f2, f3, f4 FROM t1; ERROR HY000: Can not modify more than one base table through a join view 'test.v1' drop view v1; drop table t1, t2; -set big_tables=@save_big_tables; +set tmp_memory_table_size=default; # ----------------------------------------------------------------- # -- End of 5.5 tests. # ----------------------------------------------------------------- diff --git a/mysql-test/main/view.test b/mysql-test/main/view.test index 4fc87f6283c..6c9a6aa70c8 100644 --- a/mysql-test/main/view.test +++ b/mysql-test/main/view.test @@ -5628,8 +5628,7 @@ drop view v60; --echo # MDEV-15572: view.test, server crash with --big-tables=1 --echo # -set @save_big_tables=@@big_tables; -set big_tables=ON; +set tmp_memory_table_size=0; # force on-disk tmp table CREATE TABLE t1 ( f1 int , f2 int , f3 int , f4 int); CREATE TABLE t2 ( f1 int , f2 int , f3 int , f4 int); @@ -5642,7 +5641,7 @@ REPLACE INTO v1 (f1, f2, f3, f4) drop view v1; drop table t1, t2; -set big_tables=@save_big_tables; +set tmp_memory_table_size=default; --echo # ----------------------------------------------------------------- --echo # -- End of 5.5 tests. diff --git a/mysql-test/main/win.result b/mysql-test/main/win.result index 4adb5c84f1e..0eec38db611 100644 --- a/mysql-test/main/win.result +++ b/mysql-test/main/win.result @@ -1742,8 +1742,7 @@ drop table t1; # create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -set @tmp=@@big_tables; -set big_tables=1; +set tmp_memory_table_size=0; select rank() over (order by a) from t1; rank() over (order by a) 1 @@ -1756,7 +1755,7 @@ rank() over (order by a) 8 9 10 -set big_tables=@tmp; +set tmp_memory_table_size=default; drop table t1; # # Check if "ORDER BY window_func" works diff --git a/mysql-test/main/win.test b/mysql-test/main/win.test index 3d5fb2240de..ef55f4bdbc7 100644 --- a/mysql-test/main/win.test +++ b/mysql-test/main/win.test @@ -1067,10 +1067,9 @@ drop table t1; --echo # create table t1(a int); insert into t1 values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); -set @tmp=@@big_tables; -set big_tables=1; +set tmp_memory_table_size=0; # force on-disk tmp table select rank() over (order by a) from t1; -set big_tables=@tmp; +set tmp_memory_table_size=default; drop table t1; --echo # |