Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.2(EOL), 10.3(EOL)
-
Tried on Ubuntu
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;
|
Attachments
Issue Links
- includes
-
-
- Closed
-
Activity
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
|
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.