Debian Codegraph @

mysql-test/suite/gis/t/st_collect.test

raw
1 # This test is for the ST_COLLECT aggregation function, introduced in WL#13454
2
3 --echo # setup of data for tests involving simple aggregations and group by
4 CREATE TABLE table_simple_aggregation ( running_number INTEGER NOT NULL
5 AUTO_INCREMENT, grouping_condition INTEGER, location GEOMETRY , PRIMARY KEY (
6 running_number));
7 INSERT INTO table_simple_aggregation ( grouping_condition, location ) VALUES
8 ( 0,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
9 ( 1,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
10 ( 0,ST_GEOMFROMTEXT('POINT(1 0)',4326)),
11 ( 1,ST_GEOMFROMTEXT('POINT(2 0)',4326)),
12 ( 0,ST_GEOMFROMTEXT('POINT(3 0)',4326));
13
14 --echo # Functional requirement F-4: ST_COLLECT shall support simple table
15 --echo # aggregations
16 --echo # result shall be 1
17 SELECT 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 --echo # Functional requirement F-8 Shall support DISTINCT in aggregates
21 --echo # result shall be 1
22 SELECT ST_EQUALS( (SELECT ST_COLLECT( DISTINCT location ) AS t FROM
23 table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT((0 0),(1 0),(2 0),(3
24 0)) ',4326));
25 --echo # Functional requirement F-5: ST_COLLECT shall support group by, which
26 --echo # is given by aggregation machinery
27 --echo # result shall be
28 --echo # MULTIPOINT((0 0),(1 0),(3 0))
29 --echo # MULTIPOINT((2 0),(0 0))
30 --sorted_result
31 SELECT ST_ASTEXT(ST_COLLECT( DISTINCT location )) AS t FROM
32 table_simple_aggregation GROUP BY grouping_condition;
33
34 --echo # Distinct with rollup
35 SELECT st_astext(ST_COLLECT( distinct location )) AS t from
36 table_simple_aggregation group by st_latitude(location) with rollup;
37
38
39 INSERT INTO table_simple_aggregation (location) VALUES
40 ( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
41 ( NULL);
42
43 --echo # F-7 Aggregations with Nulls inside will just miss an element for each
44 --echo # Null
45 --echo # the result here shall be 1
46 SELECT ST_EQUALS((SELECT ST_COLLECT(LOCATION) AS T FROM
47 table_simple_aggregation), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION( MULTIPOINT((0
48 0),(1 0),(3 0)), MULTIPOINT((2 0),(0 0)), POINT(0 0))',4326));
49 --echo # F-1 ST_COLLECT SHALL only return NULL if all elements are NULL or the
50 --echo # aggregate is empty.
51 --echo # as only a null is aggregated the result of the subquery shall be NULL
52 --echo # and the result of the whole query shall be 1
53 SELECT (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
54 location = NULL) IS NULL;
55 --echo # as no element is aggregated the result of the subquery shall be NULL
56 --echo # and the result of the whole query shall be 1
57 SELECT (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
58 st_srid(location)=2110) IS NULL;
59
60 INSERT INTO table_simple_aggregation (location) VALUES
61 ( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
62 ( NULL),
63 ( NULL);
64 SELECT ST_ASTEXT(ST_COLLECT(location) OVER ( ROWS BETWEEN 1 PRECEDING AND
65 CURRENT ROW)) FROM table_simple_aggregation;
66
67
68 --echo Excercising multiple code paths.
69 --sorted_result
70 SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(running_number)
71 OVER() FROM table_simple_aggregation GROUP BY running_number;
72 --sorted_result
73 SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(grouping_condition)
74 OVER(), grouping_condition FROM table_simple_aggregation GROUP BY
75 grouping_condition;
76 --sorted_result
77 SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(grouping_condition) OVER(),
78 grouping_condition FROM table_simple_aggregation GROUP BY grouping_condition;
79 --sorted_result
80 SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(running_number) OVER() FROM
81 table_simple_aggregation GROUP BY running_number;
82
83
84
85 --echo # Teardown of testing NULL data
86 DROP TABLE table_simple_aggregation;
87
88
89 --echo # Setup for testing handling of multiple SRS
90 CREATE TABLE multi_srs_table ( running_number INTEGER NOT NULL AUTO_INCREMENT,
91 geometry GEOMETRY , PRIMARY KEY ( running_number ));
92 INSERT INTO multi_srs_table( geometry ) VALUES
93 (ST_GEOMFROMTEXT('POINT(60 -24)' ,4326)),
94 (ST_GEOMFROMTEXT('POINT(61 -24)' ,4326)),
95 (ST_GEOMFROMTEXT('POINT(38 77)'));
96
97 --echo # F-2 a) If the elements in an aggregate is of different SRSs,
98 --echo # ST_COLLECT MUST raise ER_GIS_DIFFERENT_SRIDS.
99 --error ER_GIS_DIFFERENT_SRIDS_AGGREGATION
100 SELECT ST_ASTEXT(ST_COLLECT(geometry)) AS t FROM multi_srs_table;
101 --echo # F-2 b) If all the elements in an aggregate is of same SRS, ST_COLLECT
102 --echo # MUST return a result in that SRS.
103 --echo # result shall be one MULTIPOINT((60 -24),(61 -24)) with SRID 4326 and
104 --echo # one
105 --echo # Multipoint((38 77)) with SRID 0. There is some rounding issue on the
106 --echo # result, bug #31535105
107 --sorted_result
108 SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
109 multi_srs_table GROUP BY ST_SRID(geometry);
110
111 --echo Rollup needs all SRIDs to be the same.
112 --error ER_GIS_DIFFERENT_SRIDS_AGGREGATION
113 SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
114 multi_srs_table GROUP BY ST_SRID(geometry) WITH ROLLUP;
115
116 --echo # Triggering a codepath for geometrycollection in temp tables
117 INSERT INTO multi_srs_table( geometry ) VALUES
118 (ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(60 -24))' ,4326));
119 --sorted_result
120 SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
121 multi_srs_table GROUP BY ST_SRID(geometry);
122
123
124 --echo #teardown of testing handling of multiple SRS
125 DROP TABLE multi_srs_table;
126
127 --echo # setup of testing handling different geometry types
128 CREATE TABLE simple_table ( running_number INTEGER NOT NULL AUTO_INCREMENT ,
129 geo GEOMETRY, PRIMARY KEY ( RUNNING_NUMBER));
130 INSERT INTO simple_table ( geo) VALUES
131 (ST_GEOMFROMTEXT('POINT(0 0)')),
132 (ST_GEOMFROMTEXT('LINESTRING(1 0, 1 1)')),
133 (ST_GEOMFROMTEXT('LINESTRING(2 0, 2 1)')),
134 (ST_GEOMFROMTEXT('POLYGON((3 0, 0 0, 0 3, 3 3, 3 0))')),
135 (ST_GEOMFROMTEXT('POLYGON((4 0, 0 0, 0 4, 4 4, 4 0))')),
136 (ST_GEOMFROMTEXT('MULTIPOINT(5 0)')),
137 (ST_GEOMFROMTEXT('MULTIPOINT(6 0)')),
138 (ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')),
139 (ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
140
141
142 --echo # Functional requirement F-9 a, b, and c ) An aggregation containing
143 --echo # more than one type of geometry or any MULTI is GEOMETRYCOLLECTION, if
144 --echo # it only contains a single type of POINTS, LINESTRINGS or POLYGONS it
145 --echo # will be a MULTI of the same kind.
146 --echo # MP: Multipoint
147 --echo # MPoly: Multipolygon
148 --echo # MLS: Multilinestring
149 --echo # GC: geometrycollection
150 --echo # Functional requirement F-6 shall support window functions
151 --echo # result is expected for come in this order: MP, GC, MLS, GC, MPpoly,
152 --echo # GC, GC, GC, GC
153 SELECT ST_ASTEXT(ST_COLLECT(geo) OVER( ORDER BY running_number ROWS BETWEEN 1
154 PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table;
155 --echo # with DISTINCT this result is expected to be:
156 --echo # GEMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
157 --echo # GEMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
158 SELECT ST_ASTEXT(ST_COLLECT( DISTINCT geo) OVER( ORDER BY running_number ROWS
159 BETWEEN 1 PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table WHERE
160 ST_EQUALS(geo,ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
161
162 --echo # Exercising the "copy" constructor
163 SELECT ST_ASTEXT(ST_COLLECT(geo)) FROM simple_table GROUP BY geo WITH ROLLUP;
164
165 --echo # Casting Geometry as decimal invokes val_decimal()
166 SELECT CAST(ST_COLLECT(geo) AS DECIMAL ) FROM simple_table;
167
168 DROP TABLE simple_table;
169
170 set @d:=repeat(1,228);
171 --error ER_SRS_NOT_FOUND
172 select st_collect(substr(@d,110,47));
173 --error ER_SRS_NOT_FOUND
174 select st_collect(left(@d,42));
175 --error ER_SRS_NOT_FOUND
176 select st_collect(right(@d,42));