| 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. |