1
drop table if exists t1;
2
## Creating new table t1 ##
5
id INT NOT NULL auto_increment,
9
## Creating another table t2 ##
12
id INT NOT NULL auto_increment,
16
## Inserting records in both the tables ##
17
INSERT INTO t1(name) VALUES('Record_1');
18
INSERT INTO t1(name) VALUES('Record_2');
19
INSERT INTO t1(name) VALUES('Record_3');
20
INSERT INTO t1(name) VALUES('Record_4');
21
INSERT INTO t1(name) VALUES('Record_5');
22
INSERT INTO t2(name) VALUES('Record_1_1');
23
INSERT INTO t2(name) VALUES('Record_2_1');
24
INSERT INTO t2(name) VALUES('Record_3_1');
25
INSERT INTO t2(name) VALUES('Record_4_1');
26
INSERT INTO t2(name) VALUES('Record_5_1');
27
'#--------------------FN_DYNVARS_079_01-------------------------#'
28
## Setting max_join size value to 10 ##
29
SET @@session.max_join_size=10;
30
## This should work ##
31
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
33
1 Record_1 1 Record_1_1
34
2 Record_2 2 Record_2_1
35
3 Record_3 3 Record_3_1
36
4 Record_4 4 Record_4_1
37
5 Record_5 5 Record_5_1
38
'#--------------------FN_DYNVARS_079_02-------------------------#'
39
## Creating new connection test_con1 ##
40
## Setting value of max_join_size ##
41
SET @@session.max_join_size=8;
42
## Since total joins are more than max_join_size value so error will occur ##
43
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
44
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
45
'#--------------------FN_DYNVARS_079_03-------------------------#'
46
## Setting global value of variable ##
47
SET @@global.max_join_size=8;
48
## Creating and switching to new connection test_con2 ##
49
## Verifying value of max_join_size ##
50
SELECT @@global.max_join_size;
51
@@global.max_join_size
53
## Since total joins are more than max_join_size value so error will occur ##
54
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
55
ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the SELECT is okay
56
## Dropping both the tables ##
58
## Restoring values ##
59
SET @@global.max_join_size = DEFAULT;
60
SET @@session.max_join_size = DEFAULT;
61
## Dropping connections ##