Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.3, 10.4
-
None
Description
MariaDB seems to return redundant double quotes when passing a JSON value to a JSON function.
Note, Oracle queries used below for comparison with MariaDB were tested with:
Oracle Database 18c Enterprise Edition Release 18.0.0.0.0 - Production Version 18.3.0.0.0
|
HAVING
SELECT JSON_ARRAY('1') AS a, JSON_ARRAY(JSON_ARRAY('1')); |
+-------+-----------------------------+
|
| a | JSON_ARRAY(JSON_ARRAY('1')) |
|
+-------+-----------------------------+
|
| ["1"] | [["1"]] |
|
+-------+-----------------------------+
|
SELECT JSON_ARRAY('1') AS a FROM DUAL HAVING JSON_ARRAY(a)='[["1"]]'; |
Empty set (0.00 sec)
|
SELECT JSON_ARRAY('1') AS a FROM DUAL HAVING JSON_ARRAY(JSON_COMPACT(a))='[["1"]]'; |
+-------+
|
| a |
|
+-------+
|
| ["1"] |
|
+-------+
|
Notice, JSON_ARRAY(a) in HAVING does not see that "a" is a JSON value. It considers it as text.
The expected result would be to return one row in both the second and the third queries.
VIEW columns
The same problem is repeatable with JSON values coming from VIEWs:
DROP VIEW v1; |
CREATE VIEW v1 AS SELECT JSON_ARRAY(1,2) AS ja12 FROM DUAL; |
SELECT JSON_ARRAY(ja12) FROM v1; |
+------------------+
|
| JSON_ARRAY(ja12) |
|
+------------------+
|
| ["[1, 2]"] |
|
+------------------+
|
The expected result would be:
[[1,2]]
|
Note, Oracle and MySQL-5.7 return this result:
[[1,2]]
|
Derived table columns
The same problem is repeatable with derived table columns:
SELECT JSON_ARRAY(a) FROM (SELECT JSON_ARRAY(1,2) AS a FROM DUAL) AS t1; |
+---------------+
|
| JSON_ARRAY(a) |
|
+---------------+
|
| ["[1, 2]"] |
|
+---------------+
|
Note, Oracle and MySQL-5.7 do not add quotes in a similar query:
SELECT JSON_ARRAY(a) FROM (SELECT JSON_ARRAY(1,2) AS a FROM DUAL); |
JSON_ARRAY(A)
|
[[1,2]]
|
CASE and abbreviations
Double quotes are also repeatable in these queries:
SELECT JSON_ARRAY(CASE WHEN 1=1 THEN JSON_ARRAY(1,2) END) AS a FROM DUAL; |
SELECT JSON_ARRAY(CASE WHEN 1=1 THEN JSON_ARRAY(1,2) ELSE JSON_ARRAY(1,1) END) AS a FROM DUAL; |
SELECT JSON_ARRAY(COALESCE(JSON_ARRAY(1,2),JSON_ARRAY(1,2))) AS a FROM DUAL; |
SELECT JSON_ARRAY(COALESCE(JSON_ARRAY(1,2))) AS a FROM DUAL; |
All above queries return:
+------------+
|
| a |
|
+------------+
|
| ["[1, 2]"] |
|
+------------+
|
MySQL-5.7 returns '[[1,2]]' for all queries.
Oracle returns no double quotes when using the CASE specification:
SELECT JSON_ARRAY(CASE WHEN 1=1 THEN JSON_ARRAY(1,2) END) AS a FROM DUAL; |
SELECT JSON_ARRAY(CASE WHEN 1=1 THEN JSON_ARRAY(1,2) ELSE JSON_ARRAY(1,1) END) AS a FROM DUAL; |
A
|
[[1,2]]
|
However, it does return double quotes with COALESCE:
SELECT JSON_ARRAY(COALESCE(JSON_ARRAY(1,2),JSON_ARRAY(1,2))) AS a FROM DUAL; |
A
|
["[1,2]"]
|
Aggregate functions
Double quotes are also repeatable with aggregate functions:
SELECT JSON_ARRAY(MAX(JSON_ARRAY(1,2))) AS a FROM DUAL; |
+------------+
|
| a |
|
+------------+
|
| ["[1, 2]"] |
|
+------------+
|
Note, Oracle and MySQL-5.7 returns the same result, with double quotes.
JSON_ARRAY(MAX(JSON_ARRAY(1,2)))
|
["[1,2]"]
|
Note, MySQL returns LONGTEXT rather than JSON for MAX(json):
DROP TABLE IF EXISTS t1; |
CREATE TABLE t1 AS SELECT MAX(JSON_ARRAY(1,2)) AS j; |
SHOW CREATE TABLE t1; |
+-------+---------------------------------------------------------------------------------------------------------------------+
|
| Table | Create Table |
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
| t1 | CREATE TABLE `t1` (
|
`j` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin
|
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
|
+-------+---------------------------------------------------------------------------------------------------------------------+
|
PS parameter
The same problem is repeatable with a PS parameter:
EXECUTE IMMEDIATE 'SELECT JSON_ARRAY(?)' USING JSON_ARRAY(1,2); |
+---------------+
|
| JSON_ARRAY(?) |
|
+---------------+
|
| ["[1, 2]"] |
|
+---------------+
|
The expected value is probably [["a"]], like in this query:
SELECT JSON_ARRAY(JSON_ARRAY('a')); |
+-----------------------------+
|
| JSON_ARRAY(JSON_ARRAY(1,2)) |
|
+-----------------------------+
|
| [[1, 2]] |
|
+-----------------------------+
|
Note, MySQL does not support EXECUTE IMMEDIATE or PREPARE..EXECUTE with functions.
Note, Oracle's EXECUTE IMMEDIATE does not seem to distinguish between TEXT and JSON though:
declare
|
a clob;
|
begin
|
execute immediate 'select json_array(:1) from dual' into a using json_array(1,2); |
dbms_output.put_line(a);
|
end; |
["[1,2]"]
|
If Oracle made a difference, the result would be the same with this:
declare
|
a clob;
|
begin
|
execute immediate 'select json_array(json_array(1,2)) from dual' into a; |
dbms_output.put_line(a);
|
end; |
[[1,2]]
|