Details
Description
The following works in MySQL 8 but fails in MariaDB:
SELECT |
lat,
|
lon
|
FROM |
my_table
|
WHERE |
ST_CONTAINS(
|
ST_GeomFromGeoJSON(
|
'{"geometry":{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"},"properties":{},"type":"Feature"}', |
2, 0
|
),
|
POINT(lon, lat)
|
)
|
MariaDB requires the following:
SELECT |
lat,
|
lon
|
FROM |
my_table
|
WHERE |
ST_CONTAINS(
|
ST_GeomFromGeoJSON(
|
'{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"}', |
2, 0
|
),
|
POINT(lon, lat)
|
);
|
Yes, I recognize that only the "geometry" property in the feature is necessary and also works in MySQL, but it caused quite a bit of head scratching and if the function names are going to be the same, then they should work the same.
I don't personally have MariaDB setup, I'm just using this functionality in my OSS project and helping a user troubleshoot so I've provided as much info as I can about their environment.
Quite right. I assume MySQL just ignores extra JSON fields. Also fails on 10.3 (without even a warning)
10.3
MariaDB [(none)]> select ST_GeomFromGeoJSON( '{"geometry":{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"},"properties":{},"type":"Feature"}', 2, 0 ) as x;
+------+
| x |
+------+
| NULL |
+------+
1 row in set (0.000 sec)
MariaDB [(none)]> show warnings;
Empty set (0.000 sec)
MariaDB [(none)]> select version();
+-----------------+
| version() |
+-----------------+
| 10.3.38-MariaDB |
+-----------------+
1 row in set (0.000 sec)
10.5
MariaDB [(none)]> select ST_GeomFromGeoJSON( '{"geometry":{"coordinates":[[[4.940319,51.603356],[4.89234,51.603196],[4.893885,51.576214],[4.940319,51.568532],[4.940319,51.603356]]],"type":"Polygon"},"properties":{},"type":"Feature"}', 2, 0 ) as x;
+------+
| x |
+------+
| NULL |
+------+
1 row in set (0.000 sec)
MariaDB [(none)]> show warnings;
+---------+------+------------------------+
| Level | Code | Message |
+---------+------+------------------------+
| Warning | 1474 | Name '
' has become '' |
+---------+------+------------------------+
1 row in set (0.000 sec)