1
################# mysql-test\t\max_join_size_func.test ########################
3
# Variable Name: max_join_size #
4
# Scope: GLOBAL | SESSION #
5
# Access Type: Dynamic #
7
# Default Value: 4294967295 #
8
# Range: 1-4294967295 #
11
# Creation Date: 2008-03-07 #
12
# Author: Salman Rawala #
14
# Description: Test Cases of Dynamic System Variable max_join_size #
15
# that checks the functionality of this variable #
17
# Reference: http://dev.mysql.com/doc/refman/5.1/en/ #
18
# server-system-variables.html #
20
###############################################################################
24
drop table if exists t1;
27
##############################
28
# Creating two new tables #
29
##############################
31
--echo ## Creating new table t1 ##
34
id INT NOT NULL auto_increment,
39
--echo ## Creating another table t2 ##
42
id INT NOT NULL auto_increment,
47
#########################################
48
# Inserting some data in both tables #
49
#########################################
51
--echo ## Inserting records in both the tables ##
52
INSERT INTO t1(name) VALUES('Record_1');
53
INSERT INTO t1(name) VALUES('Record_2');
54
INSERT INTO t1(name) VALUES('Record_3');
55
INSERT INTO t1(name) VALUES('Record_4');
56
INSERT INTO t1(name) VALUES('Record_5');
58
INSERT INTO t2(name) VALUES('Record_1_1');
59
INSERT INTO t2(name) VALUES('Record_2_1');
60
INSERT INTO t2(name) VALUES('Record_3_1');
61
INSERT INTO t2(name) VALUES('Record_4_1');
62
INSERT INTO t2(name) VALUES('Record_5_1');
65
--echo '#--------------------FN_DYNVARS_079_01-------------------------#'
66
#######################################################################
67
# Verifying case where max_join_size is greater than the join size #
68
#######################################################################
71
--echo ## Setting max_join size value to 10 ##
72
SET @@session.max_join_size=10;
74
--echo ## This should work ##
75
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
78
--echo '#--------------------FN_DYNVARS_079_02-------------------------#'
79
####################################################################
80
# Verifying case where max_join_size is less than the join size #
81
####################################################################
83
--echo ## Creating new connection test_con1 ##
84
connect (test_con1, localhost, root,,);
87
--echo ## Setting value of max_join_size ##
88
SET @@session.max_join_size=8;
90
--echo ## Since total joins are more than max_join_size value so error will occur ##
91
--Error ER_TOO_BIG_SELECT
92
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
95
--echo '#--------------------FN_DYNVARS_079_03-------------------------#'
96
##########################################################
97
# Verifying behavior of max_join_size on global scope #
98
##########################################################
100
--echo ## Setting global value of variable ##
101
SET @@global.max_join_size=8;
103
--echo ## Creating and switching to new connection test_con2 ##
104
connect (test_con2, localhost, root,,);
105
connection test_con2;
107
--echo ## Verifying value of max_join_size ##
108
SELECT @@global.max_join_size;
110
--echo ## Since total joins are more than max_join_size value so error will occur ##
111
--Error ER_TOO_BIG_SELECT
112
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
114
--echo ## Dropping both the tables ##
117
--echo ## Restoring values ##
118
SET @@global.max_join_size = DEFAULT;
119
SET @@session.max_join_size = DEFAULT;
121
--echo ## Dropping connections ##
122
disconnect test_con1;
123
disconnect test_con2;