1** Setup ** 2 3SET @session_sql_big_selects = @@SESSION.sql_big_selects; 4SET @session_max_join_size = @@SESSION.max_join_size; 5SET @global_max_join_size = @@GLOBAL.max_join_size; 6SET MAX_JOIN_SIZE=9; 7CREATE TEMPORARY TABLE t1(a varchar(20) not null, b varchar(20)); 8CREATE TEMPORARY TABLE t2(a varchar(20) null, b varchar(20)); 9INSERT INTO t1 VALUES('aa','bb'); 10INSERT INTO t1 VALUES('aa1','bb'); 11INSERT INTO t1 VALUES('aa2','bb'); 12INSERT INTO t1 VALUES('aa3','bb'); 13INSERT INTO t1 VALUES('aa4','bb'); 14INSERT INTO t2 VALUES('aa','bb'); 15INSERT INTO t2 VALUES('aa1','bb'); 16INSERT INTO t2 VALUES('aa2','bb'); 17INSERT INTO t2 VALUES('aa3','bb'); 18INSERT INTO t2 VALUES('aa4','bb'); 19'#--------------------FN_DYNVARS_154_01-------------------------#' 20Expected error "Too big select" 21SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; 22ERROR 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 23Expected error The SELECT would examine more than MAX_JOIN_SIZE rows. 24'#--------------------FN_DYNVARS_154_02-------------------------#' 25SET SESSION SQL_BIG_SELECTS = 1; 26SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; 27a b a b 28aa bb aa bb 29aa1 bb aa1 bb 30aa2 bb aa2 bb 31aa3 bb aa3 bb 32aa4 bb aa4 bb 33This should work 34SET SESSION SQL_BIG_SELECTS = 0; 35DELETE FROM t2 WHERE a = 'aa4'; 36SELECT * FROM t1 INNER JOIN t2 ON t1.a = t2.a; 37a b a b 38aa bb aa bb 39aa1 bb aa1 bb 40aa2 bb aa2 bb 41aa3 bb aa3 bb 42This should work 43'#--------------------FN_DYNVARS_154_03-------------------------#' 44connect con_int1,localhost,root,,; 45connection con_int1; 46SELECT @@SESSION.sql_big_selects; 47@@SESSION.sql_big_selects 481 491 Expected 50SET SESSION sql_big_selects = 0; 51connect con_int2,localhost,root,,; 52connection con_int2; 53SELECT @@SESSION.sql_big_selects; 54@@SESSION.sql_big_selects 551 561 Expected 57SET SESSION sql_big_selects = 1; 58connection con_int1; 59SELECT @@SESSION.sql_big_selects; 60@@SESSION.sql_big_selects 610 620 Expected 63connection con_int2; 64SELECT @@SESSION.sql_big_selects; 65@@SESSION.sql_big_selects 661 671 Expected 68connection default; 69disconnect con_int1; 70disconnect con_int2; 71SET @@SESSION.sql_big_selects = @session_sql_big_selects; 72SET @@SESSION.max_join_size = @session_max_join_size; 73SET @@GLOBAL.max_join_size = @global_max_join_size; 74DROP TABLE t1; 75DROP TABLE t2; 76