Debian Codegraph @

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

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