Debian Codegraph @

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

raw
1 ==============================================
2 SQL DELETE SCENARIOS
3 ==============================================
4
5 ================================================================================
6 PREAMBLE
7 ================================================================================
8 CREATE USER deletesqluser@localhost identified by 'deletesqluser';
9 GRANT ALL ON *.* TO deletesqluser@localhost;
10 ================================================================================
11 TEST START
12 ================================================================================
13 RUN DROP SCHEMA if EXISTS MySQLXplugin
14
15 0 rows affected
16 RUN CREATE SCHEMA MySQLXplugin DEFAULT CHARSET='utf8'
17
18 1 rows affected
19 RUN USE MySQLXplugin
20
21 0 rows affected
22 RUN CREATE TABLE Categories (
23 CategoryID int NOT NULL AUTO_INCREMENT,
24 CategoryName varchar(100),
25 CategoryDescription varchar(200),
26 CategoryIMEI tinyint,
27 CategoryDecimal decimal(5,2),
28 PRIMARY key (CategoryID)
29 )
30
31 0 rows affected
32 RUN INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
33 VALUES
34 (1,'Sports','Sports related category',1,235.15),
35 (2,'Entertaiment','Entertaiment related category',2,235.15),
36 (3, 'Home','Home related category',3,235.15),
37 (4, 'Kitchen','Kitchen related category',4,235.15)
38
39 4 rows affected
40 last insert id: 4
41 Records: 4 Duplicates: 0 Warnings: 0
42 RUN CREATE TABLE ExtraCategoryInfo (
43 ExtraCategoryInfoID int NOT NULL AUTO_INCREMENT,
44 CategoryID int NOT NULL,
45 MainParentCategoryName varchar(100),
46 PRIMARY key (ExtraCategoryInfoID)
47 )
48
49 0 rows affected
50 RUN INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
51 VALUES
52 (1,1,'People'),
53 (2,2,'People'),
54 (3,2,'House'),
55 (4,3,'House')
56
57 4 rows affected
58 last insert id: 4
59 Records: 4 Duplicates: 0 Warnings: 0
60 RUN SET SQL_SAFE_UPDATES = 1
61
62 0 rows affected
63 RUN DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID in (2,3) AND CategoryID = 2 LIMIT 1
64
65 1 rows affected
66 RUN DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 1
67
68 1 rows affected
69 RUN DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People'
70 While executing DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People':
71 Got expected error: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. (code 1175)
72 RUN DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4)
73 While executing DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4):
74 Got expected error: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. (code 1175)
75 RUN DELETE FROM Categories WHERE CategoryIMEI = 1
76 While executing DELETE FROM Categories WHERE CategoryIMEI = 1:
77 Got expected error: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. (code 1175)
78 RUN DELETE FROM ExtraCategoryInfo WHERE EXISTS
79 (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID
80 AND ExtraCategoryInfo.MainParentCategoryName = 'People' )
81 While executing DELETE FROM ExtraCategoryInfo WHERE EXISTS
82 (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID
83 AND ExtraCategoryInfo.MainParentCategoryName = 'People' ):
84 Got expected error: You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column. (code 1175)
85 RUN SELECT * FROM ExtraCategoryInfo
86 ExtraCategoryInfoID CategoryID MainParentCategoryName
87 3 2 House
88 4 3 House
89 0 rows affected
90 RUN START TRANSACTION
91
92 0 rows affected
93 RUN INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
94 VALUES
95 (5,4,'Hotel'),
96 (6,4,'Hotel')
97
98 2 rows affected
99 last insert id: 6
100 Records: 2 Duplicates: 0 Warnings: 0
101 RUN SELECT * FROM ExtraCategoryInfo
102 ExtraCategoryInfoID CategoryID MainParentCategoryName
103 3 2 House
104 4 3 House
105 5 4 Hotel
106 6 4 Hotel
107 0 rows affected
108 RUN DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 5
109
110 1 rows affected
111 RUN SELECT * FROM ExtraCategoryInfo
112 ExtraCategoryInfoID CategoryID MainParentCategoryName
113 3 2 House
114 4 3 House
115 6 4 Hotel
116 0 rows affected
117 RUN COMMIT
118
119 0 rows affected
120 RUN INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
121 VALUES
122 (7,1,'GROUND'),
123 (8,1,'GROUND')
124
125 2 rows affected
126 last insert id: 8
127 Records: 2 Duplicates: 0 Warnings: 0
128 RUN SELECT * FROM ExtraCategoryInfo
129 ExtraCategoryInfoID CategoryID MainParentCategoryName
130 3 2 House
131 4 3 House
132 6 4 Hotel
133 7 1 GROUND
134 8 1 GROUND
135 0 rows affected
136 RUN START TRANSACTION
137
138 0 rows affected
139 RUN DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 7
140
141 1 rows affected
142 RUN SELECT * FROM ExtraCategoryInfo
143 ExtraCategoryInfoID CategoryID MainParentCategoryName
144 3 2 House
145 4 3 House
146 6 4 Hotel
147 8 1 GROUND
148 0 rows affected
149 RUN ROLLBACK
150
151 0 rows affected
152 RUN SELECT * FROM ExtraCategoryInfo
153 ExtraCategoryInfoID CategoryID MainParentCategoryName
154 3 2 House
155 4 3 House
156 6 4 Hotel
157 7 1 GROUND
158 8 1 GROUND
159 0 rows affected
160 Mysqlx.Ok {
161 msg: "bye!"
162 }
163 ok
164 .
165 #---------- SAFE MODE OFF ----------#
166 .
167 RUN USE MySQLXplugin
168
169 0 rows affected
170 RUN SET SQL_SAFE_UPDATES = 0
171
172 0 rows affected
173 RUN SELECT COUNT(*) FROM ExtraCategoryInfo
174 COUNT(*)
175 5
176 0 rows affected
177 RUN DELETE FROM ExtraCategoryInfo
178
179 5 rows affected
180 RUN SELECT COUNT(*) FROM ExtraCategoryInfo
181 COUNT(*)
182 0
183 0 rows affected
184 RUN INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
185 VALUES
186 (1,1,'People'),
187 (2,2,'People'),
188 (3,2,'House'),
189 (4,3,'House')
190
191 4 rows affected
192 last insert id: 4
193 Records: 4 Duplicates: 0 Warnings: 0
194 RUN SELECT COUNT(*) FROM ExtraCategoryInfo
195 COUNT(*)
196 4
197 0 rows affected
198 RUN DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People'
199
200 2 rows affected
201 RUN DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4)
202
203 0 rows affected
204 RUN DELETE FROM Categories WHERE CategoryIMEI = 1
205
206 1 rows affected
207 RUN SELECT COUNT(*) FROM ExtraCategoryInfo
208 COUNT(*)
209 2
210 0 rows affected
211 RUN DELETE FROM ExtraCategoryInfo WHERE EXISTS
212 (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID
213 AND ExtraCategoryInfo.MainParentCategoryName = 'House' )
214
215 2 rows affected
216 RUN SELECT COUNT(*) FROM ExtraCategoryInfo
217 COUNT(*)
218 0
219 0 rows affected
220 RUN SELECT * FROM Categories
221 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal
222 2 Entertaiment Entertaiment related category 2 235.15
223 3 Home Home related category 3 235.15
224 4 Kitchen Kitchen related category 4 235.15
225 0 rows affected
226 RUN DELETE QUICK FROM Categories ORDER BY CategoryName DESC LIMIT 2
227
228 2 rows affected
229 RUN SELECT * FROM Categories
230 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal
231 2 Entertaiment Entertaiment related category 2 235.15
232 0 rows affected
233 RUN DELETE LOW_PRIORITY FROM Categories
234
235 1 rows affected
236 RUN DELETE FROM ExtraCategoryInfo
237
238 0 rows affected
239 RUN INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
240 VALUES
241 (1,'Sports','Sports related category',1,235.15),
242 (2,'Entertaiment','Entertaiment related category',2,235.15),
243 (3, 'Home','Home related category',3,235.15),
244 (4, 'Kitchen','Kitchen related category',4,235.15),
245 (5, 'Office','office related category',5,235.15)
246
247 5 rows affected
248 last insert id: 5
249 Records: 5 Duplicates: 0 Warnings: 0
250 RUN INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
251 VALUES
252 (1,1,'People'),
253 (2,2,'People'),
254 (3,2,'House'),
255 (4,3,'House'),
256 (5,4,'Hotel'),
257 (6,4,'Hotel'),
258 (7,7,'Hotel'),
259 (8,8,'Hotel'),
260 (9,9,'Hotel')
261
262 9 rows affected
263 last insert id: 9
264 Records: 9 Duplicates: 0 Warnings: 0
265 RUN ALTER TABLE ExtraCategoryInfo
266 PARTITION BY RANGE (ExtraCategoryInfoID) (
267 PARTITION p0 VALUES LESS THAN (8),
268 PARTITION p1 VALUES LESS THAN (10)
269 )
270
271 9 rows affected
272 Records: 9 Duplicates: 0 Warnings: 0
273 RUN SELECT * FROM Categories
274 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal
275 1 Sports Sports related category 1 235.15
276 2 Entertaiment Entertaiment related category 2 235.15
277 3 Home Home related category 3 235.15
278 4 Kitchen Kitchen related category 4 235.15
279 5 Office office related category 5 235.15
280 0 rows affected
281 RUN SELECT * FROM ExtraCategoryInfo
282 ExtraCategoryInfoID CategoryID MainParentCategoryName
283 1 1 People
284 2 2 People
285 3 2 House
286 4 3 House
287 5 4 Hotel
288 6 4 Hotel
289 7 7 Hotel
290 8 8 Hotel
291 9 9 Hotel
292 0 rows affected
293 RUN DELETE FROM ExtraCategoryInfo PARTITION (p1) LIMIT 1
294
295 1 rows affected
296 RUN SELECT * FROM ExtraCategoryInfo
297 ExtraCategoryInfoID CategoryID MainParentCategoryName
298 1 1 People
299 2 2 People
300 3 2 House
301 4 3 House
302 5 4 Hotel
303 6 4 Hotel
304 7 7 Hotel
305 9 9 Hotel
306 0 rows affected
307 RUN DELETE FROM ExtraCategoryInfo PARTITION (p1)
308
309 1 rows affected
310 RUN SELECT * FROM ExtraCategoryInfo
311 ExtraCategoryInfoID CategoryID MainParentCategoryName
312 1 1 People
313 2 2 People
314 3 2 House
315 4 3 House
316 5 4 Hotel
317 6 4 Hotel
318 7 7 Hotel
319 0 rows affected
320 RUN DELETE FROM Categories,ExtraCategoryInfo USING Categories INNER JOIN ExtraCategoryInfo
321 WHERE Categories.CategoryID=ExtraCategoryInfo.CategoryID
322
323 10 rows affected
324 RUN SELECT * FROM Categories
325 CategoryID CategoryName CategoryDescription CategoryIMEI CategoryDecimal
326 5 Office office related category 5 235.15
327 0 rows affected
328 RUN SELECT * FROM ExtraCategoryInfo
329 ExtraCategoryInfoID CategoryID MainParentCategoryName
330 7 7 Hotel
331 0 rows affected
332 ================================================================================
333 CLEAN UP
334 ================================================================================
335 RUN DROP SCHEMA if EXISTS MySQLXplugin
336
337 2 rows affected
338 RUN DROP USER deletesqluser@localhost
339
340 0 rows affected
341 RUN SET SQL_SAFE_UPDATES = 1
342
343 0 rows affected
344 Mysqlx.Ok {
345 msg: "bye!"
346 }
347 ok