Debian Codegraph @

mysql-test/suite/sys_vars/r/max_join_size_func.result

raw
1 drop table if exists t1;
2 ## Creating new table t1 ##
3 CREATE TABLE t1
4 (
5 id INT NOT NULL auto_increment,
6 PRIMARY KEY (id),
7 name VARCHAR(30)
8 );
9 ## Creating another table t2 ##
10 CREATE TABLE t2
11 (
12 id INT NOT NULL auto_increment,
13 PRIMARY KEY (id),
14 name VARCHAR(30)
15 );
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;
32 id name id name
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 connect test_con1, localhost, root,,;
40 connection test_con1;
41 ## Setting value of max_join_size ##
42 SET @@session.max_join_size=4;
43 ## Since total joins are more than max_join_size value so error will occur ##
44 SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
45 ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
46 '#--------------------FN_DYNVARS_079_03-------------------------#'
47 ## Setting global value of variable ##
48 SET @@global.max_join_size=4;
49 connect test_con2, localhost, root,,;
50 connection test_con2;
51 ## Verifying value of max_join_size ##
52 SELECT @@global.max_join_size;
53 @@global.max_join_size
54 4
55 ## Since total joins are more than max_join_size value so error will occur ##
56 SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;
57 ERROR 42000: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay
58 ## Dropping both the tables ##
59 Drop table t1, t2;
60 ## Restoring values ##
61 SET @@global.max_join_size = DEFAULT;
62 SET @@session.max_join_size = DEFAULT;
63 disconnect test_con1;
64 disconnect test_con2;