Debian Codegraph @

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

raw
1 --source include/have_innodb.inc
2 --source include/have_geometry.inc
3 --source include/innodb_page_size.inc
4
5 ############################################################################################
6 # Creating the spatial objects #
7 ############################################################################################
8
9 --echo # Creating the spatial objects
10 USE test;
11
12 CREATE TABLE gis_point (fid INTEGER NOT NULL PRIMARY KEY, g POINT);
13 CREATE TABLE gis_linestring (fid INTEGER NOT NULL PRIMARY KEY, g LINESTRING);
14 CREATE TABLE gis_polygon (fid INTEGER NOT NULL PRIMARY KEY, g POLYGON);
15 CREATE TABLE gis_multi_point (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOINT);
16 CREATE TABLE gis_multi_linestring (fid INTEGER NOT NULL PRIMARY KEY, g MULTILINESTRING);
17 CREATE TABLE gis_multi_polygon (fid INTEGER NOT NULL PRIMARY KEY, g MULTIPOLYGON);
18 CREATE TABLE gis_geometrycollection (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRYCOLLECTION);
19
20 ############################################################################################
21 # Inserting the values specific to the spatial objects #
22 ############################################################################################
23
24 --echo # Inserting POINT Geometry Values
25 INSERT INTO gis_point VALUES
26 (101, ST_POINTFROMTEXT('POINT(0 0)')),
27 (102, ST_POINTFROMTEXT('POINT(1 0)')),
28 (103, ST_POINTFROMTEXT('POINT(0 1)')),
29 (104, ST_POINTFROMTEXT('POINT(1 1)')),
30 (105, ST_POINTFROMTEXT('POINT(-1 1)'));
31
32 INSERT INTO gis_point VALUES
33 (106, ST_POINTFROMWKB(POINT(0,0))),
34 (107, ST_POINTFROMWKB(POINT(10,0))),
35 (108, ST_POINTFROMWKB(POINT(0,10))),
36 (109, ST_POINTFROMWKB(POINT(-10,0))),
37 (110, ST_POINTFROMWKB(POINT(0,-10)));
38
39 INSERT INTO gis_point VALUES
40 (111, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1 1)')))),
41 (112, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 1e308)')))),
42 (113, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(1e308 -1e308)')))),
43 (114, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 1e308)')))),
44 (115, ST_POINTFROMWKB(ST_ASWKB(ST_POINTFROMTEXT('POINT(-1e308 -1e308)'))));
45
46 --echo # Inserting LINESTRING Geometry Values
47 INSERT INTO gis_linestring VALUES
48 (201, ST_LINEFROMTEXT('LINESTRING(0 0,5 5)')),
49 (202, ST_LINEFROMTEXT('LINESTRING(0 0,2 2,4 4)')),
50 (203, ST_LINEFROMTEXT('LINESTRING(0 0,5 5,10 10)'));
51
52 INSERT INTO gis_linestring VALUES
53 (204, ST_LINESTRINGFROMTEXT('LINESTRING(10 10,5 5)')),
54 (205, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,12 12,24 24)')),
55 (206, ST_LINESTRINGFROMTEXT('LINESTRING(0 0,50 50,100 100)'));
56
57 INSERT INTO gis_linestring VALUES
58 (207, ST_LINEFROMWKB(LINESTRING(POINT(0,0), POINT(5,5)))),
59 (208, ST_LINEFROMWKB(LINESTRING(POINT(0,0), POINT(-5,-5), POINT(-10,10)))),
60 (209, ST_LINEFROMWKB(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8))));
61
62 INSERT INTO gis_linestring VALUES
63 (210, ST_LINESTRINGFROMWKB(LINESTRING(POINT(0,0), POINT(5,5)))),
64 (211, ST_LINESTRINGFROMWKB(LINESTRING(POINT(0,0), POINT(-50,-50), POINT(10,-10)))),
65 (212, ST_LINESTRINGFROMWKB(LINESTRING(POINT(0,0), POINT(1e308,1e308), POINT(1e308,-1e308))));
66
67 INSERT INTO gis_linestring VALUES
68 (213, ST_LINEFROMWKB(ST_ASWKB(ST_LINEFROMTEXT('LINESTRING(1e308 1e308,1e308 -1e308)')))),
69 (214, ST_LINEFROMWKB(ST_ASWKB(ST_LINEFROMTEXT('LINESTRING(1 1,2 2,3 3,4 4,5 5,6 6,7 7,8 8,9 9)')))),
70 (215, ST_LINESTRINGFROMWKB(ST_ASWKB(ST_LINESTRINGFROMTEXT('LINESTRING(10 10,10 -10,-10 -10,-10 10,10 10)'))));
71
72 --echo # Inserting POLYGON Geometry Values
73 INSERT INTO gis_polygon VALUES
74 (301, ST_POLYFROMTEXT('POLYGON((0 0,0 5,5 5,0 0))')),
75 (302, ST_POLYFROMTEXT('POLYGON((0 0,0 5,5 5,5 0,0 0))')),
76 (303, ST_POLYFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0))'));
77
78 INSERT INTO gis_polygon VALUES
79 (304, ST_POLYGONFROMTEXT('POLYGON((0 0,0 50,50 50,50 0,0 0))')),
80 (305, ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0,0 0),(4 4,4 6,6 6,6 4,4 4))')),
81 (306, ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'));
82
83 INSERT INTO gis_polygon VALUES
84 (307, ST_POLYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(0,0))))),
85 (308, ST_POLYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,15), POINT(15,15), POINT(15,0), POINT(0,0))))),
86 (309, ST_POLYFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
87 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))));
88
89 INSERT INTO gis_polygon VALUES
90 (310, ST_POLYGONFROMWKB(POLYGON(LINESTRING(POINT(0,0), POINT(0,5), POINT(5,5), POINT(0,0))))),
91 (311, ST_POLYGONFROMWKB(POLYGON(LINESTRING(POINT(10,10), POINT(10,15), POINT(15,15), POINT(15,10), POINT(10,10))))),
92 (312, ST_POLYGONFROMWKB(POLYGON(LINESTRING(POINT(10,10), POINT(10,20), POINT(20,20), POINT(20,10), POINT(10,10)),
93 LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14)))));
94
95 INSERT INTO gis_polygon VALUES
96 (313, ST_POLYFROMWKB(ST_ASWKB(ST_POLYFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0,5 5,0 0))')))),
97 (314, ST_POLYFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((10 0,10 10,0 10,-10 10,-10 0,-10 -10,0 10,10 -10,10 0))')))),
98 (315, ST_POLYGONFROMWKB(ST_ASWKB(ST_POLYGONFROMTEXT('POLYGON((0 0,0 10,10 10,10 0,0 0,0 0,0 0),(2 2,2 4,4 4,4 2,2 2),(6 6,6 8,8 8,8 6,6 6))'))));
99
100 --echo # Inserting MULTIPOINT Geometry Values
101 INSERT INTO gis_multi_point VALUES
102 (401, ST_GEOMFROMTEXT('MULTIPOINT(0 0)')),
103 (402, ST_GEOMFROMTEXT('MULTIPOINT(0 0,2 2,4 4)')),
104 (403, ST_GEOMFROMTEXT('MULTIPOINT(0 0,5 5,10 10)'));
105
106 INSERT INTO gis_multi_point VALUES
107 (404, ST_GEOMFROMTEXT('MULTIPOINT(0 0,100 100)')),
108 (405, ST_GEOMFROMTEXT('MULTIPOINT(0 0,1e308 1e308)')),
109 (406, ST_GEOMFROMTEXT('MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308 1e308,-1e308 -1e308)'));
110
111 INSERT INTO gis_multi_point VALUES
112 (407, ST_GEOMFROMWKB(MULTIPOINT(POINT(0,0)))),
113 (408, ST_GEOMFROMWKB(MULTIPOINT(POINT(0,0), POINT(10,10)))),
114 (409, ST_GEOMFROMWKB(MULTIPOINT(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6))));
115
116 INSERT INTO gis_multi_point VALUES
117 (410, ST_GEOMFROMWKB(MULTIPOINT(POINT(0,0)))),
118 (411, ST_GEOMFROMWKB(MULTIPOINT(POINT(0,0), POINT(1e308,1e308)))),
119 (412, ST_GEOMFROMWKB(MULTIPOINT(POINT(1e308,1e308), POINT(-1e308,1e308), POINT(1e308,-1e308), POINT(-1e308,-1e308))));
120
121 INSERT INTO gis_multi_point VALUES
122 (413, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTIPOINT(0 0)')))),
123 (414, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTIPOINT(0 0,1e308 1e308,-1e308 -1e308)')))),
124 (415, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTIPOINT(1e308 1e308,1e308 -1e308,-1e308 1e308,-1e308 -1e308,1e308 1e308)'))));
125
126 --echo # Inserting MULTILINESTRING Geometry Values
127 INSERT INTO gis_multi_linestring VALUES
128 (501, ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 2))')),
129 (502, ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 2,4 4))')),
130 (503, ST_GEOMFROMTEXT('MULTILINESTRING((0 0,2 2,4 4),(6 6,8 8,10 10))'));
131
132 INSERT INTO gis_multi_linestring VALUES
133 (504, ST_GEOMFROMTEXT('MULTILINESTRING((0 0,100 100,-100 -100))')),
134 (505, ST_GEOMFROMTEXT('MULTILINESTRING((1e308 1e308,-1e308 -1e308))')),
135 (506, ST_GEOMFROMTEXT('MULTILINESTRING((1e308 1e308,-1e308 -1e308),(1e308 -1e308,-1e308 1e308))'));
136
137 INSERT INTO gis_multi_linestring VALUES
138 (507, ST_GEOMFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2))))),
139 (508, ST_GEOMFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24))))),
140 (509, ST_GEOMFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4)),
141 LINESTRING(POINT(6,6), POINT(8,8), POINT(10,10)))));
142
143 INSERT INTO gis_multi_linestring VALUES
144 (510, ST_GEOMFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4))))),
145 (511, ST_GEOMFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(1,1), POINT(2,2))))),
146 (512, ST_GEOMFROMWKB(MULTILINESTRING(LINESTRING(POINT(0,0), POINT(12,12), POINT(24,24)),
147 LINESTRING(POINT(36,36), POINT(48,48), POINT(50,50)))));
148
149 INSERT INTO gis_multi_linestring VALUES
150 (513, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10),(0 10,10 0))')))),
151 (514, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,10 10,-10 10,0 0),(0 0,-10 -10,10 -10,0 0))')))),
152 (515, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTILINESTRING((0 0,0 100),(0 0,100 0),(0 0,0 -100),(0 0,-100 0))'))));
153
154 --echo # Inserting MULTIPOLGYON Geometry Values
155 INSERT INTO gis_multi_polygon VALUES
156 (601, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)))')),
157 (602, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)),((5 5,5 10,10 10,5 5)))')),
158 (603, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0,0 0,0 0),(4 4,4 6,6 6,6 4,4 4)))'));
159
160 INSERT INTO gis_multi_polygon VALUES
161 (604, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 5,5 5,0 0)))')),
162 (605, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0,0 0,0 0),(4 4,4 6,6 6,6 4,4 4)),((0 0,0 -2,-2 -2,0 0)))')),
163 (606, ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,-5 5,0 0)),((0 0,-5 -5,5 -5,0 0)))'));
164
165 INSERT INTO gis_multi_polygon VALUES
166 (607, ST_GEOMFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0)))))),
167 (608, ST_GEOMFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(10,0), POINT(10,10), POINT(0,10), POINT(0,0)),
168 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))))),
169 (609, ST_GEOMFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(5,0), POINT(5,5), POINT(0,5), POINT(0,0))),
170 POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0))))));
171
172 INSERT INTO gis_multi_polygon VALUES
173 (610, ST_GEOMFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0)))))),
174 (611, ST_GEOMFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(10,10), POINT(20,10), POINT(20,20), POINT(10,20), POINT(10,10)),
175 LINESTRING(POINT(14,14), POINT(14,16), POINT(16,16), POINT(16,14), POINT(14,14)))))),
176 (612, ST_GEOMFROMWKB(MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
177 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
178 POLYGON(LINESTRING(POINT(0,0), POINT(-5,0), POINT(-5,-5), POINT(0,-5), POINT(0,0))))));
179
180 INSERT INTO gis_multi_polygon VALUES
181 (613, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,5 -5,0 0)),((0 0,-5 5,-5 -5,0 0)))')))),
182 (614, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,10 10,-10 10,0 0)),((0 0,-10 -10,10 -10,0 0)))')))),
183 (615, ST_GEOMFROMWKB(ST_ASWKB(ST_GEOMFROMTEXT('MULTIPOLYGON(((0 0,5 5,10 0,5 -5,0 0)))'))));
184
185 --echo # Inserting GEOMETRYCOLLECTION Geometry Values
186 INSERT INTO gis_geometrycollection VALUES
187 (701, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0))')),
188 (702, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
189 (703, ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION(POINT(5 5), POLYGON((0 0,0 10,10 10,10 0,0 0)))'));
190
191 INSERT INTO gis_geometrycollection VALUES
192 (704, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0), LINESTRING(0 0,10 10))')),
193 (705, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(POINT(0 0),'
194 'LINESTRING(0 0,10 10),'
195 'POLYGON((0 0,0 10,10 10,10 0,0 0,0 0, 0 0)))')),
196 (706, ST_GEOMETRYCOLLECTIONFROMTEXT('GEOMETRYCOLLECTION(MULTIPOINT(0 0,5 5,10 10),'
197 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
198 'MULTIPOLYGON(((0 0,0 10,10 10,10 0,0 0)),'
199 '((0 0,-10 0,-10 -10,0 -10,0 0))))'));
200
201 INSERT INTO gis_geometrycollection VALUES
202 (707, ST_GEOMCOLLFROMWKB(GEOMETRYCOLLECTION(POINT(0,0)))),
203 (708, ST_GEOMCOLLFROMWKB(GEOMETRYCOLLECTION(POINT(0,0),
204 LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
205 POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
206 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))))),
207 (709, ST_GEOMCOLLFROMWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(0,0), POINT(5,5), POINT(10,10)),
208 MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
209 LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
210 MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
211 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
212 POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0)))))));
213
214 INSERT INTO gis_geometrycollection VALUES
215 (710, ST_GEOMETRYCOLLECTIONFROMWKB(GEOMETRYCOLLECTION(POINT(0,0),
216 LINESTRING(POINT(0,0), POINT(100,100))))),
217 (711, ST_GEOMETRYCOLLECTIONFROMWKB(GEOMETRYCOLLECTION(POINT(10,10),
218 LINESTRING(POINT(10,10), POINT(12,12), POINT(14,14), POINT(16,16), POINT(18,18)),
219 POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
220 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4)))))),
221 (712, ST_GEOMETRYCOLLECTIONFROMWKB(GEOMETRYCOLLECTION(MULTIPOINT(POINT(10,10), POINT(15,15), POINT(20,20)),
222 MULTILINESTRING(LINESTRING(POINT(0,0), POINT(2,2), POINT(4,4), POINT(6,6), POINT(8,8)),
223 LINESTRING(POINT(10,10), POINT(5,5), POINT(0,10))),
224 MULTIPOLYGON(POLYGON(LINESTRING(POINT(0,0), POINT(0,10), POINT(10,10), POINT(10,0), POINT(0,0)),
225 LINESTRING(POINT(4,4), POINT(4,6), POINT(6,6), POINT(6,4), POINT(4,4))),
226 POLYGON(LINESTRING(POINT(0,0), POINT(-10,0), POINT(-10,-10), POINT(0,0)))))));
227
228 INSERT INTO gis_geometrycollection VALUES
229 (713, ST_GEOMCOLLFROMWKB(ST_ASWKB(ST_GEOMCOLLFROMTEXT('GEOMETRYCOLLECTION('
230 'POINT(0 0),'
231 'LINESTRING(0 0,10 10),'
232 'POLYGON((0 0,0 10,10 10,10 0,0 0)),'
233 'MULTIPOINT(0 0,2 2,4 4,6 6,8 8,10 10),'
234 'MULTILINESTRING((0 0,10 10),(0 10,10 0)),'
235 'MULTIPOLYGON(((0 0,0 5,5 5,5 0,0 0)),((5 5,5 10,10 10,10 5,5 5))))'))));
236
237 ############################################################################################
238 # Checking spatial index with geometry data #
239 ############################################################################################
240
241 --echo # Creating a table with spatial index column
242 CREATE TABLE geom_index (fid INTEGER NOT NULL PRIMARY KEY, g GEOMETRY NOT NULL, SPATIAL INDEX(g));
243
244 --echo # Inserting geometry values
245 INSERT INTO geom_index SELECT * FROM gis_point;
246
247 INSERT INTO geom_index SELECT * FROM gis_linestring;
248
249 INSERT INTO geom_index SELECT * FROM gis_polygon;
250
251 INSERT INTO geom_index SELECT * FROM gis_multi_point;
252
253 INSERT INTO geom_index SELECT * FROM gis_multi_linestring;
254
255 INSERT INTO geom_index SELECT * FROM gis_multi_polygon;
256
257 INSERT INTO geom_index SELECT * FROM gis_geometrycollection;
258
259 check table geom_index;
260
261 --echo # Final cleanup
262 DROP TABLE gis_point;
263 DROP TABLE gis_linestring;
264 DROP TABLE gis_polygon;
265 DROP TABLE gis_multi_point;
266 DROP TABLE gis_multi_linestring;
267 DROP TABLE gis_multi_polygon;
268 DROP TABLE gis_geometrycollection;
269 DROP TABLE geom_index;