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