Debian Codegraph @

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

raw
1 # setup of data for tests involving simple aggregations and group by
2 CREATE TABLE table_simple_aggregation ( running_number INTEGER NOT NULL
3 AUTO_INCREMENT, grouping_condition INTEGER, location GEOMETRY , PRIMARY KEY (
4 running_number));
5 INSERT INTO table_simple_aggregation ( grouping_condition, location ) VALUES
6 ( 0,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
7 ( 1,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
8 ( 0,ST_GEOMFROMTEXT('POINT(1 0)',4326)),
9 ( 1,ST_GEOMFROMTEXT('POINT(2 0)',4326)),
10 ( 0,ST_GEOMFROMTEXT('POINT(3 0)',4326));
11 # Functional requirement F-4: ST_COLLECT shall support simple table
12 # aggregations
13 # result shall be 1
14 SELECT ST_EQUALS( (SELECT ST_COLLECT( location ) AS t FROM
15 table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT((0 0),(0 0),(1 0),(2
16 0),(3 0)) ',4326));
17 ST_EQUALS( (SELECT ST_COLLECT( location ) AS t FROM
18 table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT((0 0),(0 0),(1 0),(2
19 0),(3 0)) ',4326))
20 1
21 # Functional requirement F-8 Shall support DISTINCT in aggregates
22 # result shall be 1
23 SELECT ST_EQUALS( (SELECT ST_COLLECT( DISTINCT location ) AS t FROM
24 table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT((0 0),(1 0),(2 0),(3
25 0)) ',4326));
26 ST_EQUALS( (SELECT ST_COLLECT( DISTINCT location ) AS t FROM
27 table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT((0 0),(1 0),(2 0),(3
28 0)) ',4326))
29 1
30 # Functional requirement F-5: ST_COLLECT shall support group by, which
31 # is given by aggregation machinery
32 # result shall be
33 # MULTIPOINT((0 0),(1 0),(3 0))
34 # MULTIPOINT((2 0),(0 0))
35 SELECT ST_ASTEXT(ST_COLLECT( DISTINCT location )) AS t FROM
36 table_simple_aggregation GROUP BY grouping_condition;
37 t
38 MULTIPOINT((0 0),(1 0),(3 0))
39 MULTIPOINT((0 0),(2 0))
40 # Distinct with rollup
41 SELECT st_astext(ST_COLLECT( distinct location )) AS t from
42 table_simple_aggregation group by st_latitude(location) with rollup;
43 t
44 MULTIPOINT((0 0))
45 MULTIPOINT((1 0))
46 MULTIPOINT((2 0))
47 MULTIPOINT((3 0))
48 MULTIPOINT((0 0),(1 0),(2 0),(3 0))
49 INSERT INTO table_simple_aggregation (location) VALUES
50 ( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
51 ( NULL);
52 # F-7 Aggregations with Nulls inside will just miss an element for each
53 # Null
54 # the result here shall be 1
55 SELECT ST_EQUALS((SELECT ST_COLLECT(LOCATION) AS T FROM
56 table_simple_aggregation), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION( MULTIPOINT((0
57 0),(1 0),(3 0)), MULTIPOINT((2 0),(0 0)), POINT(0 0))',4326));
58 ST_EQUALS((SELECT ST_COLLECT(LOCATION) AS T FROM
59 table_simple_aggregation), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION( MULTIPOINT((0
60 0),(1 0),(3 0)), MULTIPOINT((2 0),(0 0)), POINT(0 0))',4326))
61 1
62 # F-1 ST_COLLECT SHALL only return NULL if all elements are NULL or the
63 # aggregate is empty.
64 # as only a null is aggregated the result of the subquery shall be NULL
65 # and the result of the whole query shall be 1
66 SELECT (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
67 location = NULL) IS NULL;
68 (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
69 location = NULL) IS NULL
70 1
71 # as no element is aggregated the result of the subquery shall be NULL
72 # and the result of the whole query shall be 1
73 SELECT (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
74 st_srid(location)=2110) IS NULL;
75 (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
76 st_srid(location)=2110) IS NULL
77 1
78 INSERT INTO table_simple_aggregation (location) VALUES
79 ( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
80 ( NULL),
81 ( NULL);
82 SELECT ST_ASTEXT(ST_COLLECT(location) OVER ( ROWS BETWEEN 1 PRECEDING AND
83 CURRENT ROW)) FROM table_simple_aggregation;
84 ST_ASTEXT(ST_COLLECT(location) OVER ( ROWS BETWEEN 1 PRECEDING AND
85 CURRENT ROW))
86 MULTIPOINT((0 0))
87 MULTIPOINT((0 0),(0 0))
88 MULTIPOINT((0 0),(1 0))
89 MULTIPOINT((1 0),(2 0))
90 MULTIPOINT((2 0),(3 0))
91 MULTIPOINT((3 0),(0 0))
92 MULTIPOINT((0 0))
93 MULTIPOINT((0 0))
94 MULTIPOINT((0 0))
95 NULL
96 Excercising multiple code paths.
97 SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(running_number)
98 OVER() FROM table_simple_aggregation GROUP BY running_number;
99 geo SUM(running_number)
100 MULTIPOINT((0 0)) 55
101 MULTIPOINT((0 0)) 55
102 MULTIPOINT((0 0)) 55
103 MULTIPOINT((0 0)) 55
104 MULTIPOINT((1 0)) 55
105 MULTIPOINT((2 0)) 55
106 MULTIPOINT((3 0)) 55
107 NULL 55
108 NULL 55
109 NULL 55
110 OVER()
111 SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(grouping_condition)
112 OVER(), grouping_condition FROM table_simple_aggregation GROUP BY
113 grouping_condition;
114 geo SUM(grouping_condition)
115 MULTIPOINT((0 0)) 1 NULL
116 MULTIPOINT((0 0),(1 0),(3 0)) 1 0
117 MULTIPOINT((0 0),(2 0)) 1 1
118 OVER() grouping_condition
119 SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(grouping_condition) OVER(),
120 grouping_condition FROM table_simple_aggregation GROUP BY grouping_condition;
121 geo SUM(grouping_condition) OVER() grouping_condition
122 MULTIPOINT((0 0),(0 0)) 1 NULL
123 MULTIPOINT((0 0),(1 0),(3 0)) 1 0
124 MULTIPOINT((0 0),(2 0)) 1 1
125 SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(running_number) OVER() FROM
126 table_simple_aggregation GROUP BY running_number;
127 geo SUM(running_number) OVER()
128 MULTIPOINT((0 0)) 55
129 MULTIPOINT((0 0)) 55
130 MULTIPOINT((0 0)) 55
131 MULTIPOINT((0 0)) 55
132 MULTIPOINT((1 0)) 55
133 MULTIPOINT((2 0)) 55
134 MULTIPOINT((3 0)) 55
135 NULL 55
136 NULL 55
137 NULL 55
138 # Teardown of testing NULL data
139 DROP TABLE table_simple_aggregation;
140 # Setup for testing handling of multiple SRS
141 CREATE TABLE multi_srs_table ( running_number INTEGER NOT NULL AUTO_INCREMENT,
142 geometry GEOMETRY , PRIMARY KEY ( running_number ));
143 INSERT INTO multi_srs_table( geometry ) VALUES
144 (ST_GEOMFROMTEXT('POINT(60 -24)' ,4326)),
145 (ST_GEOMFROMTEXT('POINT(61 -24)' ,4326)),
146 (ST_GEOMFROMTEXT('POINT(38 77)'));
147 # F-2 a) If the elements in an aggregate is of different SRSs,
148 # ST_COLLECT MUST raise ER_GIS_DIFFERENT_SRIDS.
149 SELECT ST_ASTEXT(ST_COLLECT(geometry)) AS t FROM multi_srs_table;
150 ERROR 22S05: Arguments to function st_collect contains geometries with different SRIDs: 4326 and 0. All geometries must have the same SRID.
151 # F-2 b) If all the elements in an aggregate is of same SRS, ST_COLLECT
152 # MUST return a result in that SRS.
153 # result shall be one MULTIPOINT((60 -24),(61 -24)) with SRID 4326 and
154 # one
155 # Multipoint((38 77)) with SRID 0. There is some rounding issue on the
156 # result, bug #31535105
157 SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
158 multi_srs_table GROUP BY ST_SRID(geometry);
159 st_srid(geometry) t
160 0 MULTIPOINT((38 77))
161 4326 MULTIPOINT((60 -24),(61.00000000000001 -24))
162 Rollup needs all SRIDs to be the same.
163 SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
164 multi_srs_table GROUP BY ST_SRID(geometry) WITH ROLLUP;
165 ERROR 22S05: Arguments to function st_collect contains geometries with different SRIDs: 0 and 4326. All geometries must have the same SRID.
166 # Triggering a codepath for geometrycollection in temp tables
167 INSERT INTO multi_srs_table( geometry ) VALUES
168 (ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(60 -24))' ,4326));
169 SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
170 multi_srs_table GROUP BY ST_SRID(geometry);
171 st_srid(geometry) t
172 0 MULTIPOINT((38 77))
173 4326 GEOMETRYCOLLECTION(POINT(60 -24),POINT(61.00000000000001 -24),GEOMETRYCOLLECTION(POINT(60 -24)))
174 #teardown of testing handling of multiple SRS
175 DROP TABLE multi_srs_table;
176 # setup of testing handling different geometry types
177 CREATE TABLE simple_table ( running_number INTEGER NOT NULL AUTO_INCREMENT ,
178 geo GEOMETRY, PRIMARY KEY ( RUNNING_NUMBER));
179 INSERT INTO simple_table ( geo) VALUES
180 (ST_GEOMFROMTEXT('POINT(0 0)')),
181 (ST_GEOMFROMTEXT('LINESTRING(1 0, 1 1)')),
182 (ST_GEOMFROMTEXT('LINESTRING(2 0, 2 1)')),
183 (ST_GEOMFROMTEXT('POLYGON((3 0, 0 0, 0 3, 3 3, 3 0))')),
184 (ST_GEOMFROMTEXT('POLYGON((4 0, 0 0, 0 4, 4 4, 4 0))')),
185 (ST_GEOMFROMTEXT('MULTIPOINT(5 0)')),
186 (ST_GEOMFROMTEXT('MULTIPOINT(6 0)')),
187 (ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')),
188 (ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
189 # Functional requirement F-9 a, b, and c ) An aggregation containing
190 # more than one type of geometry or any MULTI is GEOMETRYCOLLECTION, if
191 # it only contains a single type of POINTS, LINESTRINGS or POLYGONS it
192 # will be a MULTI of the same kind.
193 # MP: Multipoint
194 # MPoly: Multipolygon
195 # MLS: Multilinestring
196 # GC: geometrycollection
197 # Functional requirement F-6 shall support window functions
198 # result is expected for come in this order: MP, GC, MLS, GC, MPpoly,
199 # GC, GC, GC, GC
200 SELECT ST_ASTEXT(ST_COLLECT(geo) OVER( ORDER BY running_number ROWS BETWEEN 1
201 PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table;
202 geocollect
203 MULTIPOINT((0 0))
204 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 0,1 1))
205 MULTILINESTRING((1 0,1 1),(2 0,2 1))
206 GEOMETRYCOLLECTION(LINESTRING(2 0,2 1),POLYGON((3 0,3 3,0 3,0 0,3 0)))
207 MULTIPOLYGON(((3 0,3 3,0 3,0 0,3 0)),((4 0,4 4,0 4,0 0,4 0)))
208 GEOMETRYCOLLECTION(POLYGON((4 0,4 4,0 4,0 0,4 0)),MULTIPOINT((5 0)))
209 GEOMETRYCOLLECTION(MULTIPOINT((5 0)),MULTIPOINT((6 0)))
210 GEOMETRYCOLLECTION(MULTIPOINT((6 0)),GEOMETRYCOLLECTION EMPTY)
211 GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY,GEOMETRYCOLLECTION EMPTY)
212 # with DISTINCT this result is expected to be:
213 # GEMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
214 # GEMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
215 SELECT ST_ASTEXT(ST_COLLECT( DISTINCT geo) OVER( ORDER BY running_number ROWS
216 BETWEEN 1 PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table WHERE
217 ST_EQUALS(geo,ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
218 geocollect
219 GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
220 GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
221 # Exercising the "copy" constructor
222 SELECT ST_ASTEXT(ST_COLLECT(geo)) FROM simple_table GROUP BY geo WITH ROLLUP;
223 ST_ASTEXT(ST_COLLECT(geo))
224 MULTIPOINT((0 0))
225 MULTILINESTRING((2 0,2 1))
226 MULTILINESTRING((1 0,1 1))
227 MULTIPOLYGON(((3 0,3 3,0 3,0 0,3 0)))
228 MULTIPOLYGON(((4 0,4 4,0 4,0 0,4 0)))
229 GEOMETRYCOLLECTION(MULTIPOINT((5 0)))
230 GEOMETRYCOLLECTION(MULTIPOINT((6 0)))
231 GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY,GEOMETRYCOLLECTION EMPTY)
232 GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(2 0,2 1),LINESTRING(1 0,1 1),POLYGON((3 0,3 3,0 3,0 0,3 0)),POLYGON((4 0,4 4,0 4,0 0,4 0)),MULTIPOINT((5 0)),MULTIPOINT((6 0)),GEOMETRYCOLLECTION EMPTY,GEOMETRYCOLLECTION EMPTY)
233 # Casting Geometry as decimal invokes val_decimal()
234 SELECT CAST(ST_COLLECT(geo) AS DECIMAL ) FROM simple_table;
235 CAST(ST_COLLECT(geo) AS DECIMAL )
236 0
237 DROP TABLE simple_table;
238 set @d:=repeat(1,228);
239 select st_collect(substr(@d,110,47));
240 ERROR SR001: There's no spatial reference system with SRID 825307441.
241 select st_collect(left(@d,42));
242 ERROR SR001: There's no spatial reference system with SRID 825307441.
243 select st_collect(right(@d,42));
244 ERROR SR001: There's no spatial reference system with SRID 825307441.