Debian Codegraph @

mysql-test/suite/x/r/flow_resultset_prepexecute_sql.result

raw
1 CREATE SCHEMA IF NOT EXISTS xtest;
2 USE xtest;
3 CREATE PROCEDURE recreate_tables()
4 BEGIN
5 CREATE TABLE IF NOT EXISTS xtable (phrase VARCHAR(30), prio INTEGER);
6 TRUNCATE TABLE xtable;
7 INSERT INTO xtable (phrase, prio) VALUES ('Alice has a cat', 1);
8 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 1', 2);
9 INSERT INTO xtable (phrase, prio) VALUES ('Peppa has a teddy', 1);
10 INSERT INTO xtable (phrase, prio) VALUES ('Bob has a dog 2', 3);
11 INSERT INTO xtable (phrase, prio) VALUES ('Row to delete', 4);
12 CREATE TABLE IF NOT EXISTS xtable_with_pk (p_id INTEGER AUTO_INCREMENT PRIMARY KEY, description varchar(40));
13 TRUNCATE TABLE xtable_with_pk;
14 INSERT INTO xtable_with_pk (description) VALUES ('MyRow');
15 END //
16 CREATE PROCEDURE two_resultsets()
17 BEGIN
18 SELECT phrase FROM xtable limit 3;
19 SELECT prio FROM xtable limit 2;
20 END //
21 CREATE PROCEDURE two_resultsets_with_errors()
22 BEGIN
23 SELECT 1/prio as col1 FROM xtable;
24 SELECT 1/prio as col1 FROM xtable;
25 END //
26 CREATE PROCEDURE three_resultsets()
27 BEGIN
28 SELECT phrase FROM xtable limit 3;
29 SELECT prio FROM xtable limit 2;
30 SELECT prio FROM xtable ORDER BY prio limit 3;
31 END //
32 CALL recreate_tables();
33
34
35 ## I. Validate simple resultsets
36 #
37 # 1. Assert resultset for delete queries
38 # 2. Assert resultset for update queries
39 # 3. Assert resultset for insert queries (no auto_inc column)
40 # 4. Assert resultset for insert queries (has auto_inc column)
41 # 5. Assert resultset for select queries
42
43 ## II. Validate simple multi-resultsets
44 #
45 # 1. Assert two resultsets for select queries
46 # 2. Assert three resultsets for select queries
47
48 ## III. Validate simple multi/resultsets with warnings
49 #
50 # 1. Assert one resultsets for select queries
51 # 2. Assert two resultsets for select queries
52
53 #
54 # I.1
55 [PREPARE - DELETE FROM xtest.xtable WHERE prio=4 or prio=3]
56 RUN recvok
57 [Execute]
58 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:2]
59 [ASSERT Mysqlx.Sql.StmtExecuteOk]
60
61 #
62 # I.2
63 [PREPARE - UPDATE xtest.xtable SET prio=prio-1]
64 RUN recvok
65 [Execute]
66 [ASSERT notice session state change - PRODUCED_MESSAGE, type: V_STRING]
67 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:3]
68 [ASSERT Mysqlx.Sql.StmtExecuteOk]
69
70 #
71 # I.3
72 [PREPARE - INSERT INTO xtest.xtable(phrase, prio) VALUE('Bob has a dog 2', 3)]
73 RUN recvok
74 [Execute]
75 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
76 [ASSERT Mysqlx.Sql.StmtExecuteOk]
77
78 #
79 # I.4
80 [PREPARE - INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow')]
81 RUN recvok
82 [Execute]
83 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
84 [ASSERT notice session state change - GENERATED_INSERT_ID, v_unsigned_int: 2]
85 [ASSERT Mysqlx.Sql.StmtExecuteOk]
86
87 #
88 # I.5
89 [PREPARE - SELECT phrase FROM xtest.xtable]
90 RUN recvok
91 [Execute]
92 [ASSERT Mysqlx.Resultset.ColumnMetaData]
93 [ASSERT Mysqlx.Resultset.Row]
94 [ASSERT Mysqlx.Resultset.Row]
95 [ASSERT Mysqlx.Resultset.Row]
96 [ASSERT Mysqlx.Resultset.Row]
97 [ASSERT Mysqlx.Resultset.FetchDone]
98 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
99 [ASSERT Mysqlx.Sql.StmtExecuteOk]
100
101 #
102 # II.1
103 [PREPARE - CALL two_resultsets]
104 RUN recvok
105 [Execute]
106 [ASSERT Mysqlx.Resultset.ColumnMetaData]
107 [ASSERT Mysqlx.Resultset.Row]
108 [ASSERT Mysqlx.Resultset.Row]
109 [ASSERT Mysqlx.Resultset.Row]
110 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
111 [ASSERT Mysqlx.Resultset.ColumnMetaData]
112 [ASSERT Mysqlx.Resultset.Row]
113 [ASSERT Mysqlx.Resultset.Row]
114 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
115 [ASSERT Mysqlx.Resultset.FetchDone]
116 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
117 [ASSERT Mysqlx.Sql.StmtExecuteOk]
118
119 #
120 # II.2
121 [PREPARE - CALL three_resultsets]
122 RUN recvok
123 [Execute]
124 [ASSERT Mysqlx.Resultset.ColumnMetaData]
125 [ASSERT Mysqlx.Resultset.Row]
126 [ASSERT Mysqlx.Resultset.Row]
127 [ASSERT Mysqlx.Resultset.Row]
128 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
129 [ASSERT Mysqlx.Resultset.ColumnMetaData]
130 [ASSERT Mysqlx.Resultset.Row]
131 [ASSERT Mysqlx.Resultset.Row]
132 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
133 [ASSERT Mysqlx.Resultset.ColumnMetaData]
134 [ASSERT Mysqlx.Resultset.Row]
135 [ASSERT Mysqlx.Resultset.Row]
136 [ASSERT Mysqlx.Resultset.Row]
137 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
138 [ASSERT Mysqlx.Resultset.FetchDone]
139 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
140 [ASSERT Mysqlx.Sql.StmtExecuteOk]
141
142 #
143 # III.1
144 [PREPARE - SELECT 1/prio as col1 FROM xtest.xtable]
145 RUN recvok
146 [Execute]
147 [ASSERT Mysqlx.Resultset.ColumnMetaData]
148 [ASSERT Mysqlx.Resultset.Row]
149 [ASSERT Mysqlx.Resultset.Row]
150 [ASSERT Mysqlx.Resultset.Row]
151 [ASSERT Mysqlx.Resultset.Row]
152 [ASSERT Mysqlx.Resultset.FetchDone]
153 [ASSERT notice warning - WARNING, 1365]
154 [ASSERT notice warning - WARNING, 1365]
155 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
156 [ASSERT Mysqlx.Sql.StmtExecuteOk]
157
158 #
159 # III.2
160 [PREPARE - CALL two_resultsets_with_errors]
161 RUN recvok
162 [Execute]
163 [ASSERT Mysqlx.Resultset.ColumnMetaData]
164 [ASSERT Mysqlx.Resultset.Row]
165 [ASSERT Mysqlx.Resultset.Row]
166 [ASSERT Mysqlx.Resultset.Row]
167 [ASSERT Mysqlx.Resultset.Row]
168 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
169 [ASSERT Mysqlx.Resultset.ColumnMetaData]
170 [ASSERT Mysqlx.Resultset.Row]
171 [ASSERT Mysqlx.Resultset.Row]
172 [ASSERT Mysqlx.Resultset.Row]
173 [ASSERT Mysqlx.Resultset.Row]
174 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
175 [ASSERT Mysqlx.Resultset.FetchDone]
176 [ASSERT notice warning - WARNING, 1365]
177 [ASSERT notice warning - WARNING, 1365]
178 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
179 [ASSERT Mysqlx.Sql.StmtExecuteOk]
180 Mysqlx.Ok {
181 msg: "bye!"
182 }
183 ok