mirror of
https://github.com/MariaDB/server.git
synced 2025-06-06 12:54:41 +02:00
bc87abc381
In spatial_utility_function_collect, temporarily exclude the two affected queries because the DISTINCT flag isn't consistently passed to the Item_func_collect class. Created MDEV-36695 to address that bug. In spatial_utility_function_isvalid, add an alias for queries to allow the test to pass under view protocol.
222 lines
9.1 KiB
Text
222 lines
9.1 KiB
Text
# Copyright (c) 2021, Oracle and/or its affiliates
|
|
# Copyright (c) 2024, MariaDB Corporation.
|
|
#
|
|
# This program is free software; you can redistribute it and/or modify
|
|
# it under the terms of the GNU General Public License as published by
|
|
# the Free Software Foundation; version 2 of the License.
|
|
#
|
|
# This program is distributed in the hope that it will be useful,
|
|
# but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
# GNU General Public License for more details.
|
|
#
|
|
# You should have received a copy of the GNU General Public License
|
|
# along with this program; if not, write to the Free Software
|
|
# Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1335 USA
|
|
|
|
# This test is for the ST_COLLECT aggregation function, introduced in WL#13454
|
|
|
|
--echo # setup of data for tests involving simple aggregations and group by
|
|
CREATE TABLE table_simple_aggregation ( running_number INTEGER NOT NULL
|
|
AUTO_INCREMENT, grouping_condition INTEGER, location GEOMETRY , PRIMARY KEY (
|
|
running_number));
|
|
INSERT INTO table_simple_aggregation ( grouping_condition, location ) VALUES
|
|
( 0,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
|
|
( 1,ST_GEOMFROMTEXT('POINT(0 0)',4326)),
|
|
( 0,ST_GEOMFROMTEXT('POINT(1 0)',4326)),
|
|
( 1,ST_GEOMFROMTEXT('POINT(2 0)',4326)),
|
|
( 0,ST_GEOMFROMTEXT('POINT(3 0)',4326));
|
|
|
|
--echo # Functional requirement F-4: ST_COLLECT shall support simple table
|
|
--echo # aggregations
|
|
--echo # result shall be 1
|
|
SELECT ST_EQUALS( (SELECT ST_COLLECT( location ) AS t FROM
|
|
table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT(0 0,0 0,1 0,2
|
|
0,3 0) ',4326)) c;
|
|
--echo # Functional requirement F-8 Shall support DISTINCT in aggregates
|
|
--echo # result shall be 1
|
|
SELECT ST_EQUALS( (SELECT ST_COLLECT( DISTINCT location ) AS t FROM
|
|
table_simple_aggregation) , ST_GEOMFROMTEXT('MULTIPOINT(0 0,1 0,2 0,3
|
|
0) ',4326)) c;
|
|
--echo # Functional requirement F-5: ST_COLLECT shall support group by, which
|
|
--echo # is given by aggregation machinery
|
|
--echo # result shall be
|
|
--echo # MULTIPOINT(0 0,1 0,3 0)
|
|
--echo # MULTIPOINT(2 0,0 0)
|
|
--sorted_result
|
|
SELECT ST_ASTEXT(ST_COLLECT( DISTINCT location )) AS t FROM
|
|
table_simple_aggregation GROUP BY grouping_condition;
|
|
|
|
INSERT INTO table_simple_aggregation (location) VALUES
|
|
( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
|
|
( NULL);
|
|
|
|
--echo # F-7 Aggregations with Nulls inside will just miss an element for each
|
|
--echo # Null
|
|
--echo # the result here shall be 1
|
|
SELECT ST_EQUALS((SELECT ST_COLLECT(LOCATION) AS T FROM
|
|
table_simple_aggregation), ST_GEOMFROMTEXT('GEOMETRYCOLLECTION( MULTIPOINT(0
|
|
0,1 0,3 0), MULTIPOINT(2 0,0 0), POINT(0 0))',4326)) c;
|
|
--echo # F-1 ST_COLLECT SHALL only return NULL if all elements are NULL or the
|
|
--echo # aggregate is empty.
|
|
--echo # as only a null is aggregated the result of the subquery shall be NULL
|
|
--echo # and the result of the whole query shall be 1
|
|
SELECT (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
|
|
location = NULL) IS NULL c;
|
|
--echo # as no element is aggregated the result of the subquery shall be NULL
|
|
--echo # and the result of the whole query shall be 1
|
|
SELECT (SELECT ST_COLLECT(location) AS t FROM table_simple_aggregation WHERE
|
|
st_srid(location)=2110) IS NULL c;
|
|
|
|
INSERT INTO table_simple_aggregation (location) VALUES
|
|
( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
|
|
( NULL),
|
|
( NULL);
|
|
SELECT ST_ASTEXT(ST_COLLECT(location) OVER ( ROWS BETWEEN 1 PRECEDING AND
|
|
CURRENT ROW)) c FROM table_simple_aggregation;
|
|
|
|
|
|
--echo Excercising multiple code paths.
|
|
--sorted_result
|
|
SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(running_number)
|
|
OVER() FROM table_simple_aggregation GROUP BY running_number;
|
|
--sorted_result
|
|
|
|
--echo # remove disable_view_protocol after fixing MDEV-36695
|
|
--disable_view_protocol
|
|
SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(grouping_condition)
|
|
OVER(), grouping_condition FROM table_simple_aggregation GROUP BY
|
|
grouping_condition;
|
|
--enable_view_protocol
|
|
|
|
--sorted_result
|
|
SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(grouping_condition) OVER(),
|
|
grouping_condition FROM table_simple_aggregation GROUP BY grouping_condition;
|
|
--sorted_result
|
|
SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(running_number) OVER() FROM
|
|
table_simple_aggregation GROUP BY running_number;
|
|
|
|
--enable_warnings
|
|
set session group_concat_max_len= 10;
|
|
|
|
SELECT ST_COLLECT( location ) AS t FROM table_simple_aggregation;
|
|
|
|
set session group_concat_max_len= 1048576;
|
|
--disable_warnings
|
|
|
|
--echo # Teardown of testing NULL data
|
|
DROP TABLE table_simple_aggregation;
|
|
|
|
|
|
--echo # Setup for testing handling of multiple SRS
|
|
CREATE TABLE multi_srs_table ( running_number INTEGER NOT NULL AUTO_INCREMENT,
|
|
geometry GEOMETRY , PRIMARY KEY ( running_number ));
|
|
INSERT INTO multi_srs_table( geometry ) VALUES
|
|
(ST_GEOMFROMTEXT('POINT(60 -24)' ,4326)),
|
|
(ST_GEOMFROMTEXT('POINT(61 -24)' ,4326)),
|
|
(ST_GEOMFROMTEXT('POINT(38 77)'));
|
|
|
|
--echo # F-2 a) If the elements in an aggregate is of different SRSs,
|
|
--echo # ST_COLLECT MUST raise ER_GIS_DIFFERENT_SRIDS.
|
|
--error ER_GIS_DIFFERENT_SRIDS_AGGREGATION
|
|
SELECT ST_ASTEXT(ST_COLLECT(geometry)) AS t FROM multi_srs_table;
|
|
--echo # F-2 b) If all the elements in an aggregate is of same SRS, ST_COLLECT
|
|
--echo # MUST return a result in that SRS.
|
|
--echo # result shall be one MULTIPOINT((60 -24),(61 -24)) with SRID 4326 and
|
|
--echo # one
|
|
--echo # Multipoint((38 77)) with SRID 0. There is some rounding issue on the
|
|
--echo # result, bug #31535105
|
|
--sorted_result
|
|
SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
|
|
multi_srs_table GROUP BY ST_SRID(geometry);
|
|
|
|
--echo Rollup needs all SRIDs to be the same.
|
|
--error ER_GIS_DIFFERENT_SRIDS_AGGREGATION
|
|
SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
|
|
multi_srs_table GROUP BY ST_SRID(geometry) WITH ROLLUP;
|
|
|
|
--echo # Triggering a codepath for geometrycollection in temp tables
|
|
INSERT INTO multi_srs_table( geometry ) VALUES
|
|
(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(60 -24))' ,4326));
|
|
--sorted_result
|
|
SELECT st_srid(geometry),ST_ASTEXT( geometry ) AS t FROM
|
|
multi_srs_table GROUP BY ST_SRID(geometry);
|
|
|
|
|
|
--echo #teardown of testing handling of multiple SRS
|
|
DROP TABLE multi_srs_table;
|
|
|
|
--echo # setup of testing handling different geometry types
|
|
CREATE TABLE simple_table ( running_number INTEGER NOT NULL AUTO_INCREMENT ,
|
|
geo GEOMETRY, PRIMARY KEY ( RUNNING_NUMBER));
|
|
INSERT INTO simple_table ( geo) VALUES
|
|
(ST_GEOMFROMTEXT('POINT(0 0)')),
|
|
(ST_GEOMFROMTEXT('LINESTRING(1 0, 1 1)')),
|
|
(ST_GEOMFROMTEXT('LINESTRING(2 0, 2 1)')),
|
|
(ST_GEOMFROMTEXT('POLYGON((3 0, 0 0, 0 3, 3 3, 3 0))')),
|
|
(ST_GEOMFROMTEXT('POLYGON((4 0, 0 0, 0 4, 4 4, 4 0))')),
|
|
(ST_GEOMFROMTEXT('MULTIPOINT(5 0)')),
|
|
(ST_GEOMFROMTEXT('MULTIPOINT(6 0)')),
|
|
(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY')),
|
|
(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION EMPTY'));
|
|
|
|
|
|
--echo # Functional requirement F-9 a, b, and c ) An aggregation containing
|
|
--echo # more than one type of geometry or any MULTI is GEOMETRYCOLLECTION, if
|
|
--echo # it only contains a single type of POINTS, LINESTRINGS or POLYGONS it
|
|
--echo # will be a MULTI of the same kind.
|
|
--echo # MP: Multipoint
|
|
--echo # MPoly: Multipolygon
|
|
--echo # MLS: Multilinestring
|
|
--echo # GC: geometrycollection
|
|
--echo # Functional requirement F-6 shall support window functions
|
|
--echo # result is expected for come in this order: MP, GC, MLS, GC, MPpoly,
|
|
--echo # GC, GC, GC, GC
|
|
SELECT ST_ASTEXT(ST_COLLECT(geo) OVER( ORDER BY running_number ROWS BETWEEN 1
|
|
PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table;
|
|
--echo # with DISTINCT this result is expected to be:
|
|
--echo # MP, GC, MLS, GC, MPpoly, GC, GC, GC, GC with only one EMPTY GC
|
|
|
|
--echo # remove disable_view_protocol after fixing MDEV-36695
|
|
--disable_view_protocol
|
|
SELECT ST_ASTEXT(ST_COLLECT(DISTINCT geo) OVER( ORDER BY running_number ROWS BETWEEN 1
|
|
PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table;
|
|
--enable_view_protocol
|
|
|
|
--echo # Exercising the "copy" constructor
|
|
SELECT ST_ASTEXT(ST_COLLECT(geo)) FROM simple_table GROUP BY geo WITH ROLLUP;
|
|
|
|
--echo # Casting Geometry as decimal invokes val_decimal()
|
|
--error ER_ILLEGAL_PARAMETER_DATA_TYPE_FOR_OPERATION
|
|
SELECT CAST(ST_COLLECT(geo) AS DECIMAL ) FROM simple_table;
|
|
|
|
DROP TABLE simple_table;
|
|
|
|
--echo #
|
|
--echo # MDEV-35102 CREATE TABLE AS SELECT ST_collect ... does not work
|
|
--echo #
|
|
SELECT ST_astext(ST_collect(( POINTFROMTEXT(' POINT( 4 1 ) ') )));
|
|
CREATE TABLE tb1 AS SELECT (ST_collect(( POINTFROMTEXT(' POINT( 4 1 ) ') )) );
|
|
DROP TABLE tb1;
|
|
|
|
--echo #
|
|
--echo # MDEV-35975 Server crashes after CREATE VIEW as SELECT ST_COLLECT
|
|
--echo #
|
|
create view v1 as SELECT ST_COLLECT(ST_GEOMFROMTEXT('POINT(0 0)'));
|
|
drop view v1;
|
|
create view v1 as SELECT GROUP_CONCAT(ST_GEOMFROMTEXT('POINT(0 0)'));
|
|
drop view v1;
|
|
|
|
--echo #
|
|
--echo # MDEV-36167 Assertion `0' failed in Item_sum_str::reset_field after selecting st_collect + group by
|
|
--echo #
|
|
CREATE TABLE t1 (a int, p point);
|
|
INSERT INTO t1 (a, p) VALUES (0,st_geomfromtext('POINT(1 1)')), ( 1,st_geomfromtext('POINT(0 0)')), ( 0,st_geomfromtext('POINT(1 1)'));
|
|
SELECT st_astext(ST_COLLECT(p)) FROM t1 GROUP BY a;
|
|
DROP TABLE t1;
|
|
|
|
--echo #
|
|
--echo # MDEV-36491 Server crashes in Item_func_group_concat::print
|
|
--echo #
|
|
--error ER_INVALID_GROUP_FUNC_USE
|
|
SELECT 1 FROM dual WHERE group_concat(1, 1);
|