Debian Codegraph @

mysql-test/suite/x/include/test_flow_resultset_prepexecute_sql.inc

raw
1
2 --write_file $MYSQL_TMP_DIR/resultset.xpl
3 -->import assert_messages.macro
4 -->import assert_notices.macro
5
6 -->varlet %ID% 1
7 -->macro Prepare_and_execute_wo_res %SQL%
8 -->noquery_result
9 -->quiet
10 -->echo [PREPARE - %SQL%]
11 Mysqlx.Prepare.Prepare {
12 stmt_id: %ID%
13 stmt: {
14 type: STMT
15 stmt_execute: {
16 stmt: "%SQL%"
17 }
18 }
19 }
20 -->recvok
21 -->echo [Execute]
22 Mysqlx.Prepare.Execute {
23 stmt_id: %ID%
24 }
25 -->varinc %ID% 1
26 -->query_result
27 -->noquiet
28 -->endmacro
29
30 ##
31 ## Purpose of this test is:
32 #
33 # * validating that resultsets are in align to the UML flow:
34 #
35 # ...
36 # loop has more resultsets or not at end of fetch
37 # group resultset
38 # loop has more columns
39 # server --> client: ColumnMetaData
40 # end
41 # loop has more rows
42 # server --> client: Row
43 # end
44 # end
45 # alt has more resultsets
46 # server --> client: FetchDoneMoreResultsets
47 # end
48 # end
49 # loop has more OUT-paramsets or not at end of fetch
50 # server --> client: FetchDoneMoreOutParams
51 # group resultset
52 # loop has more columns
53 # server --> client: ColumnMetaData
54 # end
55 # loop has more rows
56 # server --> client: Row
57 # end
58 # end
59 # end
60 # alt at end of all resultsets
61 # server --> client: FetchDone
62 # else cursor is opened
63 # server --> client: FetchSuspended
64 # end
65 # ...
66 #
67 # * expecting following notices for SQL prepared-statement:
68 #
69 # |Notice\Stmt |Select |Insert |Update |Delete |
70 # |-----------------------|-------|-------|-------|-------|
71 # |ROWS_AFFECTED |X |X |X |X |
72 # |PRODUCED_MESSAGE | | |X | |
73 # |GENERATED_INSERT_ID | |X(PK) | | |
74 # |GENERATED_DOCUMENT_IDS | | | | |
75 #
76 -->echo
77 -->echo
78 -->echo ## I. Validate simple resultsets
79 -->echo #
80 -->echo # 1. Assert resultset for delete queries
81 -->echo # 2. Assert resultset for update queries
82 -->echo # 3. Assert resultset for insert queries (no auto_inc column)
83 -->echo # 4. Assert resultset for insert queries (has auto_inc column)
84 -->echo # 5. Assert resultset for select queries
85 -->echo
86 -->echo ## II. Validate simple multi-resultsets
87 -->echo #
88 -->echo # 1. Assert two resultsets for select queries
89 -->echo # 2. Assert three resultsets for select queries
90 -->echo
91 -->echo ## III. Validate simple multi/resultsets with warnings
92 -->echo #
93 -->echo # 1. Assert one resultsets for select queries
94 -->echo # 2. Assert two resultsets for select queries
95
96 -->echo
97 -->echo #
98 -->echo # I.1
99 callmacro Prepare_and_execute_wo_res DELETE FROM xtest.xtable WHERE prio=4 or prio=3;
100 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:2;
101 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
102
103 -->echo
104 -->echo #
105 -->echo # I.2
106 callmacro Prepare_and_execute_wo_res UPDATE xtest.xtable SET prio=prio-1;
107 callmacro Assert_notice_session_state PRODUCED_MESSAGE type: V_STRING;
108 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:3;
109 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
110
111 -->echo
112 -->echo #
113 -->echo # I.3
114 callmacro Prepare_and_execute_wo_res INSERT INTO xtest.xtable(phrase, prio) VALUE('Bob has a dog 2', 3);
115 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
116 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
117
118 -->echo
119 -->echo #
120 -->echo # I.4
121 callmacro Prepare_and_execute_wo_res INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow');
122 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:1;
123 callmacro Assert_notice_session_state GENERATED_INSERT_ID v_unsigned_int: 2;
124 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
125
126 -->echo
127 -->echo #
128 -->echo # I.5
129 callmacro Prepare_and_execute_wo_res SELECT phrase FROM xtest.xtable;
130 callmacro Assert_metadata BYTES "phrase";
131 callmacro Assert_rows 4;
132 callmacro Assert_message Mysqlx.Resultset.FetchDone;
133 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
134 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
135
136
137 -->echo
138 -->echo #
139 -->echo # II.1
140 callmacro Prepare_and_execute_wo_res CALL two_resultsets;
141 callmacro Assert_metadata BYTES "phrase";
142 callmacro Assert_rows 3;
143 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
144 callmacro Assert_metadata SINT "prio";
145 callmacro Assert_rows 2;
146 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
147 callmacro Assert_message Mysqlx.Resultset.FetchDone;
148 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
149 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
150
151 -->echo
152 -->echo #
153 -->echo # II.2
154 callmacro Prepare_and_execute_wo_res CALL three_resultsets;
155 callmacro Assert_metadata BYTES "phrase";
156 callmacro Assert_rows 3;
157 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
158 callmacro Assert_metadata SINT "prio";
159 callmacro Assert_rows 2;
160 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
161 callmacro Assert_metadata SINT "prio";
162 callmacro Assert_rows 3;
163 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
164 callmacro Assert_message Mysqlx.Resultset.FetchDone;
165 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
166 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
167
168
169 -->echo
170 -->echo #
171 -->echo # III.1
172 callmacro Prepare_and_execute_wo_res SELECT 1/prio as col1 FROM xtest.xtable;
173 callmacro Assert_metadata DECIMAL "col1";
174 callmacro Assert_rows 4;
175 callmacro Assert_message Mysqlx.Resultset.FetchDone;
176 ## ER_DIVISION_BY_ZERO == 1365
177 callmacro Assert_notice_warning WARNING 1365;
178 callmacro Assert_notice_warning WARNING 1365;
179 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
180 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
181
182 -->echo
183 -->echo #
184 -->echo # III.2
185 callmacro Prepare_and_execute_wo_res CALL two_resultsets_with_errors;
186 callmacro Assert_metadata DECIMAL "col1";
187 callmacro Assert_rows 4;
188 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
189 callmacro Assert_metadata DECIMAL "col1";
190 callmacro Assert_rows 4;
191 callmacro Assert_message Mysqlx.Resultset.FetchDoneMoreResultsets;
192 callmacro Assert_message Mysqlx.Resultset.FetchDone;
193 ## ER_DIVISION_BY_ZERO == 1365
194 callmacro Assert_notice_warning WARNING 1365;
195 callmacro Assert_notice_warning WARNING 1365;
196 callmacro Assert_notice_session_state ROWS_AFFECTED v_unsigned_int:0;
197 callmacro Assert_message Mysqlx.Sql.StmtExecuteOk;
198
199 EOF
200
201
202 source sample_tables.inc;
203
204 DELIMITER //;
205
206 CREATE PROCEDURE two_resultsets()
207 BEGIN
208 SELECT phrase FROM xtable limit 3;
209 SELECT prio FROM xtable limit 2;
210 END //
211
212 CREATE PROCEDURE two_resultsets_with_errors()
213 BEGIN
214 SELECT 1/prio as col1 FROM xtable;
215 SELECT 1/prio as col1 FROM xtable;
216 END //
217
218 CREATE PROCEDURE three_resultsets()
219 BEGIN
220 SELECT phrase FROM xtable limit 3;
221 SELECT prio FROM xtable limit 2;
222 SELECT prio FROM xtable ORDER BY prio limit 3;
223 END //
224
225 DELIMITER ;//