[MDEV-15931] JSON_TYPE doesn't work correctly Created: 2018-04-19  Updated: 2024-01-21

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

Type: Bug Priority: Major
Reporter: Nicola Marangoni Assignee: Rucha Deodhar
Resolution: Unresolved Votes: 0
Labels: json
Environment:

Tried on Ubuntu

Issue Links:
PartOf
includes MDEV-19487 JSON_TYPE doesnt detect the type of S... Confirmed

 Description   

The JSON_TYPE works well with arguments generated by the JSON_QUERY funtions and correctly detect the OBJECT and ARRAY types.
When the argument originates from the JSON_VALUE function, it detects onyl INTEGERs correctly.
Decimals are detected as DOUBLE.
Strings are detected as NULL (no type) and strings beginning with an integer (like "23hjk") number are detected as INTEGER; if they begin with a decimal (like "3.234dfg") they are detected as DOUBLE.

SELECT
 
	JSON_VALID(t.json),
 
	JSON_TYPE(JSON_QUERY(t.json,'$.b')),
 
	JSON_TYPE(JSON_QUERY(t.json,'$.b.gh')),
 
	JSON_TYPE(JSON_VALUE(t.json,'$.b.ab')),
 
	JSON_TYPE(JSON_VALUE(t.json,'$.b.cd')),
 
	JSON_TYPE(JSON_VALUE(t.json,'$.b.ef')),
 
	JSON_TYPE(JSON_VALUE(t.json,'$.b.gh[0].ghi')),
 
	JSON_TYPE(JSON_VALUE(t.json,'$.b.gh[1].jkl'))
 
FROM
 
	(SELECT '{"a": "abc", "b": {"ab": "xyz5", "cd": "1xvz", "ef": "321", "gh": [{"ghi": "1.234"}, {"jkl": "334.2qrt"}]}}' AS json) AS t;

Actually these statements would be sufficient to reproduce:

select JSON_TYPE('321');
 
show warnings;
 
 
 
select JSON_TYPE('abc');
 
show warnings;
 
 
 
select JSON_TYPE('123abc');
 
show warnings;
 
 
 
select JSON_TYPE('12.3abc');
 
show warnings;



 Comments   
Comment by Sergei Golubchik [ 2020-07-10 ]

JSON_TYPE('abc'); is correct, it's not a JSON string, a valid JSON string is JSON_TYPE('"abc"');.

But JSON_TYPE('123a') is incorrectly detected as a valid JSON integer, this is a bug.

Comment by Janez Resnik [ 2022-08-16 ]

Scientific notation is also wrongly detected as integer, should be double:

Demo: https://onecompiler.com/mariadb/3yd6efagy

select version();
 
 
 
select json_valid('1'),json_valid('1.1'),json_valid('1e1'),json_valid('1e-1'),json_valid('1,1'),json_valid('1abc'),json_valid('abc1')
 
union all
 
select json_type('1'),json_type('1.1'),json_type('1e1'),json_type('1e-1'),json_type('1,1'),json_type('1abc'),json_type('abc1')
 
union all
 
select json_value('1','$'),json_value('1.1','$'),json_value('1e1','$'),json_value('1e-1','$'),json_value('1,1','$'),json_value('1abc','$'),json_value('abc1','$')
 
union all
 
select json_extract('1','$'),json_extract('1.1','$'),json_extract('1e1','$'),json_extract('1e-1','$'),json_extract('1,1','$'),json_extract('1abc','$'),json_extract('abc1','$')
 
union all
 
select 0+json_extract('1','$'),0+json_extract('1.1','$'),0+json_extract('1e1','$'),0+json_extract('1e-1','$'),0+json_extract('1,1','$'),0+json_extract('1abc','$'),0+json_extract('abc1','$')

Output:

version()
 
10.7.4-MariaDB-1:10.7.4+maria~focal
 
 
 
json_valid('1')	json_valid('1.1')	json_valid('1e1')	json_valid('1e-1')	json_valid('1,1')	json_valid('1abc')	json_valid('abc1')
 
1	1	1	1	0	0	0
 
INTEGER	DOUBLE	INTEGER	INTEGER	INTEGER	INTEGER	NULL
 
1	1.1	1e1	1e-1	1	1	NULL
 
1	1.1	1e1	1e-1	NULL	NULL	NULL
 
1	1.1	10	0.1	1	1	NULL

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