Debian Codegraph @

mysql-test/suite/x/t/delete_sql_o.test

raw
1 ########### ../t/delete_sql.test #############
2 ### #
3 ### This test runs aims to run DELETE statement variant #
4 ### with mysqlxtest client. #
5 ### Test covers #
6 ### - delete with safe_update mode(ON/OFF) #
7 ### - delete with WHERE,LIMIT,ORDER BY clauses #
8 ### - delete on multiple table #
9 ### - delete with transaction #
10 ### - delete with partition #
11 ### #
12 ########################################################################
13 #
14
15 --echo ==============================================
16 --echo SQL DELETE SCENARIOS
17 --echo ==============================================
18 --echo
19
20 ## Preamble
21 --echo ================================================================================
22 --echo PREAMBLE
23 --echo ================================================================================
24 --source include/xplugin_preamble.inc
25 CREATE USER deletesqluser@localhost identified by 'deletesqluser';
26 GRANT ALL ON *.* TO deletesqluser@localhost;
27
28 LET $orig_sql_safe_updates = `select @@global.sql_safe_updates`;
29
30 ## TEST STARTS HERE
31 --echo ================================================================================
32 --echo TEST START
33 --echo ================================================================================
34 --write_file $MYSQL_TMP_DIR/mysqlx-delete_sql.xpl
35 -->sql
36 DROP SCHEMA if EXISTS MySQLXplugin;
37 CREATE SCHEMA MySQLXplugin DEFAULT CHARSET='utf8';
38 USE MySQLXplugin;
39
40 CREATE TABLE Categories (
41 CategoryID int NOT NULL AUTO_INCREMENT,
42 CategoryName varchar(100),
43 CategoryDescription varchar(200),
44 CategoryIMEI tinyint,
45 CategoryDecimal decimal(5,2),
46 PRIMARY key (CategoryID)
47 );
48
49 INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
50 VALUES
51 (1,'Sports','Sports related category',1,235.15),
52 (2,'Entertaiment','Entertaiment related category',2,235.15),
53 (3, 'Home','Home related category',3,235.15),
54 (4, 'Kitchen','Kitchen related category',4,235.15);
55
56 CREATE TABLE ExtraCategoryInfo (
57 ExtraCategoryInfoID int NOT NULL AUTO_INCREMENT,
58 CategoryID int NOT NULL,
59 MainParentCategoryName varchar(100),
60 PRIMARY key (ExtraCategoryInfoID)
61 );
62
63 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
64 VALUES
65 (1,1,'People'),
66 (2,2,'People'),
67 (3,2,'House'),
68 (4,3,'House');
69
70 ## SQL safe updates as 1 will allow to delete only when you use a key column in the wehere clause
71 SET SQL_SAFE_UPDATES = 1;
72
73 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID in (2,3) AND CategoryID = 2 LIMIT 1;
74 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 1;
75
76 ## Error expected because of the safe mode = 1
77 -->endsql
78 -->expecterror 1175
79 -->sql
80 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People';
81 -->endsql
82 -->expecterror 1175
83 -->sql
84 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4);
85 -->endsql
86 -->expecterror 1175
87 -->sql
88 DELETE FROM Categories WHERE CategoryIMEI = 1;
89 -->endsql
90 -->expecterror 1175
91 -->sql
92 DELETE FROM ExtraCategoryInfo WHERE EXISTS
93 (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID
94 AND ExtraCategoryInfo.MainParentCategoryName = 'People' );
95
96 SELECT * FROM ExtraCategoryInfo;
97
98 # Run with transction
99 START TRANSACTION;
100 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
101 VALUES
102 (5,4,'Hotel'),
103 (6,4,'Hotel');
104 SELECT * FROM ExtraCategoryInfo;
105 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 5;
106 SELECT * FROM ExtraCategoryInfo;
107 COMMIT;
108
109 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
110 VALUES
111 (7,1,'GROUND'),
112 (8,1,'GROUND');
113 SELECT * FROM ExtraCategoryInfo;
114 START TRANSACTION;
115 DELETE FROM ExtraCategoryInfo WHERE ExtraCategoryInfoID = 7;
116 SELECT * FROM ExtraCategoryInfo;
117 ROLLBACK;
118 SELECT * FROM ExtraCategoryInfo;
119
120 -->endsql
121 EOF
122
123 --exec $MYSQLXTEST -u deletesqluser --password='deletesqluser' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql.xpl 2>&1
124
125 --echo .
126 --echo #---------- SAFE MODE OFF ----------#
127 --echo .
128
129
130 --write_file $MYSQL_TMP_DIR/mysqlx-delete_sql_safemode_0.xpl
131 -->sql
132
133 USE MySQLXplugin;
134 ## SQL safe updates as 0 will allow to delete without the need to use a key column
135 SET SQL_SAFE_UPDATES = 0;
136
137 SELECT COUNT(*) FROM ExtraCategoryInfo;
138 DELETE FROM ExtraCategoryInfo;
139 SELECT COUNT(*) FROM ExtraCategoryInfo;
140
141 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
142 VALUES
143 (1,1,'People'),
144 (2,2,'People'),
145 (3,2,'House'),
146 (4,3,'House');
147
148 SELECT COUNT(*) FROM ExtraCategoryInfo;
149 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'People';
150 DELETE FROM ExtraCategoryInfo WHERE MainParentCategoryName = 'House' AND CategoryID in (4);
151 DELETE FROM Categories WHERE CategoryIMEI = 1;
152 SELECT COUNT(*) FROM ExtraCategoryInfo;
153 DELETE FROM ExtraCategoryInfo WHERE EXISTS
154 (SELECT * FROM Categories WHERE Categories.CategoryID = ExtraCategoryInfo.CategoryID
155 AND ExtraCategoryInfo.MainParentCategoryName = 'House' );
156 SELECT COUNT(*) FROM ExtraCategoryInfo;
157
158 # USE ORDER BY with DELETE and QUICK
159 SELECT * FROM Categories;
160 DELETE QUICK FROM Categories ORDER BY CategoryName DESC LIMIT 2;
161 SELECT * FROM Categories;
162 # LOW_PRIORITY
163 DELETE LOW_PRIORITY FROM Categories;
164 DELETE FROM ExtraCategoryInfo;
165
166 # Re-populate table data
167 INSERT INTO Categories(CategoryID, CategoryName, CategoryDescription, CategoryIMEI, CategoryDecimal)
168 VALUES
169 (1,'Sports','Sports related category',1,235.15),
170 (2,'Entertaiment','Entertaiment related category',2,235.15),
171 (3, 'Home','Home related category',3,235.15),
172 (4, 'Kitchen','Kitchen related category',4,235.15),
173 (5, 'Office','office related category',5,235.15);
174 INSERT INTO ExtraCategoryInfo(ExtraCategoryInfoID, CategoryID, MainParentCategoryName)
175 VALUES
176 (1,1,'People'),
177 (2,2,'People'),
178 (3,2,'House'),
179 (4,3,'House'),
180 (5,4,'Hotel'),
181 (6,4,'Hotel'),
182 (7,7,'Hotel'),
183 (8,8,'Hotel'),
184 (9,9,'Hotel');
185
186 ALTER TABLE ExtraCategoryInfo
187 PARTITION BY RANGE (ExtraCategoryInfoID) (
188 PARTITION p0 VALUES LESS THAN (8),
189 PARTITION p1 VALUES LESS THAN (10)
190 );
191 SELECT * FROM Categories;
192 SELECT * FROM ExtraCategoryInfo;
193 # DELETE USING PARTITION
194 DELETE FROM ExtraCategoryInfo PARTITION (p1) LIMIT 1;
195 SELECT * FROM ExtraCategoryInfo;
196 DELETE FROM ExtraCategoryInfo PARTITION (p1);
197 SELECT * FROM ExtraCategoryInfo;
198
199 # DELETE FROM MULTIPLE TABLE
200 DELETE FROM Categories,ExtraCategoryInfo USING Categories INNER JOIN ExtraCategoryInfo
201 WHERE Categories.CategoryID=ExtraCategoryInfo.CategoryID;
202
203 SELECT * FROM Categories;
204 SELECT * FROM ExtraCategoryInfo;
205
206 -->endsql
207
208 ## Cleanup
209 -->echo ================================================================================
210 -->echo CLEAN UP
211 -->echo ================================================================================
212 -->sql
213 DROP SCHEMA if EXISTS MySQLXplugin;
214 DROP USER deletesqluser@localhost;
215 SET SQL_SAFE_UPDATES = 1;
216 -->endsql
217 EOF
218
219 --exec $MYSQLXTEST -u deletesqluser --password='deletesqluser' --file=$MYSQL_TMP_DIR/mysqlx-delete_sql_safemode_0.xpl 2>&1
220
221 ## Cleanup
222 --source ../include/xplugin_cleanup.inc
223
224 --disable_query_log
225 EVAL SET global sql_safe_updates = $orig_sql_safe_updates;
226 --enable_query_log