MDEV-19713 Remove big_tables system variable

mark big_tables deprecated, the server can put temp tables on disk
as needed avoiding "table full" errors.

in case someone would really need to force a tmp table to be created
on disk from the start and for testing allow tmp_memory_table_size
to be set to 0.

fix tests to use that instead (and add a test that it actually
works).

make sure in-memory TREE size limit is never 0 (it's [ab]using
tmp_memory_table_size at the moment)

remove few sys_vars.*_basic tests

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 #