mirror of
https://github.com/MariaDB/server.git
synced 2025-10-31 02:46:29 +01: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.
262 lines
10 KiB
Text
262 lines
10 KiB
Text
# 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));
|
|
# Functional requirement F-4: ST_COLLECT shall support simple table
|
|
# aggregations
|
|
# 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;
|
|
c
|
|
1
|
|
# Functional requirement F-8 Shall support DISTINCT in aggregates
|
|
# 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;
|
|
c
|
|
1
|
|
# Functional requirement F-5: ST_COLLECT shall support group by, which
|
|
# is given by aggregation machinery
|
|
# result shall be
|
|
# MULTIPOINT(0 0,1 0,3 0)
|
|
# MULTIPOINT(2 0,0 0)
|
|
SELECT ST_ASTEXT(ST_COLLECT( DISTINCT location )) AS t FROM
|
|
table_simple_aggregation GROUP BY grouping_condition;
|
|
t
|
|
MULTIPOINT(0 0,1 0,3 0)
|
|
MULTIPOINT(0 0,2 0)
|
|
INSERT INTO table_simple_aggregation (location) VALUES
|
|
( ST_GEOMFROMTEXT('POINT(0 -0)' ,4326)),
|
|
( NULL);
|
|
# F-7 Aggregations with Nulls inside will just miss an element for each
|
|
# Null
|
|
# 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;
|
|
c
|
|
1
|
|
# F-1 ST_COLLECT SHALL only return NULL if all elements are NULL or the
|
|
# aggregate is empty.
|
|
# as only a null is aggregated the result of the subquery shall be NULL
|
|
# 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;
|
|
c
|
|
1
|
|
# as no element is aggregated the result of the subquery shall be NULL
|
|
# 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;
|
|
c
|
|
1
|
|
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;
|
|
c
|
|
MULTIPOINT(0 0)
|
|
MULTIPOINT(0 0,0 0)
|
|
MULTIPOINT(0 0,1 0)
|
|
MULTIPOINT(1 0,2 0)
|
|
MULTIPOINT(2 0,3 0)
|
|
MULTIPOINT(3 0,0 -0)
|
|
MULTIPOINT(0 -0)
|
|
MULTIPOINT(0 -0)
|
|
MULTIPOINT(0 -0)
|
|
NULL
|
|
Excercising multiple code paths.
|
|
SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(running_number)
|
|
OVER() FROM table_simple_aggregation GROUP BY running_number;
|
|
geo SUM(running_number)
|
|
OVER()
|
|
MULTIPOINT(0 -0) 55
|
|
MULTIPOINT(0 -0) 55
|
|
MULTIPOINT(0 0) 55
|
|
MULTIPOINT(0 0) 55
|
|
MULTIPOINT(1 0) 55
|
|
MULTIPOINT(2 0) 55
|
|
MULTIPOINT(3 0) 55
|
|
NULL 55
|
|
NULL 55
|
|
NULL 55
|
|
# remove disable_view_protocol after fixing MDEV-36695
|
|
SELECT ST_ASTEXT(ST_COLLECT(DISTINCT location)) AS geo, SUM(grouping_condition)
|
|
OVER(), grouping_condition FROM table_simple_aggregation GROUP BY
|
|
grouping_condition;
|
|
geo SUM(grouping_condition)
|
|
OVER() grouping_condition
|
|
MULTIPOINT(0 -0) 1 NULL
|
|
MULTIPOINT(0 0,1 0,3 0) 1 0
|
|
MULTIPOINT(0 0,2 0) 1 1
|
|
SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(grouping_condition) OVER(),
|
|
grouping_condition FROM table_simple_aggregation GROUP BY grouping_condition;
|
|
geo SUM(grouping_condition) OVER() grouping_condition
|
|
MULTIPOINT(0 -0,0 -0) 1 NULL
|
|
MULTIPOINT(0 0,1 0,3 0) 1 0
|
|
MULTIPOINT(0 0,2 0) 1 1
|
|
SELECT ST_ASTEXT(ST_COLLECT(location)) AS geo, SUM(running_number) OVER() FROM
|
|
table_simple_aggregation GROUP BY running_number;
|
|
geo SUM(running_number) OVER()
|
|
MULTIPOINT(0 -0) 55
|
|
MULTIPOINT(0 -0) 55
|
|
MULTIPOINT(0 0) 55
|
|
MULTIPOINT(0 0) 55
|
|
MULTIPOINT(1 0) 55
|
|
MULTIPOINT(2 0) 55
|
|
MULTIPOINT(3 0) 55
|
|
NULL 55
|
|
NULL 55
|
|
NULL 55
|
|
set session group_concat_max_len= 10;
|
|
SELECT ST_COLLECT( location ) AS t FROM table_simple_aggregation;
|
|
t
|
|
NULL
|
|
Warnings:
|
|
Warning 1260 Row 1 was cut by st_collect()
|
|
set session group_concat_max_len= 1048576;
|
|
# Teardown of testing NULL data
|
|
DROP TABLE table_simple_aggregation;
|
|
# 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)'));
|
|
# F-2 a) If the elements in an aggregate is of different SRSs,
|
|
# ST_COLLECT MUST raise ER_GIS_DIFFERENT_SRIDS.
|
|
SELECT ST_ASTEXT(ST_COLLECT(geometry)) AS t FROM multi_srs_table;
|
|
ERROR HY000: Arguments to function st_collect( contains geometries with different SRIDs: 4326 and 0. All geometries must have the same SRID.
|
|
# F-2 b) If all the elements in an aggregate is of same SRS, ST_COLLECT
|
|
# MUST return a result in that SRS.
|
|
# result shall be one MULTIPOINT((60 -24),(61 -24)) with SRID 4326 and
|
|
# one
|
|
# Multipoint((38 77)) with SRID 0. There is some rounding issue on the
|
|
# result, bug #31535105
|
|
SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
|
|
multi_srs_table GROUP BY ST_SRID(geometry);
|
|
st_srid(geometry) t
|
|
0 MULTIPOINT(38 77)
|
|
4326 MULTIPOINT(60 -24,61 -24)
|
|
Rollup needs all SRIDs to be the same.
|
|
SELECT st_srid(geometry),ST_ASTEXT(ST_COLLECT( geometry )) AS t FROM
|
|
multi_srs_table GROUP BY ST_SRID(geometry) WITH ROLLUP;
|
|
ERROR HY000: Arguments to function st_collect( contains geometries with different SRIDs: 0 and 4326. All geometries must have the same SRID.
|
|
# Triggering a codepath for geometrycollection in temp tables
|
|
INSERT INTO multi_srs_table( geometry ) VALUES
|
|
(ST_GEOMFROMTEXT('GEOMETRYCOLLECTION(POINT(60 -24))' ,4326));
|
|
SELECT st_srid(geometry),ST_ASTEXT( geometry ) AS t FROM
|
|
multi_srs_table GROUP BY ST_SRID(geometry);
|
|
st_srid(geometry) t
|
|
0 POINT(38 77)
|
|
4326 POINT(60 -24)
|
|
#teardown of testing handling of multiple SRS
|
|
DROP TABLE multi_srs_table;
|
|
# 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'));
|
|
# Functional requirement F-9 a, b, and c ) An aggregation containing
|
|
# more than one type of geometry or any MULTI is GEOMETRYCOLLECTION, if
|
|
# it only contains a single type of POINTS, LINESTRINGS or POLYGONS it
|
|
# will be a MULTI of the same kind.
|
|
# MP: Multipoint
|
|
# MPoly: Multipolygon
|
|
# MLS: Multilinestring
|
|
# GC: geometrycollection
|
|
# Functional requirement F-6 shall support window functions
|
|
# result is expected for come in this order: MP, GC, MLS, GC, MPpoly,
|
|
# 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;
|
|
geocollect
|
|
MULTIPOINT(0 0)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 0,1 1))
|
|
MULTILINESTRING((1 0,1 1),(2 0,2 1))
|
|
GEOMETRYCOLLECTION(LINESTRING(2 0,2 1),POLYGON((3 0,0 0,0 3,3 3,3 0)))
|
|
MULTIPOLYGON(((3 0,0 0,0 3,3 3,3 0)),((4 0,0 0,0 4,4 4,4 0)))
|
|
GEOMETRYCOLLECTION(POLYGON((4 0,0 0,0 4,4 4,4 0)),MULTIPOINT(5 0))
|
|
GEOMETRYCOLLECTION(MULTIPOINT(5 0),MULTIPOINT(6 0))
|
|
GEOMETRYCOLLECTION(MULTIPOINT(6 0),GEOMETRYCOLLECTION EMPTY)
|
|
GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY,GEOMETRYCOLLECTION EMPTY)
|
|
# with DISTINCT this result is expected to be:
|
|
# MP, GC, MLS, GC, MPpoly, GC, GC, GC, GC with only one EMPTY GC
|
|
# remove disable_view_protocol after fixing MDEV-36695
|
|
SELECT ST_ASTEXT(ST_COLLECT(DISTINCT geo) OVER( ORDER BY running_number ROWS BETWEEN 1
|
|
PRECEDING AND CURRENT ROW)) AS geocollect FROM simple_table;
|
|
geocollect
|
|
MULTIPOINT(0 0)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(1 0,1 1))
|
|
MULTILINESTRING((1 0,1 1),(2 0,2 1))
|
|
GEOMETRYCOLLECTION(LINESTRING(2 0,2 1),POLYGON((3 0,0 0,0 3,3 3,3 0)))
|
|
MULTIPOLYGON(((3 0,0 0,0 3,3 3,3 0)),((4 0,0 0,0 4,4 4,4 0)))
|
|
GEOMETRYCOLLECTION(POLYGON((4 0,0 0,0 4,4 4,4 0)),MULTIPOINT(5 0))
|
|
GEOMETRYCOLLECTION(MULTIPOINT(5 0),MULTIPOINT(6 0))
|
|
GEOMETRYCOLLECTION(MULTIPOINT(6 0),GEOMETRYCOLLECTION EMPTY)
|
|
GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY)
|
|
# Exercising the "copy" constructor
|
|
SELECT ST_ASTEXT(ST_COLLECT(geo)) FROM simple_table GROUP BY geo WITH ROLLUP;
|
|
ST_ASTEXT(ST_COLLECT(geo))
|
|
MULTIPOINT(0 0)
|
|
MULTILINESTRING((2 0,2 1))
|
|
MULTILINESTRING((1 0,1 1))
|
|
MULTIPOLYGON(((3 0,0 0,0 3,3 3,3 0)))
|
|
MULTIPOLYGON(((4 0,0 0,0 4,4 4,4 0)))
|
|
GEOMETRYCOLLECTION(MULTIPOINT(5 0))
|
|
GEOMETRYCOLLECTION(MULTIPOINT(6 0))
|
|
GEOMETRYCOLLECTION(GEOMETRYCOLLECTION EMPTY,GEOMETRYCOLLECTION EMPTY)
|
|
GEOMETRYCOLLECTION(POINT(0 0),LINESTRING(2 0,2 1),LINESTRING(1 0,1 1),POLYGON((3 0,0 0,0 3,3 3,3 0)),POLYGON((4 0,0 0,0 4,4 4,4 0)),MULTIPOINT(5 0),MULTIPOINT(6 0),GEOMETRYCOLLECTION EMPTY,GEOMETRYCOLLECTION EMPTY)
|
|
# Casting Geometry as decimal invokes val_decimal()
|
|
SELECT CAST(ST_COLLECT(geo) AS DECIMAL ) FROM simple_table;
|
|
ERROR HY000: Illegal parameter data type geometry for operation 'decimal_typecast'
|
|
DROP TABLE simple_table;
|
|
#
|
|
# MDEV-35102 CREATE TABLE AS SELECT ST_collect ... does not work
|
|
#
|
|
SELECT ST_astext(ST_collect(( POINTFROMTEXT(' POINT( 4 1 ) ') )));
|
|
ST_astext(ST_collect(( POINTFROMTEXT(' POINT( 4 1 ) ') )))
|
|
MULTIPOINT(4 1)
|
|
CREATE TABLE tb1 AS SELECT (ST_collect(( POINTFROMTEXT(' POINT( 4 1 ) ') )) );
|
|
DROP TABLE tb1;
|
|
#
|
|
# MDEV-35975 Server crashes after CREATE VIEW as SELECT ST_COLLECT
|
|
#
|
|
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;
|
|
#
|
|
# MDEV-36167 Assertion `0' failed in Item_sum_str::reset_field after selecting st_collect + group by
|
|
#
|
|
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;
|
|
st_astext(ST_COLLECT(p))
|
|
MULTIPOINT(1 1,1 1)
|
|
MULTIPOINT(0 0)
|
|
DROP TABLE t1;
|
|
#
|
|
# MDEV-36491 Server crashes in Item_func_group_concat::print
|
|
#
|
|
SELECT 1 FROM dual WHERE group_concat(1, 1);
|
|
ERROR HY000: Invalid use of group function
|