[MDEV-27361] Hybrid functions with JSON arguments do not send format metadata Created: 2021-12-25  Updated: 2023-04-27

Status: Open
Project: MariaDB Server
Component/s: Data types, JSON
Affects Version/s: 10.5, 10.6, 10.7, 10.8
Fix Version/s: 10.5, 10.6

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Unresolved Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27018 IF and COALESCE lose "json" property Closed
relates to MDEV-27370 UNION looses JSON property Open

 Description   

I start mariadb --column-type-info test and run the following script:

DROP TABLE IF EXISTS t1;
 
CREATE TABLE t1 (a JSON);
 
INSERT INTO t1 VALUES ('{"a":"b"}');
 
SELECT a, JSON_COMPACT(a), COALESCE(a) FROM t1;

The output is:

MariaDB [test]> SELECT a, JSON_COMPACT(a), COALESCE(a) FROM t1;
 
Field   1:  `a`
 
Org_field:  `a`
 
Catalog:    `def`
 
Database:   `test`
 
Table:      `t1`
 
Org_table:  `t1`
 
Type:       BLOB (format=json)
 
Collation:  utf8_general_ci (33)
 
Length:     4294967295
 
Max_length: 9
 
Decimals:   0
 
Flags:      BLOB BINARY 
 
 
Field   2:  `JSON_COMPACT(a)`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       LONG_BLOB (format=json)
 
Collation:  utf8_general_ci (33)
 
Length:     4294967295
 
Max_length: 9
 
Decimals:   0
 
Flags:      BINARY 
 
 
Field   3:  `COALESCE(a)`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       LONG_BLOB
 
Collation:  utf8_general_ci (33)
 
Length:     4294967295
 
Max_length: 9
 
Decimals:   39
 
Flags:      BINARY 
 
 
+-----------+-----------------+-------------+
 
| a         | JSON_COMPACT(a) | COALESCE(a) |
 
+-----------+-----------------+-------------+
 
| {"a":"b"} | {"a":"b"}       | {"a":"b"}   |
 
+-----------+-----------------+-------------+
 
1 row in set (0.000 sec)

Notice, it prints format=json for the first and the second column, but it does not send format for the third column.

 Comments   
Comment by Alexander Barkov [ 2021-12-25 ]

Also repeatable with JSON_ARRAYAGG:

SELECT  JSON_ARRAYAGG(1) FROM t1;


Field   1:  `JSON_ARRAYAGG(1)`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       BLOB
 
Collation:  utf8_general_ci (33)
 
Length:     9437184
 
Max_length: 3
 
Decimals:   0
 
Flags:      
 
 
 
 
+------------------+
 
| JSON_ARRAYAGG(1) |
 
+------------------+
 
| [1]              |
 
+------------------+
 
1 row in set (0.001 sec)

Comment by Alexander Barkov [ 2021-12-25 ]

Also repeatable with JSON_OBJECTAGG:

SELECT  JSON_OBJECTAGG('a','b') FROM t1;


Field   1:  `JSON_OBJECTAGG('a','b')`
 
Org_field:  ``
 
Catalog:    `def`
 
Database:   ``
 
Table:      ``
 
Org_table:  ``
 
Type:       BLOB
 
Collation:  utf8_general_ci (33)
 
Length:     9437184
 
Max_length: 9
 
Decimals:   0
 
Flags:      
 
 
 
 
+-------------------------+
 
| JSON_OBJECTAGG('a','b') |
 
+-------------------------+
 
| {"a":"b"}               |
 
+-------------------------+

Generated at Thu Feb 08 09:52:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.