| 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)); |