Debian Codegraph @

mysql-test/suite/gis/r/cast_as_geometry.result

raw
1 #####################################
2 # Testing of general requirements #
3 #####################################
4 #
5 ####################################################################
6 # Requirement F-1a: #
7 # Casting using CONVERT(..., <geometry type>) must be allowed #
8 # and give the same result as CAST(... AS <geometry type>). #
9 ####################################################################
10 #
11 # Casting using CONVERT(..., POINT) must give the same result
12 # as CAST(... AS POINT).
13 # Must return 1.
14 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1))') AS POINT), CONVERT(ST_GEOMFROMTEXT('MULTIPOINT((1 1))'), POINT));
15 ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1))') AS POINT), CONVERT(ST_GEOMFROMTEXT('MULTIPOINT((1 1))'), POINT))
16 1
17 #
18 # Casting using CONVERT(..., LINESTRING) must give the same result
19 # as CAST(... AS LINESTRING).
20 # Must return 1.
21 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2))') AS LINESTRING), CONVERT(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2))'), LINESTRING));
22 ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2))') AS LINESTRING), CONVERT(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2))'), LINESTRING))
23 1
24 #
25 # Casting using CONVERT(..., POLYGON) must give the same result
26 # as CAST(... AS POLYGON).
27 # Must return 1.
28 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))') AS POLYGON), CONVERT(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))'), POLYGON));
29 ST_EQUALS(CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))') AS POLYGON), CONVERT(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)))'), POLYGON))
30 1
31 #
32 # Casting using CONVERT(..., MULTIPOINT) must give the same result
33 # as CAST(... AS MULTIPOINT).
34 # Must return 1.
35 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTIPOINT), CONVERT(ST_GEOMFROMTEXT('POINT(1 1)'), MULTIPOINT));
36 ST_EQUALS(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTIPOINT), CONVERT(ST_GEOMFROMTEXT('POINT(1 1)'), MULTIPOINT))
37 1
38 #
39 # Casting using CONVERT(..., MULTILINESTRING) must give the same result
40 # as CAST(... AS MULTILINESTRING).
41 # Must return 1.
42 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS MULTILINESTRING), CONVERT(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)'), MULTILINESTRING));
43 ST_EQUALS(CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS MULTILINESTRING), CONVERT(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)'), MULTILINESTRING))
44 1
45 #
46 # Casting using CONVERT(..., MULTIPOLYGON) must give the same result
47 # as CAST(... AS MULTIPOLYGON).
48 # Must return 1.
49 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS MULTIPOLYGON), CONVERT(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), MULTIPOLYGON));
50 ST_EQUALS(CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS MULTIPOLYGON), CONVERT(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), MULTIPOLYGON))
51 1
52 #
53 # Casting using CONVERT(..., GEOMETRYCOLLECTION) must give the same result
54 # as CAST(... AS GEOMETRYCOLLECTION).
55 # Must return 1.
56 SELECT ST_EQUALS(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION), CONVERT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))'), GEOMETRYCOLLECTION));
57 ST_EQUALS(CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION), CONVERT(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))'), GEOMETRYCOLLECTION))
58 1
59 #
60 ####################################################################
61 # Requirement F-1b: #
62 # Casting to geometry types using #
63 # JSON_VALUE(... RETURNING <geometry type>) must not be allowed. #
64 ####################################################################
65 #
66 # Casting to POINT using JSON_VALUE(... RETURNING POINT) must raise error.
67 DO JSON_VALUE('{"geometry": {"type" : "Point", "coordinates": [1.0, 1.0]}}', '$.geometry' RETURNING POINT);
68 ERROR 22S01: Invalid cast from JSON to POINT.
69 #
70 # Casting to LINESTRING using JSON_VALUE(... RETURNING LINESTRING) must raise error.
71 DO JSON_VALUE('{"geometry": {"type": "LineString", "coordinates": [[1.0, 1.0], [2.0, 2.0]]}}', '$.geometry' RETURNING LINESTRING);
72 ERROR 22S01: Invalid cast from JSON to LINESTRING.
73 #
74 # Casting to POLYGON using JSON_VALUE(... RETURNING POLYGON) must raise error.
75 DO JSON_VALUE('{"geometry": {"type": "Polygon", "coordinates": [[[1.0, 1.0], [4.0, 1.0], [4.0, 4.0], [1.0, 4.0], [1.0, 1.0]]]}}', '$.geometry' RETURNING POLYGON);
76 ERROR 22S01: Invalid cast from JSON to POLYGON.
77 #
78 # Casting to MULTIPOINT using JSON_VALUE(... RETURNING MULTIPOINT) must raise error.
79 DO JSON_VALUE('{"geometry": {"type": "MultiPoint", "coordinates": [[1.0, 1.0], [2.0, 2.0]]}}', '$.geometry' RETURNING MULTIPOINT);
80 ERROR 22S01: Invalid cast from JSON to MULTIPOINT.
81 #
82 # Casting to MULTILINESTRING using JSON_VALUE(... RETURNING MULTILINESTRING) must raise error.
83 DO JSON_VALUE('{"geometry": {"type": "MultiLineString", "coordinates": [[[1.0, 1.0], [2.0, 2.0]], [[3.0, 3.0], [4.0, 4.0]]]}}', '$.geometry' RETURNING MULTILINESTRING);
84 ERROR 22S01: Invalid cast from JSON to MULTILINESTRING.
85 #
86 # Casting to MULTIPOLYGON using JSON_VALUE(... RETURNING MULTIPOLYGON) must raise error.
87 DO JSON_VALUE('{"geometry": {"type": "{"type": "MultiPolygon", "coordinates": [[[[1.0, 1.0], [4.0, 1.0], [4.0, 4.0], [1.0, 4.0], [1.0, 1.0]]], [[[2.0, 2.0], [2.0, 3.0], [3.0, 3.0], [3.0, 2.0], [2.0, 2.0]]]]}}', '$.geometry' RETURNING MULTIPOLYGON);
88 ERROR 22S01: Invalid cast from JSON to MULTIPOLYGON.
89 #
90 # Casting to GEOMETRYCOLLECTION using JSON_VALUE(... RETURNING GEOMETRYCOLLECTION) must raise error.
91 DO JSON_VALUE('{"geometry": {"type": "GeometryCollection", "geometries": [{"type": "Point", "coordinates": [1.0, 1.0]}, {"type": "LineString", "coordinates": [[2.0, 2.0], [3.0, 3.0]]}]}}', '$.geometry' RETURNING GEOMETRYCOLLECTION);
92 ERROR 22S01: Invalid cast from JSON to GEOMETRYCOLLECTION.
93 #
94 ####################################################################
95 # Requirement F-1c: #
96 # Casting to ARRAY of geometry types must not be allowed. #
97 ####################################################################
98 #
99 # Casting using CAST(... AS POINT ARRAY) must raise error.
100 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT ARRAY);
101 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of POINT'
102 #
103 # Casting using CAST(... AS LINESTRING ARRAY) must raise error.
104 DO CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING ARRAY);
105 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of LINESTRING'
106 #
107 # Casting using CAST(... POLYGON ARRAY) must raise error.
108 DO CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON ARRAY);
109 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of POLYGON'
110 #
111 # Casting using CAST(... MULTIPOINT ARRAY) must raise error.
112 DO CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT ARRAY);
113 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of MULTIPOINT'
114 #
115 # Casting using CAST(... MULTILINESTRING ARRAY) must raise error.
116 DO CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING ARRAY);
117 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of MULTILINESTRING>'
118 #
119 # Casting using CAST(... MULTIPOLYGON ARRAY) must raise error.
120 DO CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON ARRAY);
121 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of MULTIPOLYGON'
122 #
123 # Casting using CAST(... GEOMETRYCOLLECTION ARRAY) must raise error.
124 DO CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION ARRAY);
125 ERROR 42000: This version of MySQL doesn't yet support 'CAST-ing data to array of GEOMETRYCOLLECTION'
126 #
127 ####################################################################
128 # Requirement F-1d: #
129 # Non-instantiable target types must not be allowed. #
130 ####################################################################
131 #
132 # Casting to GEOMETRY must raise error.
133 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS GEOMETRY));
134 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GEOMETRY))' at line 1
135 #
136 # Casting to CURVE must raise error.
137 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS CURVE));
138 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CURVE))' at line 1
139 #
140 # Casting to SURFACE must raise error.
141 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS SURFACE));
142 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SURFACE))' at line 1
143 #
144 # Casting to MULTICURVE must raise error.
145 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTICURVE));
146 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MULTICURVE))' at line 1
147 #
148 # Casting to MULTISURFACE must raise error.
149 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS MULTISURFACE));
150 ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'MULTISURFACE))' at line 1
151 #
152 ####################################################################
153 # Requirement F-1e: #
154 # Casting to non-geometry types must give same result as before. #
155 ####################################################################
156 #
157 # Casting geometry to SIGNED must raise error.
158 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS SIGNED);
159 ERROR HY000: Incorrect arguments to cast_as_signed
160 #
161 # Casting geometry to UNSIGNED must raise error.
162 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS UNSIGNED);
163 ERROR HY000: Incorrect arguments to cast_as_unsigned
164 #
165 # Casting geometry to YEAR must raise error.
166 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS YEAR);
167 ERROR HY000: Incorrect arguments to cast_as_year
168 #
169 # Casting geometry to DATE must return NULL.
170 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DATE);
171 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DATE)
172 NULL
173 #
174 # Casting geometry to TIME must return NULL.
175 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS TIME);
176 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS TIME)
177 NULL
178 #
179 # Casting geometry to DATETIME must return NULL.
180 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DATETIME);
181 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DATETIME)
182 NULL
183 #
184 # Casting geometry to BINARY must return SWKB.
185 SELECT HEX(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS BINARY));
186 HEX(CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS BINARY))
187 000000000101000000000000000000F03F000000000000F03F
188 #
189 # Casting geometry to DECIMAL must return 0.
190 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DECIMAL);
191 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DECIMAL)
192 0
193 #
194 # Casting geometry to FLOAT must return 0.
195 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS FLOAT);
196 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS FLOAT)
197 0
198 #
199 # Casting geometry to DOUBLE must return 0.
200 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DOUBLE);
201 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS DOUBLE)
202 0
203 #
204 # Casting geometry to JSON must return JSON-string.
205 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS JSON);
206 CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS JSON)
207 {"type": "Point", "coordinates": [1.0, 1.0]}
208 #
209 ####################################################################
210 # Requirement F-2: #
211 # Casting NULL to geometry types must return NULL. #
212 ####################################################################
213 #
214 SELECT CAST(NULL AS POINT);
215 CAST(NULL AS POINT)
216 NULL
217 #
218 ####################################################################
219 # Requirement F-3: #
220 # Casting geometries that are not syntactically well-formed #
221 # must not be allowed. #
222 ####################################################################
223 #
224 # Casting 'POINT(1)' to POINT must raise error.
225 DO CAST(0x000000000101000000000000000000F03F AS POINT);
226 ERROR 22023: Invalid GIS data provided to function cast_as_point.
227 #
228 ####################################################################
229 # Requirement F-4: #
230 # Casting geometry with undefined SRS must raise error. #
231 ####################################################################
232 #
233 DO CAST(ST_GEOMFROMTEXT('POINT(1 1)', 256) AS POINT);
234 ERROR SR001: There's no spatial reference system with SRID 256.
235 #
236 ####################################################################
237 # Requirement F-5: #
238 # Casting must not change coordinate values or order. #
239 ####################################################################
240 #
241 # Must return 1.
242 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POINT(0 1)'), CAST(ST_GEOMFROMTEXT('POINT(0 1)') AS POINT));
243 ST_EQUALS(ST_GEOMFROMTEXT('POINT(0 1)'), CAST(ST_GEOMFROMTEXT('POINT(0 1)') AS POINT))
244 1
245 #
246 # Must return 1.
247 SELECT ST_EQUALS(ST_GEOMFROMTEXT('LINESTRING(1 2, 3 4)'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))') AS LINESTRING));
248 ST_EQUALS(ST_GEOMFROMTEXT('LINESTRING(1 2, 3 4)'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))') AS LINESTRING))
249 1
250 #
251 # Must return 1.
252 SELECT ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 1, 4 4, 1 4, 1 1)') AS POLYGON));
253 ST_EQUALS(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 4 1, 4 4, 1 4, 1 1)') AS POLYGON))
254 1
255 #
256 # Must return 1.
257 SELECT ST_EQUALS(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 2, 3 4)') AS MULTIPOINT));
258 ST_EQUALS(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))'), CAST(ST_GEOMFROMTEXT('LINESTRING(1 2, 3 4)') AS MULTIPOINT))
259 1
260 #
261 # Must return 1.
262 SELECT ST_EQUALS(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'), CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))') AS MULTILINESTRING));
263 ST_EQUALS(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))'), CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 2 3, 3 3, 3 2, 2 2))') AS MULTILINESTRING))
264 1
265 #
266 # Must return 1.
267 SELECT ST_EQUALS(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 3 2, 3 3, 2 3, 2 2)))'), CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS MULTIPOLYGON));
268 ST_EQUALS(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 3 2, 3 3, 2 3, 2 2)))'), CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 4 1, 4 4, 1 4, 1 1), (2 2, 3 2, 3 3, 2 3, 2 2))') AS MULTIPOLYGON))
269 1
270 #
271 # Must return 1.
272 SELECT ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 2), POINT(3 4))'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))') AS GEOMETRYCOLLECTION));
273 ST_EQUALS(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 2), POINT(3 4))'), CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 2), (3 4))') AS GEOMETRYCOLLECTION))
274 1
275 #
276 ####################################################################
277 # Requirement F-6a: #
278 # Casting must not change the SRS of the geometry. #
279 ####################################################################
280 #
281 # Must return 0.
282 SELECT ST_SRID(CAST(ST_GEOMFROMTEXT('POINT(1 1)', 0) AS POINT));
283 ST_SRID(CAST(ST_GEOMFROMTEXT('POINT(1 1)', 0) AS POINT))
284 0
285 #
286 # Must return 4326.
287 SELECT ST_SRID(CAST(ST_GEOMFROMTEXT('POINT(45 90)', 4326) AS POINT));
288 ST_SRID(CAST(ST_GEOMFROMTEXT('POINT(45 90)', 4326) AS POINT))
289 4326
290 #
291 ####################################################################
292 # Requirement F-6b: #
293 # Casting geographic geometry with longitude outside of #
294 # (-180, 180] must not be allowed. #
295 ####################################################################
296 #
297 # Casting POINT(0 180.000000001) with SRID=4326 to POINT must raise error.
298 DO CAST(0xE6100000010100000070890000008066400000000000000000 AS POINT);
299 ERROR 22S02: A parameter of function cast_as_point contains a geometry with longitude 180.000000, which is out of range. It must be within (-180.000000, 180.000000].
300 #
301 # Casting POINT(0 -180.000000001) with SRID=4326 to POINT must raise error.
302 DO CAST(0xE6100000010100000070890000008066C00000000000000000 AS POINT);
303 ERROR 22S02: A parameter of function cast_as_point contains a geometry with longitude -180.000000, which is out of range. It must be within (-180.000000, 180.000000].
304 #
305 ####################################################################
306 # Requirement F-6c: #
307 # Casting geographic geometry with latitude outside of #
308 # [-90, 90] must not be allowed. #
309 ####################################################################
310 #
311 # Casting POINT(90.000000001 0) with SRID=4326 to POINT must raise error.
312 DO CAST(0xE610000001010000000000000000000000E112010000805640 AS POINT);
313 ERROR 22S03: A parameter of function cast_as_point contains a geometry with latitude 90.000000, which is out of range. It must be within [-90.000000, 90.000000].
314 #
315 # Casting POINT(-90.000000001 0) with SRID=4326 to POINT must raise error.
316 DO CAST(0xE610000001010000000000000000000000E1120100008056C0 AS POINT);
317 ERROR 22S03: A parameter of function cast_as_point contains a geometry with latitude -90.000000, which is out of range. It must be within [-90.000000, 90.000000].
318 #
319 ####################################################################
320 # Queries with EXPLAIN should return appropiate function name #
321 ####################################################################
322 #
323 # Should return /* select#1 */ select cast(st_geomfromtext('point(1 1)') as point) AS `CAST(ST_GEOMFROMTEXT('POINT((1 1))') AS POINT)'
324 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT);
325 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
326 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
327 Warnings:
328 Note 1003 /* select#1 */ select cast(st_geomfromtext('POINT(1 1)') as point) AS `CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT)`
329 #
330 # Should return /* select#1 */ select cast(st_geomfromtext('LINESTRING(1 1, 2 2)') as linestring) AS `CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING)`
331 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING);
332 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
333 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
334 Warnings:
335 Note 1003 /* select#1 */ select cast(st_geomfromtext('LINESTRING(1 1, 2 2)') as linestring) AS `CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING)`
336 #
337 # Should return /* select#1 */ select cast(st_geomfromtext('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') as polygon) AS `CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON)`
338 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON);
339 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
340 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
341 Warnings:
342 Note 1003 /* select#1 */ select cast(st_geomfromtext('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') as polygon) AS `CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON)`
343 #
344 # Should return /* select#1 */ select cast(st_geomfromtext('MULTIPOINT((1 1), (2 2))') as multipoint) AS `CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT)`
345 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT);
346 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
347 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
348 Warnings:
349 Note 1003 /* select#1 */ select cast(st_geomfromtext('MULTIPOINT((1 1), (2 2))') as multipoint) AS `CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT)`
350 #
351 # Should return /* select#1 */ select cast(st_geomfromtext('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') as multilinestring) AS `CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING)`
352 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING);
353 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
354 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
355 Warnings:
356 Note 1003 /* select#1 */ select cast(st_geomfromtext('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') as multilinestring) AS `CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING)`
357 #
358 # Should return /* select#1 */ select cast(st_geomfromtext('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') as multipolygon) AS `CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON)`
359 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON);
360 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
361 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
362 Warnings:
363 Note 1003 /* select#1 */ select cast(st_geomfromtext('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') as multipolygon) AS `CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON)`
364 #
365 # Should return /* select#1 */ select cast(st_geomfromtext('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') as geometrycollection) AS `CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION)`
366 EXPLAIN SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION);
367 id select_type table partitions type possible_keys key key_len ref rows filtered Extra
368 1 SIMPLE NULL NULL NULL NULL NULL NULL NULL NULL NULL No tables used
369 Warnings:
370 Note 1003 /* select#1 */ select cast(st_geomfromtext('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') as geometrycollection) AS `CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION)`
371 ####################################################################
372 # SELECT ... UNION SELECT ... with #
373 # CAST(... AS <GEOMETRY_TYPE>) columns should work as expected #
374 ####################################################################
375 #
376 # Should return Point.
377 WITH g(g1) AS (
378 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT)
379 UNION
380 SELECT CAST(ST_GEOMFROMTEXT('POINT(1 1)') AS POINT))
381 SELECT ST_ASTEXT(g1) FROM g;
382 ST_ASTEXT(g1)
383 POINT(1 1)
384 #
385 # Should return Linestring.
386 WITH g(g1) AS (
387 SELECT CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING)
388 UNION
389 SELECT CAST(ST_GEOMFROMTEXT('LINESTRING(1 1, 2 2)') AS LINESTRING))
390 SELECT ST_ASTEXT(g1) FROM g;
391 ST_ASTEXT(g1)
392 LINESTRING(1 1,2 2)
393 #
394 # Should return Polygon.
395 WITH g(g1) AS (
396 SELECT CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON)
397 UNION
398 SELECT CAST(ST_GEOMFROMTEXT('POLYGON((1 1, 4 1, 4 4, 1 4, 1 1))') AS POLYGON))
399 SELECT ST_ASTEXT(g1) FROM g;
400 ST_ASTEXT(g1)
401 POLYGON((1 1,4 1,4 4,1 4,1 1))
402 #
403 # Should return Multipoint.
404 WITH g(g1) AS (
405 SELECT CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT)
406 UNION
407 SELECT CAST(ST_GEOMFROMTEXT('MULTIPOINT((1 1), (2 2))') AS MULTIPOINT))
408 SELECT ST_ASTEXT(g1) FROM g;
409 ST_ASTEXT(g1)
410 MULTIPOINT((1 1),(2 2))
411 #
412 # Should return Multilinestring.
413 WITH g(g1) AS (
414 SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING)
415 UNION
416 SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTILINESTRING((1 1, 2 2), (3 3, 4 4))') AS MULTILINESTRING))
417 SELECT ST_ASTEXT(g1) FROM g;
418 ST_ASTEXT(g1)
419 MULTILINESTRING((1 1,2 2),(3 3,4 4))
420 #
421 # Should return Multipolygon.
422 WITH g(g1) AS (
423 SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON)
424 UNION
425 SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON) UNION SELECT CAST(ST_GEOMFROMTEXT('MULTIPOLYGON(((1 1, 4 1, 4 4, 1 4, 1 1)), ((2 2, 2 3, 3 3, 3 2, 2 2)))') AS MULTIPOLYGON))
426 SELECT ST_ASTEXT(g1) FROM g;
427 ST_ASTEXT(g1)
428 MULTIPOLYGON(((1 1,4 1,4 4,1 4,1 1)),((2 2,3 2,3 3,2 3,2 2)))
429 #
430 # Should return Geometrycollection.
431 WITH g(g1) AS (
432 SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION) UNION SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION)
433 UNION
434 SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION) UNION SELECT CAST(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(1 1), LINESTRING(2 2, 3 3))') AS GEOMETRYCOLLECTION))
435 SELECT ST_ASTEXT(g1) FROM g;
436 ST_ASTEXT(g1)
437 GEOMETRYCOLLECTION(POINT(1 1),LINESTRING(2 2,3 3))