1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
|
############# mysql-test\t\sql_big_tables_func.test ########################
# #
# Variable Name: sql_big_tables #
# Scope: SESSION #
# Access Type: Dynamic #
# Data Type: BOOLEAN #
# Default Value: 0 FALSE #
# Values: 1 TRUE, 0 FALSE #
# #
# #
# Creation Date: 2008-02-25 #
# Author: Sharique Abdullah #
# #
# Description: Test Cases of Dynamic System Variable "sql_big_tables" #
# that checks behavior of this variable in the following ways #
# * Functionality based on different values #
# #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html #
# #
############################################################################
# Due to difference in the result the ps-protocol is disabled
--disable_ps_protocol
--echo ** Setup **
--echo
#
# Setup
#
SET @old_big_tables = @@SESSION.sql_big_tables;
#
# Create tables
#
CREATE TABLE t1(a varchar(20), b varchar(20));
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
--echo '#--------------------FN_DYNVARS_155_01-------------------------#'
#
# TRUE mode
#
SET SESSION sql_big_tables = 1;
let $diskTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
let $totalTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
eval SET @diskTableCount = $diskTablesBig;
eval SET @tempTableCount = $totalTablesBig;
SELECT * FROM (SELECT ta.a as a, tb.b as b
FROM t1 as ta INNER JOIN t1 as tb ON ta.a = tb.a) sub;
let $newDiskTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
let $newTotalTablesBig = query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
eval SET @diskTableCount = $newDiskTablesBig - @diskTableCount;
eval SET @tempTableCount = $newTotalTablesBig - @tempTableCount;
SELECT @diskTableCount;
--echo 1 Expected
SELECT @tempTableCount;
--echo 1 Expected
--echo '#--------------------FN_DYNVARS_155_02-------------------------#'
#
# FALSE mode
#
SET SESSION sql_big_tables = 0;
let $diskTables = query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
let $totalTables = query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
eval SET @diskTableCount = $diskTables;
eval SET @tempTableCount = $totalTables;
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;
let $newDiskTables= query_get_value(SHOW STATUS LIKE 'Created_tmp_disk_tables', Value, 1);
let $newTotalTables= query_get_value(SHOW STATUS LIKE 'Created_tmp_tables', Value, 1);
eval SET @diskTableCount = $newDiskTables - @diskTableCount;
eval SET @tempTableCount = $newTotalTables - @tempTableCount;
SELECT @diskTableCount;
--echo 0 Expected
SELECT @tempTableCount;
--echo 1 Expected
--echo '#--------------------FN_DYNVARS_155_03-------------------------#'
#
# Session data integrity check
#
--echo ** Connecting con_int1 using root **
connect (con_int1,localhost,root,,);
--echo ** Connection con_int1 **
connection con_int1;
SELECT @@SESSION.sql_big_tables;
--echo 0 / FALSE Expected;
SET SESSION sql_big_tables = FALSE;
--echo ** Connecting con_int2 using root **
connect (con_int2,localhost,root,,);
--echo ** Connection con_int2 **
connection con_int2;
SELECT @@SESSION.sql_big_tables;
--echo 0 / FALSE Expected;
SET SESSION sql_big_tables = TRUE;
--echo ** Connection con_int1 **
connection con_int1;
SELECT @@SESSION.sql_big_tables;
--echo 0 / FALSE Expected;
--echo ** Connection con_int2 **
connection con_int2;
SELECT @@SESSION.sql_big_tables;
--echo 1 / TRUE Expected;
--echo ** Connection default **
connection default;
--echo Disconnecting Connections con_int1, con_int2
disconnect con_int1;
disconnect con_int2;
#
# Cleanup
#
SET SESSION sql_big_tables = @old_big_tables;
DROP TABLE t1;
--enable_ps_protocol
|
