################# mysql-test\t\max_join_size_func.test ########################
#                                                                             #
# Variable Name: max_join_size                                                #
# Scope: GLOBAL | SESSION                                                     #
# Access Type: Dynamic                                                        #
# Data Type: numeric                                                          #
# Default Value: 4294967295                                                   #
# Range:  1-4294967295                                                        #
#                                                                             #
#                                                                             #
# Creation Date: 2008-03-07                                                   #
# Author:  Salman Rawala                                                      #
#                                                                             #
# Description: Test Cases of Dynamic System Variable max_join_size            #
#              that checks the functionality of this variable                 #
#                                                                             #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/                          #
#  server-system-variables.html                                               #
#                                                                             #
###############################################################################


--disable_warnings
drop table if exists t1;
--enable_warnings

##############################
#   Creating two new tables  #
##############################

--echo ## Creating new table t1 ##
CREATE TABLE t1
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name VARCHAR(30)
);

--echo ## Creating another table t2 ##
CREATE TABLE t2
(
id INT NOT NULL auto_increment,
PRIMARY KEY (id),
name VARCHAR(30)
);

#########################################
#   Inserting some data in both tables  #
#########################################

--echo ## Inserting records in both the tables ##
INSERT INTO t1(name) VALUES('Record_1');
INSERT INTO t1(name) VALUES('Record_2');
INSERT INTO t1(name) VALUES('Record_3');
INSERT INTO t1(name) VALUES('Record_4');
INSERT INTO t1(name) VALUES('Record_5');

INSERT INTO t2(name) VALUES('Record_1_1');
INSERT INTO t2(name) VALUES('Record_2_1');
INSERT INTO t2(name) VALUES('Record_3_1');
INSERT INTO t2(name) VALUES('Record_4_1');
INSERT INTO t2(name) VALUES('Record_5_1');


--echo '#--------------------FN_DYNVARS_079_01-------------------------#'
#######################################################################
#   Verifying case where max_join_size is greater than the join size  #
#######################################################################


--echo ## Setting max_join size value to 10 ## 
SET @@session.max_join_size=10;

--echo ## This should work ##
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;


--echo '#--------------------FN_DYNVARS_079_02-------------------------#'
####################################################################
#   Verifying case where max_join_size is less than the join size  #
####################################################################

--echo ## Creating new connection test_con1 ##
connect (test_con1, localhost, root,,);
connection test_con1;

--echo ## Setting value of max_join_size ## 
SET @@session.max_join_size=8;

--echo ## Since total joins are more than max_join_size value so error will occur ##
--Error ER_TOO_BIG_SELECT
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;


--echo '#--------------------FN_DYNVARS_079_03-------------------------#'
##########################################################
#   Verifying behavior of max_join_size on global scope  #
##########################################################

--echo ## Setting global value of variable ##
SET @@global.max_join_size=8;

--echo ## Creating and switching to new connection test_con2 ## 
connect (test_con2, localhost, root,,);
connection test_con2;

--echo ## Verifying value of max_join_size ##
SELECT @@global.max_join_size;

--echo ## Since total joins are more than max_join_size value so error will occur ## 
--Error ER_TOO_BIG_SELECT
SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

--echo ## Dropping both the tables ##
Drop table t1, t2;

--echo ## Restoring values ##
SET @@global.max_join_size = DEFAULT;
SET @@session.max_join_size = DEFAULT;

--echo ## Dropping connections ##
disconnect test_con1;
disconnect test_con2;