Debian Codegraph @

mysql-test/suite/x/r/flow_resultset_stmtexecute_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 RUN DELETE FROM xtest.xtable WHERE prio=4 or prio=3
56 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:2]
57 [ASSERT Mysqlx.Sql.StmtExecuteOk]
58
59 #
60 # I.2
61 RUN UPDATE xtest.xtable SET prio=prio-1
62 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:3]
63 [ASSERT notice session state change - PRODUCED_MESSAGE, type: V_STRING]
64 [ASSERT Mysqlx.Sql.StmtExecuteOk]
65
66 #
67 # I.3
68 RUN INSERT INTO xtest.xtable(phrase, prio) VALUE('Bob has a dog 2', 3)
69 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
70 [ASSERT Mysqlx.Sql.StmtExecuteOk]
71
72 #
73 # I.4
74 RUN INSERT INTO xtest.xtable_with_pk(description) VALUE('MyCustomRow')
75 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:1]
76 [ASSERT notice session state change - GENERATED_INSERT_ID, v_unsigned_int: 2]
77 [ASSERT Mysqlx.Sql.StmtExecuteOk]
78
79 #
80 # I.5
81 RUN SELECT phrase FROM xtest.xtable
82 [ASSERT Mysqlx.Resultset.ColumnMetaData]
83 [ASSERT Mysqlx.Resultset.Row]
84 [ASSERT Mysqlx.Resultset.Row]
85 [ASSERT Mysqlx.Resultset.Row]
86 [ASSERT Mysqlx.Resultset.Row]
87 [ASSERT Mysqlx.Resultset.FetchDone]
88 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
89 [ASSERT Mysqlx.Sql.StmtExecuteOk]
90
91 #
92 # II.1
93 RUN CALL two_resultsets
94 [ASSERT Mysqlx.Resultset.ColumnMetaData]
95 [ASSERT Mysqlx.Resultset.Row]
96 [ASSERT Mysqlx.Resultset.Row]
97 [ASSERT Mysqlx.Resultset.Row]
98 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
99 [ASSERT Mysqlx.Resultset.ColumnMetaData]
100 [ASSERT Mysqlx.Resultset.Row]
101 [ASSERT Mysqlx.Resultset.Row]
102 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
103 [ASSERT Mysqlx.Resultset.FetchDone]
104 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
105 [ASSERT Mysqlx.Sql.StmtExecuteOk]
106
107 #
108 # II.2
109 RUN CALL three_resultsets
110 [ASSERT Mysqlx.Resultset.ColumnMetaData]
111 [ASSERT Mysqlx.Resultset.Row]
112 [ASSERT Mysqlx.Resultset.Row]
113 [ASSERT Mysqlx.Resultset.Row]
114 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
115 [ASSERT Mysqlx.Resultset.ColumnMetaData]
116 [ASSERT Mysqlx.Resultset.Row]
117 [ASSERT Mysqlx.Resultset.Row]
118 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
119 [ASSERT Mysqlx.Resultset.ColumnMetaData]
120 [ASSERT Mysqlx.Resultset.Row]
121 [ASSERT Mysqlx.Resultset.Row]
122 [ASSERT Mysqlx.Resultset.Row]
123 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
124 [ASSERT Mysqlx.Resultset.FetchDone]
125 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
126 [ASSERT Mysqlx.Sql.StmtExecuteOk]
127
128 #
129 # III.1
130 RUN SELECT 1/prio as col1 FROM xtest.xtable
131 [ASSERT Mysqlx.Resultset.ColumnMetaData]
132 [ASSERT Mysqlx.Resultset.Row]
133 [ASSERT Mysqlx.Resultset.Row]
134 [ASSERT Mysqlx.Resultset.Row]
135 [ASSERT Mysqlx.Resultset.Row]
136 [ASSERT Mysqlx.Resultset.FetchDone]
137 [ASSERT notice warning - WARNING, 1365]
138 [ASSERT notice warning - WARNING, 1365]
139 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
140 [ASSERT Mysqlx.Sql.StmtExecuteOk]
141
142 #
143 # III.2
144 RUN CALL two_resultsets_with_errors
145 [ASSERT Mysqlx.Resultset.ColumnMetaData]
146 [ASSERT Mysqlx.Resultset.Row]
147 [ASSERT Mysqlx.Resultset.Row]
148 [ASSERT Mysqlx.Resultset.Row]
149 [ASSERT Mysqlx.Resultset.Row]
150 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
151 [ASSERT Mysqlx.Resultset.ColumnMetaData]
152 [ASSERT Mysqlx.Resultset.Row]
153 [ASSERT Mysqlx.Resultset.Row]
154 [ASSERT Mysqlx.Resultset.Row]
155 [ASSERT Mysqlx.Resultset.Row]
156 [ASSERT Mysqlx.Resultset.FetchDoneMoreResultsets]
157 [ASSERT Mysqlx.Resultset.FetchDone]
158 [ASSERT notice warning - WARNING, 1365]
159 [ASSERT notice warning - WARNING, 1365]
160 [ASSERT notice session state change - ROWS_AFFECTED, v_unsigned_int:0]
161 [ASSERT Mysqlx.Sql.StmtExecuteOk]
162 Mysqlx.Ok {
163 msg: "bye!"
164 }
165 ok