############# mysql-test\t\sql_buffer_result_func.test #####################
#                                                                          #
# Variable Name: sql_buffer_result                                         #
# Scope: SESSION                                                           #
# Access Type: Dynamic                                                     #
# Data Type: BOOLEAN                                                       #
# Default Value: 0 FALSE                                                   #
# Values:       1 TRUE, 0 FALSE                                            #
#                                                                          #
#                                                                          #
# Creation Date: 2008-02-25                                                #
# Author:  Sharique Abdullah                                               #
#                                                                          #
# Description: Test Cases of Dynamic System Variable "sql_buffer_result"   #
#              that checks behavior of this variable in the following ways #
#              * Functionality based on different values                   #
#                                                                          #
# Reference: http://dev.mysql.com/doc/refman/5.1/en/set-option.html        #
#                                                                          #
############################################################################

--echo ** Setup **
--echo
#
# Setup
#

SET @original_sql_buffer_result = @@sql_buffer_result;

#
# Create tables
#

CREATE TEMPORARY TABLE t1(a varchar(20), b varchar(20));

INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');
INSERT INTO t1 VALUES('aa','bb');

--echo '#--------------------FN_DYNVARS_156_01-------------------------#'
#
# TRUE mode
#

SET SESSION sql_buffer_result = 1;

--disable_ps_protocol
SHOW STATUS LIKE 'Created_tmp_tables';
--enable_ps_protocol
--echo Expected value : 0.

SELECT * FROM t1;

--disable_ps_protocol
SHOW STATUS LIKE 'Created_tmp_tables';
--enable_ps_protocol
--echo Expected value : 1.

SELECT * FROM t1;

--disable_ps_protocol
SHOW STATUS LIKE 'Created_tmp_tables';
--enable_ps_protocol
--echo Expected value : 2.

--echo '#--------------------FN_DYNVARS_156_02-------------------------#'
#
# FALSE mode
#

SET SESSION sql_buffer_result = 0;

--disable_ps_protocol
SHOW STATUS LIKE 'Created_tmp_tables';
--enable_ps_protocol
--echo Expected value : 2.

SELECT * FROM t1;

--disable_ps_protocol
SHOW STATUS LIKE 'Created_tmp_tables';
--enable_ps_protocol
--echo Expected value : 2.

--echo '#--------------------FN_DYNVARS_156_03-------------------------#'
#
# Session data integrity check
#
connect (con_int1,localhost,root,,);

connection con_int1;

SELECT @@SESSION.sql_buffer_result;
--echo 0 / FALSE Expected

SET SESSION sql_buffer_result = FALSE;

connect (con_int2,localhost,root,,);

connection con_int2;

SELECT @@SESSION.sql_buffer_result;
--echo 0 / FALSE Expected

SET SESSION sql_buffer_result = TRUE;

connection con_int1;

SELECT @@SESSION.sql_buffer_result;
--echo 0 / FALSE Expected

connection con_int2;

SELECT @@SESSION.sql_buffer_result;
--echo 1 / TRUE Expected

connection default;

disconnect con_int1;
disconnect con_int2;


#
# Cleanup
#
--echo ** Cleanup **

SET @@sql_buffer_result = @original_sql_buffer_result;

DROP TABLE t1;