1
############# mysql-test\t\sql_big_tables_func.test ########################
3
# Variable Name: sql_big_tables #
5
# Access Type: Dynamic #
7
# Default Value: 0 FALSE #
8
# Values: 1 TRUE, 0 FALSE #
11
# Creation Date: 2008-02-25 #
12
# Author: Sharique Abdullah #
14
# Description: Test Cases of Dynamic System Variable "sql_big_tables" #
15
# that checks behavior of this variable in the following ways #
16
# * Functionality based on different values #
18
# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
20
############################################################################
22
# Due to difference in the result the ps-protocol is disabled
31
SET @old_big_tables = @@SESSION.sql_big_tables;
37
CREATE TABLE t1(a varchar(20), b varchar(20));
39
INSERT INTO t1 VALUES('aa','bb');
40
INSERT INTO t1 VALUES('aa','bb');
41
INSERT INTO t1 VALUES('aa','bb');
42
INSERT INTO t1 VALUES('aa','bb');
43
INSERT INTO t1 VALUES('aa','bb');
45
--echo '#--------------------FN_DYNVARS_155_01-------------------------#'
50
SET SESSION sql_big_tables = 1;
52
let $diskTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
53
let $totalTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
55
eval SET @diskTableCount = $diskTablesBig;
56
eval SET @tempTableCount = $totalTablesBig;
58
SELECT * FROM (SELECT ta.a as a, tb.b as b
59
FROM t1 as ta INNER JOIN t1 as tb ON ta.a = tb.a) sub;
61
let $newDiskTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
62
let $newTotalTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
64
eval SET @diskTableCount = $newDiskTablesBig - @diskTableCount;
65
eval SET @tempTableCount = $newTotalTablesBig - @tempTableCount;
67
SELECT @diskTableCount;
69
SELECT @tempTableCount;
72
--echo '#--------------------FN_DYNVARS_155_02-------------------------#'
77
SET SESSION sql_big_tables = 0;
79
let $diskTables = query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
80
let $totalTables = query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
82
eval SET @diskTableCount = $diskTables;
83
eval SET @tempTableCount = $totalTables;
85
SELECT * FROM (SELECT ta.b as a, tb.a as b FROM t1 as ta INNER JOIN t1 as tb ON ta.a = tb.a) sub;
87
let $newDiskTables= query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
88
let $newTotalTables= query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
90
eval SET @diskTableCount = $newDiskTables - @diskTableCount;
91
eval SET @tempTableCount = $newTotalTables - @tempTableCount;
93
SELECT @diskTableCount;
95
SELECT @tempTableCount;
98
--echo '#--------------------FN_DYNVARS_155_03-------------------------#'
100
# Session data integrity check
102
--echo ** Connecting con_int1 using root **
103
connect (con_int1,localhost,root,,);
104
--echo ** Connection con_int1 **
107
SELECT @@SESSION.sql_big_tables;
108
--echo 0 / FALSE Expected;
109
SET SESSION sql_big_tables = FALSE;
111
--echo ** Connecting con_int2 using root **
112
connect (con_int2,localhost,root,,);
113
--echo ** Connection con_int2 **
116
SELECT @@SESSION.sql_big_tables;
117
--echo 0 / FALSE Expected;
119
SET SESSION sql_big_tables = TRUE;
121
--echo ** Connection con_int1 **
123
SELECT @@SESSION.sql_big_tables;
124
--echo 0 / FALSE Expected;
126
--echo ** Connection con_int2 **
128
SELECT @@SESSION.sql_big_tables;
129
--echo 1 / TRUE Expected;
131
--echo ** Connection default **
133
--echo Disconnecting Connections con_int1, con_int2
141
SET SESSION sql_big_tables = @old_big_tables;