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

1 files changed, 14 insertions, 7 deletions

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