[MDEV-17424] JSON_ARRAY produces incorrect and inconsistant output for geometry columns Created: 2018-10-11  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.3.9, 10.2, 10.3
Fix Version/s: 10.4

Type: Bug Priority: Major
Reporter: Mark Drake Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: None
Environment:

Windows x64

Attachments: File JSON_AGG_GEOMETRY.sql    

 Description    

MariaDB [appdev4db]> drop database if exists appdev4db;
 
Query OK, 1 row affected (0.022 sec)
 
 
 
MariaDB [(none)]> create database appdev4db;
 
Query OK, 1 row affected (0.002 sec)
 
 
 
MariaDB [(none)]> use appdev4db;
 
Database changed
 
MariaDB [appdev4db]> create table T1 (C1 INT, C2 CHAR(4), C3 GEOMETRY);
 
Query OK, 0 rows affected (0.033 sec)
 
 
 
MariaDB [appdev4db]> insert into T1 values (1,'AAAA',ST_GeomFromGeoJSON('{"type": "Point", "coordinates": [74.6466372, 16.8241727]}'));
 
Query OK, 1 row affected (0.005 sec)
 
 
 
MariaDB [appdev4db]> select * from T1;
 
+------+------+---------------------------+
 
| C1   | C2   | C3                        |
 
+------+------+---------------------------+
 
|    1 | AAAA |        !ÿ■Çb⌐R@è┴h√ⁿ╥0@ |
 
+------+------+---------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [appdev4db]> select JSON_ARRAY(C1,C2,C3) from T1;
 
+---------------------------------------------------------------------------------------+
 
| JSON_ARRAY(C1,C2,C3)                                                                  |
 
+---------------------------------------------------------------------------------------+
 
| [1, "AAAA", "\u0000\u0000\u0000\u0000\u0001\u0001\u0000\u0000\u0000!ÿ■Çb⌐R@è┴h√ⁿ╥0@"] |
 
+---------------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [appdev4db]> select JSON_ARRAY(ST_ASGEOJSON(C3)) from T1;
 
+----------------------------------------------------------------------+
 
| JSON_ARRAY(ST_ASGEOJSON(C3))                                         |
 
+----------------------------------------------------------------------+
 
| ["{\"type\": \"Point\", \"coordinates\": [74.6466372, 16.8241727]}"] |
 
+----------------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [appdev4db]> select JSON_ARRAY(JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
 
+--------------------------------------------------------------+
 
| JSON_ARRAY(JSON_QUERY(ST_ASGEOJSON(C3),'$'))                 |
 
+--------------------------------------------------------------+
 
| [{"type": "Point", "coordinates": [74.6466372, 16.8241727]}] |
 
+--------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [appdev4db]> select JSON_ARRAY(C1,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
 
+-----------------------------------------------------------------+
 
| JSON_ARRAY(C1,JSON_QUERY(ST_ASGEOJSON(C3),'$'))                 |
 
+-----------------------------------------------------------------+
 
| [1, {"type": "Point", "coordinates": [74.6466372, 16.8241727]}] |
 
+-----------------------------------------------------------------+
 
1 row in set (0.001 sec)
 
 
 
MariaDB [appdev4db]> select JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
 
+---------------------------------------------------------------------------------+
 
| JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$'))                              |
 
+---------------------------------------------------------------------------------+
 
| [1, "AAAA", "{\"type\": \"Point\", \"coordinates\": [74.6466372, 16.8241727]}"] |
 
+---------------------------------------------------------------------------------+
 
1 row in set (0.001 sec)

There a number of releated problems exposed here..

The first query returns the geometry objects s an escaped string. While this it technically not incorrect I would have expected it to return the GeoJSON repsentation of the column automatically, or at the very least the repesentation output by ST_ASTEXT (If the conversion to GeoJSON is lossey).

The second query returns the location object as GEOJSON, but the output of ST_ASGEOJSON is treated as a string, rathe than as a JSON object by JSON_ARRAY. Again this is not useful.

The thrid query applies HACK to attempt to force JSON_ARRAY to treat the output of ST_ASGEOJSON as JSON. It works when selecting one column, and when selecting 2 columns but when the JSON is the third column selected it reverts to interpreitng the output of JSON_QUERY as string rather than as JSON.

 Comments   
Comment by Alice Sherepa [ 2018-10-11 ]

I can reproduce the problem on 10.2, 10.3:

 10.3 4de0d920bede330ddf7790d0aee4bf90a0
 
 
 
MariaDB [test]> select JSON_ARRAY(C1,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
 
+-----------------------------------------------------------------+
 
| JSON_ARRAY(C1,JSON_QUERY(ST_ASGEOJSON(C3),'$'))                 |
 
+-----------------------------------------------------------------+
 
| [1, {"type": "Point", "coordinates": [74.6466372, 16.8241727]}] |
 
+-----------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> select JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
 
+---------------------------------------------------------------------------------+
 
| JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$'))                              |
 
+---------------------------------------------------------------------------------+
 
| [1, "AAAA", "{\"type\": \"Point\", \"coordinates\": [74.6466372, 16.8241727]}"] |
 
+---------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)
 
 
 
MariaDB [test]> show variables like '%col%';
 
+----------------------------------+-------------------+
 
| Variable_name                    | Value             |
 
+----------------------------------+-------------------+
 
| collation_connection             | utf8_general_ci   |
 
| collation_database               | latin1_swedish_ci |
 
| collation_server                 | latin1_swedish_ci |
 
| column_compression_threshold     | 100               |
 
| column_compression_zlib_level    | 6                 |
 
| column_compression_zlib_strategy | DEFAULT_STRATEGY  |
 
| column_compression_zlib_wrap     | OFF               |
 
| protocol_version                 | 10                |
 
| proxy_protocol_networks          |                   |
 
| slave_compressed_protocol        | OFF               |
 
+----------------------------------+-------------------+
 
10 rows in set (0.002 sec)
 
 
 
MariaDB [test]> set collation_connection ='latin1_swedish_ci';
 
Query OK, 0 rows affected (0.000 sec)
 
 
 
MariaDB [test]> select JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$')) from T1;
 
+-------------------------------------------------------------------------+
 
| JSON_ARRAY(C1,C2,JSON_QUERY(ST_ASGEOJSON(C3),'$'))                      |
 
+-------------------------------------------------------------------------+
 
| [1, "AAAA", {"type": "Point", "coordinates": [74.6466372, 16.8241727]}] |
 
+-------------------------------------------------------------------------+
 
1 row in set (0.001 sec)

Generated at Thu Feb 08 08:36:21 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.