Merge 10.2 into 10.3

4 files changed, 192 insertions, 2 deletions

diff --git a/mysql-test/main/gis-json.result b/mysql-test/main/gis-json.result
index d507a9994ff..e52a7c809c6 100644
--- a/mysql-test/main/gis-json.result
+++ b/mysql-test/main/gis-json.result
@@ -62,9 +62,9 @@ SELECT st_astext(st_geomfromgeojson('{ "type": "FeatureCollection", "features":
 st_astext(st_geomfromgeojson('{ "type": "FeatureCollection", "features": [{ "type": "Feature", "geometry": { "type": "Point", "coordinates": [102.0, 0.5] }, "properties": { "prop0": "value0" } }]}'))
 GEOMETRYCOLLECTION(POINT(102 0.5))
 SELECT ST_AsText(ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [5.3, 15.0, 4.3]}',5));
-ERROR HY000: Incorrect option value: '5' for function ST_GeometryFromJSON
+ERROR HY000: Incorrect option value: '5' for function ST_GeomFromGeoJSON
 SELECT ST_AsText(ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [5.3, 15.0, 4.3]}',1));
-ERROR 22023: Invalid GIS data provided to function ST_GeometryFromJSON.
+ERROR 22023: Invalid GIS data provided to function ST_GeomFromGeoJSON.
 SELECT ST_AsText(ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [5.3, 15.0, 4.3]}',2));
 ST_AsText(ST_GeomFromGeoJSON('{ "type": "Point", "coordinates": [5.3, 15.0, 4.3]}',2))
 POINT(5.3 15)
diff --git a/mysql-test/main/gis-precise.result b/mysql-test/main/gis-precise.result
index 32b536b6e7f..c72debb6f73 100644
--- a/mysql-test/main/gis-precise.result
+++ b/mysql-test/main/gis-precise.result
@@ -806,3 +806,114 @@ SRID(GEOMETRYFROMTEXT(' MULTIPOINT(8 4,5 0,7 8,6 9,3 4,7 3,5 5) '))));
 ASTEXT(ST_BUFFER(POLYGONFROMTEXT(' POLYGON((9 9,5 2,4 5,9 9))'),
 SRID(GEOMETRYFROMTEXT(' MULTIPOINT(8 4,5 0,7 8,6 9,3 4,7 3,5 5) '))))
 POLYGON((9 9,5 2,4 5,9 9))
+#
+# MDEV-13467 Feature request: Support for ST_Distance_Sphere()
+#
+SELECT ST_DISTANCE_SPHERE();
+ERROR 42000: Incorrect parameter count in the call to native function 'ST_DISTANCE_SPHERE'
+SELECT ST_DISTANCE_SPHERE(NULL);
+ERROR 42000: Incorrect parameter count in the call to native function 'ST_DISTANCE_SPHERE'
+SELECT ST_DISTANCE_SPHERE(NULL, NULL);
+ST_DISTANCE_SPHERE(NULL, NULL)
+NULL
+SELECT ST_DISTANCE_SPHERE(NULL, NULL, 3);
+ST_DISTANCE_SPHERE(NULL, NULL, 3)
+NULL
+SELECT ST_DISTANCE_SPHERE(NULL, 1, 3);
+ST_DISTANCE_SPHERE(NULL, 1, 3)
+NULL
+SELECT ST_DISTANCE_SPHERE(1, NULL, 3);
+ST_DISTANCE_SPHERE(1, NULL, 3)
+NULL
+SELECT ST_DISTANCE_SPHERE(1, 1);
+ERROR 22023: Invalid GIS data provided to function ST_Distance_Sphere.
+SELECT ST_DISTANCE_SPHERE(1, 1, 3);
+ERROR 22023: Invalid GIS data provided to function ST_Distance_Sphere.
+SELECT ST_DISTANCE_SPHERE(1, 1, NULL);
+ST_DISTANCE_SPHERE(1, 1, NULL)
+NULL
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)'));
+ERROR HY000: Internal error: st_distance_sphere
+# Test Points and radius
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'))
+157249.0357231545
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-1 -1)'), ST_GEOMFROMTEXT('POINT(-2 -2)')), 10);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-1 -1)'), ST_GEOMFROMTEXT('POINT(-2 -2)')), 10)
+157225.0865419108
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 1);
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 1)
+0.024682056391766436
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 0);
+ERROR HY000: Internal error: Radius must be greater than zero.
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), -1);
+ERROR HY000: Internal error: Radius must be greater than zero.
+# Test longitude/lattitude
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POINT(1 2)')), 10);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POINT(1 2)')), 10)
+157225.0865419108
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POINT(2 1)')), 10);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POINT(2 1)')), 10)
+222355.4901806686
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(1 2)')), 10);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(1 2)')), 10)
+222389.3645969269
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(2 1)'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(2 1)'))
+157249.0357231545
+# Test Points - Multipoints
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1)'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1)'))
+157249.0357231545
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'))
+157249.0357231545
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'))
+157249.0357231545
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2,1 1)'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2,1 1)'))
+157249.0357231545
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'), 1);
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'), 1)
+0.024682056391766436
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2,1 1)'), 1);
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2,1 1)'), 1)
+0.024682056391766436
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2, 1 1, 3 4)'), 1);
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2, 1 1, 3 4)'), 1)
+0.024682056391766436
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2, 1 1,5 6)'), 1);
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2, 1 1,5 6)'), 1)
+0.024682056391766436
+# Test Multipoints - Multipoints
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )'), ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )'));
+ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )'), ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )'))
+0
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )')), 10);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )')), 10)
+314282.5644496733
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )')), 10);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )')), 10)
+314282.5644496733
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )'),1), 17);
+TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )'),1), 17)
+0.04933028646581131
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )'),0);
+ERROR HY000: Internal error: Radius must be greater than zero.
+set @pt1 = ST_GeomFromText('POINT(190 -30)');
+set @pt2 = ST_GeomFromText('POINT(-30  50)');
+SELECT ST_Distance_Sphere(@pt1, @pt2);
+ERROR HY000: Out of range error: Longitude should be [-180,180] in function ST_Distance_Sphere.
+set @pt1 = ST_GeomFromText('POINT(135 -30)');
+set @pt2 = ST_GeomFromText('POINT(-30 91)');
+SELECT ST_Distance_Sphere(@pt1, @pt2);
+ERROR HY000: Out of range error: Latitude should be [-90,90] in function ST_Distance_Sphere.
+set @zenica   = ST_GeomFromText('POINT(17.907743 44.203438)');
+set @sarajevo = ST_GeomFromText('POINT(18.413076 43.856258)');
+SELECT TRUNCATE(ST_Distance_Sphere(@zenica, @sarajevo), 10);
+TRUNCATE(ST_Distance_Sphere(@zenica, @sarajevo), 10)
+55878.5933759170
+SELECT TRUNCATE(ST_Distance_Sphere(@sarajevo, @zenica), 10);
+TRUNCATE(ST_Distance_Sphere(@sarajevo, @zenica), 10)
+55878.5933759170
diff --git a/mysql-test/main/gis-precise.test b/mysql-test/main/gis-precise.test
index 24f4ac9113e..da72a0c4d8a 100644
--- a/mysql-test/main/gis-precise.test
+++ b/mysql-test/main/gis-precise.test
@@ -394,3 +394,81 @@ with cte1 as(   select (st_symdifference(point(1,1),point(1,1))) as a1 ), cte2 a
 
 --source include/gis_debug.inc
 
+
+--echo #
+--echo # MDEV-13467 Feature request: Support for ST_Distance_Sphere()
+--echo #
+
+--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
+SELECT ST_DISTANCE_SPHERE();
+--error ER_WRONG_PARAMCOUNT_TO_NATIVE_FCT
+SELECT ST_DISTANCE_SPHERE(NULL);
+SELECT ST_DISTANCE_SPHERE(NULL, NULL);
+# NULL args and optional radius will return NULL
+SELECT ST_DISTANCE_SPHERE(NULL, NULL, 3);
+# At least 1 NULL arg and optional radius will return NULL
+SELECT ST_DISTANCE_SPHERE(NULL, 1, 3);
+# At least 1 NULL arg and optional radius will return NULL
+SELECT ST_DISTANCE_SPHERE(1, NULL, 3);
+# Return ER_GIS_INVALID_DATA for invalid geometry
+--error ER_GIS_INVALID_DATA
+SELECT ST_DISTANCE_SPHERE(1, 1);
+--error ER_GIS_INVALID_DATA
+SELECT ST_DISTANCE_SPHERE(1, 1, 3);
+# Return NULL if radius is NULL
+SELECT ST_DISTANCE_SPHERE(1, 1, NULL);
+# Wrong geometry
+--error ER_INTERNAL_ERROR
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('LINESTRING(0 0, 1 1)'));
+
+--echo # Test Points and radius
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'));
+# make bb x86 happy
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(-1 -1)'), ST_GEOMFROMTEXT('POINT(-2 -2)')), 10);
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 1);
+--error ER_INTERNAL_ERROR
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), 0);
+--error ER_INTERNAL_ERROR
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('POINT(1 1)'), -1);
+--echo # Test longitude/lattitude
+# make bb x86 happy
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POINT(1 2)')), 10);
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 1)'), ST_GEOMFROMTEXT('POINT(2 1)')), 10);
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(1 2)')), 10);
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(1 0)'), ST_GEOMFROMTEXT('POINT(2 1)'));
+--echo # Test Points - Multipoints
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1)'));
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 1)'), ST_GEOMFROMTEXT('POINT(0 0)'));
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'));
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2,1 1)'));
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(1 1,2 2)'), 1);
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2,1 1)'), 1);
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2, 1 1, 3 4)'), 1);
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('POINT(0 0)'), ST_GEOMFROMTEXT('MULTIPOINT(2 2, 1 1,5 6)'), 1);
+--echo # Test Multipoints - Multipoints
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )'), ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )'));
+# make bb x86 happy
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(3 4,8 9 )')), 10);
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )')), 10);
+# make bb x86 happy
+SELECT TRUNCATE(ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )'),1), 17);
+--error ER_INTERNAL_ERROR
+SELECT ST_DISTANCE_SPHERE(ST_GEOMFROMTEXT('MULTIPOINT(1 2,1 1 )'), ST_GEOMFROMTEXT('MULTIPOINT(8 9,3 4 )'),0);
+
+# Longitude out of range [-180,180]
+set @pt1 = ST_GeomFromText('POINT(190 -30)');
+set @pt2 = ST_GeomFromText('POINT(-30  50)');
+--error ER_STD_OUT_OF_RANGE_ERROR
+SELECT ST_Distance_Sphere(@pt1, @pt2);
+
+# Latitude out of range [-90, 90]
+set @pt1 = ST_GeomFromText('POINT(135 -30)');
+set @pt2 = ST_GeomFromText('POINT(-30 91)');
+--error ER_STD_OUT_OF_RANGE_ERROR
+SELECT ST_Distance_Sphere(@pt1, @pt2);
+
+# POINT in form (longitude[-180, 180] latitude[-90, 90])
+set @zenica   = ST_GeomFromText('POINT(17.907743 44.203438)');
+set @sarajevo = ST_GeomFromText('POINT(18.413076 43.856258)');
+SELECT TRUNCATE(ST_Distance_Sphere(@zenica, @sarajevo), 10);
+SELECT TRUNCATE(ST_Distance_Sphere(@sarajevo, @zenica), 10);
diff --git a/mysql-test/main/userstat.test b/mysql-test/main/userstat.test
index 42fe1c2ad17..cc7ddd58e11 100644
--- a/mysql-test/main/userstat.test
+++ b/mysql-test/main/userstat.test
@@ -5,6 +5,7 @@
 
 -- source include/have_innodb.inc
 -- source include/have_log_bin.inc
+-- source include/have_perfschema.inc
 
 select variable_value from information_schema.global_status where variable_name="handler_read_key" into @global_read_key;
 show columns from information_schema.client_statistics;