Debian Codegraph @

mysql-test/suite/innodb_gis/t/repeatable_spatial.test

raw
1 # *****************************************************************
2 # Test spatial index with isolation level Repeatable Read on DML
3 # This will also test the no phantom rows happens
4 # Check the COUNT(*) and SELECT results before & after COMMIT in
5 # the last con1 statment
6 # Test at con1: Global Repeatable Read con2 : Sess Read Committed
7 # Test at con1: Sess Repeatable Read con2 : Sess Read Committed
8 # *****************************************************************
9
10 # Test Repeatable Read & Read committed at Global, Session levels
11 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL SRID 0,
12 c3 LINESTRING NOT NULL SRID 0,c4 POLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
13 ENGINE=InnoDB;
14
15 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
16
17 --error ER_WRONG_USAGE
18 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
19 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
20
21 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
22
23 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
24
25 INSERT INTO tab(c1,c2,c3,c4,c5)
26 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
27 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
28 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
29
30 # 1 record is expected
31 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab;
32
33 # Record count should be 1
34 SELECT COUNT(*) FROM tab;
35
36 --echo "In connection 1"
37 connect (con1,localhost,root,,);
38 connection con1;
39
40 SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
41
42 SELECT @@transaction_isolation;
43
44 # Record count should be 1
45 SELECT COUNT(*) FROM tab;
46
47 # 1 record is expected
48 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab;
49
50 --echo "In connection 2"
51 connect (con2,localhost,root,,);
52 connection con2;
53
54 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
55
56 SELECT @@transaction_isolation;
57
58 START TRANSACTION;
59
60 INSERT INTO tab(c1,c2,c3,c4,c5)
61 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
62 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
63 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
64
65 SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))');
66 SET @g2 = ST_GeomFromText('POINT(10 10)');
67
68 # Record should be updated
69 UPDATE tab SET C5 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')
70 WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c2, @g2);
71
72 --echo "In connection 1"
73 connection con1;
74
75 SET @g3 = ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))');
76
77 # No record is expected (No phantom row)
78 SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g3);
79
80 # Record count should be 1
81 SELECT COUNT(*) FROM tab;
82
83 START TRANSACTION;
84
85 # Record count should be 1
86 SELECT COUNT(*) FROM tab;
87
88 --echo "In connection 2"
89 connection con2;
90
91 # Record count should be 2
92 SELECT COUNT(*) FROM tab;
93
94 COMMIT;
95
96 disconnect con2;
97 --source include/wait_until_disconnected.inc
98
99 --echo "In connection 1"
100 connection con1;
101
102 # Record count should be 1
103 SELECT COUNT(*) FROM tab;
104
105 SET @g4 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))');
106
107 # No records are expected (No phantom row)
108 SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4);
109
110 COMMIT;
111
112 # The updated record is expected
113 SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4);
114
115 # Record count should be 2
116 SELECT COUNT(*) FROM tab;
117
118 # 2 records are expected
119 SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab;
120
121 disconnect con1;
122 --source include/wait_until_disconnected.inc
123
124 --connection default
125 DROP TABLE tab;
126
127 # Test Repeatable Read & Read committed at Session levels
128 CREATE TABLE tab(c1 int NOT NULL PRIMARY KEY,c2 POINT NOT NULL SRID 0,
129 c3 LINESTRING NOT NULL SRID 0,c4 POLYGON NOT NULL SRID 0,c5 GEOMETRY NOT NULL SRID 0)
130 ENGINE=InnoDB;
131
132 ALTER TABLE tab ADD SPATIAL INDEX idx2(c2);
133
134 --error ER_WRONG_USAGE
135 ALTER TABLE tab ADD SPATIAL KEY idx3(c3 DESC);
136 ALTER TABLE tab ADD SPATIAL KEY idx3(c3);
137
138 ALTER TABLE tab ADD SPATIAL INDEX idx4(c4) COMMENT 'testing spatial index on Polygon';
139
140 ALTER TABLE tab ADD SPATIAL KEY idx5(c5) COMMENT 'testing spatial index on Geometry';
141
142 INSERT INTO tab(c1,c2,c3,c4,c5)
143 VALUES(1,ST_GeomFromText('POINT(10 10)'),ST_GeomFromText('LINESTRING(5 5,20 20,30 30)'),
144 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'),
145 ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))'));
146
147 # 1 record is expected
148 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab;
149
150 # Record count should be 1
151 SELECT COUNT(*) FROM tab;
152
153 --echo "In connection 1"
154 connect (con1,localhost,root,,);
155 connection con1;
156
157 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
158
159 SELECT @@transaction_isolation;
160
161 # Record count should be 1
162 SELECT COUNT(*) FROM tab;
163
164 # 1 record is expected
165 SELECT c1,ST_AsText(c2),ST_AsText(c3),ST_AsText(c4),ST_AsText(c5) FROM tab;
166
167 --echo "In connection 2"
168 connect (con2,localhost,root,,);
169 connection con2;
170
171 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
172
173 SELECT @@transaction_isolation;
174
175 START TRANSACTION;
176
177 INSERT INTO tab(c1,c2,c3,c4,c5)
178 VALUES(2,ST_GeomFromText('POINT(20 20)'),ST_GeomFromText('LINESTRING(20 20,30 30,40 40)'),
179 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'),
180 ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))'));
181
182 SET @g1 = ST_GeomFromText('POLYGON((30 30,40 40,50 50,30 50,30 40,30 30))');
183 SET @g2 = ST_GeomFromText('POINT(10 10)');
184
185 # Record should be updated
186 UPDATE tab SET C5 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))')
187 WHERE MBREquals(tab.c4, @g1) AND MBREquals(tab.c2, @g2);
188
189 --echo "In connection 1"
190 connection con1;
191
192 SET @g3 = ST_GeomFromText('POLYGON((40 50,40 70,50 100,70 100,80 80,70 50,40 50))');
193
194 # No record is expected (No phantom row)
195 SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g3);
196
197 # Record count should be 1
198 SELECT COUNT(*) FROM tab;
199
200 START TRANSACTION;
201
202 # Record count should be 1
203 SELECT COUNT(*) FROM tab;
204
205 --echo "In connection 2"
206 connection con2;
207
208 # Record count should be 2
209 SELECT COUNT(*) FROM tab;
210
211 COMMIT;
212
213 disconnect con2;
214 --source include/wait_until_disconnected.inc
215
216 --echo "In connection 1"
217 connection con1;
218
219 # Record count should be 1
220 SELECT COUNT(*) FROM tab;
221
222 SET @g4 = ST_GeomFromText('POLYGON((300 300,400 400,500 500,300 500,300 400,300 300))');
223
224 # No records are expected (No phantom row)
225 SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4);
226
227 COMMIT;
228
229 # The updated record is expected
230 SELECT ST_AsText(c5) FROM tab WHERE MBREquals(tab.c5, @g4);
231
232 # Record count should be 2
233 SELECT COUNT(*) FROM tab;
234
235 # 2 records are expected
236 SELECT c1,ST_AsText(c2),ST_AsText(c4),ST_AsText(c5) FROM tab;
237
238 disconnect con1;
239 --source include/wait_until_disconnected.inc
240
241 --connection default
242 DROP TABLE tab;