Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2.5
-
Debian
Description
There seems to be different behaviour when comparing the result of JSON_EXTRACT in MySql 5.7 and Mariadb 10.2.5.
In MySql the following query will result in true, but in mariadb this results in 0.
select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value'; |
To fix this I tried to just quote the value to compare and it works in mariadb (result: 1) but not in MySql (result: 0).
select JSON_EXTRACT('{"name":"value"}', '$.name') = JSON_QUOTE('value'); |
It's also an issue with boolean values:
SET @str = '{\"asdf\":true}'; |
SET @path = "$.\"asdf\""; |
select @str, @path, JSON_EXTRACT(@str, @path) = 'true'; -- mariadb |
select @str, @path, JSON_EXTRACT(@str, @path) = true; -- mysql |
Attachments
Activity
I think it's simple. If we implement a standard functionality, it should behave as in the standard. If we implement MySQL compatibility feature, it should behave as in MySQL, whenever possible. JSON_EXTRACT is MySQL compatibility function, as far as I understand.
JSON_EXTRACT also behaves differently on some strings even:
SET @str = '{\"input1\":\"`\"}'; |
select JSON_EXTRACT(@str, '$.\"input1\"') = '`'; -- true |
select JSON_EXTRACT(@str, '$.\"input1\"') = JSON_QUOTE('`'); -- false |
and I can't extract a unicode character:
select JSON_EXTRACT('{\"input1\":\"\\u00f6\"}', '$.\"input1\"'); -- results in null, mysql result is 'ö' |
I'm not quite sure which logic is correct here.
On one hand, if we just run JSON_EXTRACT (on either of the servers), the result is obviously different from 'value':
| @@version || 5.7.17-debug |On the other hand, it's reasonable to expect that quote marks are not taken into account.
It would be great if standard specified which behavior is correct, but I don't know whether it does. Leaving to holyfoot to decide what to do about it.