Debian Codegraph @

mysql-test/suite/x/t/multiple_resultsets.test

raw
1 ## Test of multi-resultsets and procedures output-parameters (part of WL9270).
2
3 ## Preamble
4 --source include/xplugin_preamble.inc
5 --source include/xplugin_create_user.inc
6
7 ##
8 ## Purpose of this test is:
9 #
10 # * validating out-params are transfered only in case of statements
11 # prepared with Mysqlx.Prepare.Prepare
12 # * validate that out-variables are set in case SQL prepare and SQL execution
13 #
14 # The out-param behavior depends on method(way) how the SP was executed:
15 #
16 # |Method |Sets out-params to user variables |Transfers out-params through X Protocol |
17 # |-----------------|-----------------------------------|----------------------------------------|
18 # |Direct SQL call |yes |no |
19 # |SQL prepare call |yes |no |
20 # |MySQLX prep call |no |yes |
21 #
22 # Please consider that macro 'Test_assert_user_variable' in some test executions is going to
23 # ignore second parameter and use in its place '-1'.
24 #
25
26 ## Test starts here
27 --write_file $MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl
28
29 import assert_variable.macro;
30 import %IMPORT_FILE%;
31
32 callmacro Test_prepare_and_execute_proc_arg0 `empty`();
33 callmacro Test_prepare_and_execute_proc_arg0 one_results_set();
34 callmacro Test_prepare_and_execute_proc_arg0 three_results_sets();
35
36 callmacro Test_reset_variables;
37 callmacro Test_prepare_and_execute_proc_arg1 out_param_no_select(@in_val,?) @res;
38 callmacro Test_assert_user_variable res 10;
39
40 callmacro Test_prepare_and_execute_proc_arg2 out_params_with_select(?,?) @min_val @max_val;
41 callmacro Test_assert_user_variable min_val 1979-11-24 00:00:00;
42 callmacro Test_assert_user_variable max_val 2013-06-15 00:00:00;
43
44 callmacro Test_prepare_and_execute_proc_arg0 update_no_select();
45 callmacro Test_prepare_and_execute_proc_arg0 update_and_select();
46 callmacro Test_prepare_and_execute_proc_arg1 update_and_out_param(?) @max_val;
47 callmacro Test_assert_user_variable max_val 6;
48 callmacro Test_prepare_and_execute_proc_arg1 update_and_out_param_and_select(?) @max_val;
49 callmacro Test_assert_user_variable max_val 12;
50
51 callmacro Test_prepare_and_execute_proc_arg0 insert_no_select();
52 callmacro Test_prepare_and_execute_proc_arg0 insert_and_select();
53 callmacro Test_prepare_and_execute_proc_arg1 insert_and_out_param(?) @max_val;
54 callmacro Test_assert_user_variable max_val 9999;
55 callmacro Test_prepare_and_execute_proc_arg1 insert_and_out_param_and_select(?) @max_val;
56 callmacro Test_assert_user_variable max_val 19999;
57
58 callmacro Test_prepare_and_execute_proc_arg0 error() -->expecterror ER_NO_SUCH_TABLE;
59 callmacro Test_prepare_and_execute_proc_arg0 select_and_error() -->expecterror ER_NO_SUCH_TABLE;
60
61 callmacro Test_reset_variables;
62 callmacro Test_prepare_and_execute_proc_arg1 out_param_and_error(?) @min_val -->expecterror ER_NO_SUCH_TABLE;
63 callmacro Test_assert_user_variable min_val -1;
64
65 callmacro Test_prepare_and_execute_proc_arg1 select_out_param_and_error(?) @min_val -->expecterror ER_NO_SUCH_TABLE;
66 callmacro Test_assert_user_variable min_val -1;
67
68 callmacro Test_prepare_and_execute_proc_arg0 with_warning() -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE;
69
70 callmacro Test_prepare_and_execute_proc_arg0 select_and_warning() -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE;
71 callmacro Test_prepare_and_execute_proc_arg1 out_param_and_warning(?) @vavg -->expectwarnings ER_WARN_DATA_OUT_OF_RANGE;
72 SELECT @vavg;
73
74 callmacro Test_prepare_and_execute_proc_arg0 select_out_param_and_warning(@min_val) -->expectwarnings WARN_DATA_TRUNCATED;
75 callmacro Assert_user_variable min_val 3;
76
77 EOF
78
79 SET @@sql_mode = "";
80 --let $STORED_PROC_DB_NAME=xtest
81
82 --echo
83 --echo
84 --echo ## I. Validate direct SP calls that generate multiple-results and uses out-params
85 --echo #
86 --echo # 1. Assert that result-file doesn't contain out-params inside the resultsets.
87 --echo # 2. Assert that out-params set their assigned user variables
88 --echo
89 --echo ## II. Validate SQL prepared SP calls that generate multiple-results and uses out-params
90 --echo #
91 --echo # 1. Assert that result-file doesn't contain out-params inside the resultsets.
92 --echo # 2. Assert that out-params set their assigned user variables
93 --echo
94 --echo ## III. Validate Mysqlx prepared SP calls that generate multiple-results and uses out-params
95 --echo #
96 --echo # 1. Assert that result-file contain out-params inside the resultsets.
97 --echo # 2. Assert that out-params don't set their assigned user variables
98 --echo
99
100 --echo
101 --echo #
102 --echo # I.
103 --source ../include/stored_procedures.inc
104 exec $MYSQLXTEST
105 -ux_root --password=''
106 --schema=$STORED_PROC_DB_NAME
107 -v%IMPORT_FILE%=test_multiple_resultsets_sql.macro
108 --file=$MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl 2>&1;
109
110 --echo
111 --echo #
112 --echo # II.
113 eval DROP SCHEMA IF EXISTS $STORED_PROC_DB_NAME;
114 source ../include/stored_procedures.inc;
115 exec $MYSQLXTEST
116 -ux_root --password=''
117 --schema=$STORED_PROC_DB_NAME
118 -v%IMPORT_FILE%=test_multiple_resultsets_sql_prepare.macro
119 --file=$MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl 2>&1;
120
121 --echo
122 --echo #
123 --echo # III.
124 eval DROP SCHEMA IF EXISTS $STORED_PROC_DB_NAME;
125 source ../include/stored_procedures.inc;
126 exec $MYSQLXTEST
127 -ux_root --password=''
128 --schema=$STORED_PROC_DB_NAME
129 -v%IMPORT_FILE%=test_multiple_resultsets_mysqlx_prepare.macro
130 --file=$MYSQL_TMP_DIR/multiple_resultsets_and_out_params.xpl 2>&1;
131
132 ## Cleanup
133 eval DROP SCHEMA IF EXISTS $STORED_PROC_DB_NAME;
134 --source ../include/xplugin_cleanup.inc