[MDEV-12604] Comparison of JSON_EXTRACT result differs with Mysql Created: 2017-04-27  Updated: 2017-08-11  Resolved: 2017-08-08

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.2.5
Fix Version/s: 10.2.8

Type: Bug Priority: Major
Reporter: Armin Preiml Assignee: Alexey Botchkov
Resolution: Fixed Votes: 1
Labels: JSON
Environment:

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



 Comments   
Comment by Elena Stepanova [ 2017-05-01 ]

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':

MySQL [test]> select @@version;
 
+--------------+
 
| @@version    |
 
+--------------+
 
| 5.7.17-debug |
 
+--------------+
 
1 row in set (0.00 sec)
 
 
 
MySQL [test]> select JSON_EXTRACT('{"name":"value"}', '$.name');
 
+--------------------------------------------+
 
| JSON_EXTRACT('{"name":"value"}', '$.name') |
 
+--------------------------------------------+
 
| "value"                                    |
 
+--------------------------------------------+
 
1 row in set (0.00 sec)

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.

Comment by Sergei Golubchik [ 2017-05-02 ]

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.

Comment by Armin Preiml [ 2017-05-10 ]

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 'ö'

Comment by Alexey Botchkov [ 2017-08-08 ]

http://lists.askmonty.org/pipermail/commits/2017-August/011358.html

Generated at Thu Feb 08 07:59:01 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.