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 ## Creating new connection test_con1 ##
40 ## Setting value of max_join_size ##
41 SET @@session.max_join_size=2;
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 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=2;
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
52 2
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 MAX_JOIN_SIZE=# if the SELECT is okay
56 ## Dropping both the tables ##
57 Drop table t1, t2;
58 ## Restoring values ##
59 SET @@global.max_join_size = DEFAULT;
60 SET @@session.max_join_size = DEFAULT;
61 ## Dropping connections ##