Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-12604

Comparison of JSON_EXTRACT result differs with Mysql

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.5
    • 10.2.8
    • JSON
    • 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

          Ascending order - Click to sort in descending order
          apreiml Armin Preiml created issue -
          serg Sergei Golubchik made changes -
          Field Original Value New Value
          Component/s JSON [ 13908 ]
          elenst Elena Stepanova added a comment -

          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.

          elenst Elena Stepanova added a comment - 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.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.2 [ 14601 ]
          Assignee Alexey Botchkov [ holyfoot ]
          serg Sergei Golubchik added a comment -

          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.

          serg Sergei Golubchik added a comment - 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.
          apreiml Armin Preiml made changes -
          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.

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
          {code}

          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).

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = JSON_QUOTE('value');
          {code}           		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.

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
          {code}

          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).

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = JSON_QUOTE('value');
          {code}

          It's also an issue with boolean values:

          {code:sql}
          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
          {code}
          apreiml Armin Preiml added a comment - - edited

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

          apreiml Armin Preiml added a comment - - edited 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 'ö'
          apreiml Armin Preiml made changes -
          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.

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
          {code}

          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).

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = JSON_QUOTE('value');
          {code}

          It's also an issue with boolean values:

          {code:sql}
          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
          {code}           		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.

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = 'value';
          {code}

          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).

          {code:sql}
          select JSON_EXTRACT('{"name":"value"}', '$.name') = JSON_QUOTE('value');
          {code}

          It's also an issue with boolean values:

          {code:sql}
          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
          {code}
          holyfoot Alexey Botchkov made changes -
          Status Open [ 1 ] In Progress [ 3 ]
          holyfoot Alexey Botchkov added a comment -

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

          holyfoot Alexey Botchkov added a comment - http://lists.askmonty.org/pipermail/commits/2017-August/011358.html
          holyfoot Alexey Botchkov made changes -
          issue.field.resolutiondate 2017-08-08 07:03:18.0 2017-08-08 07:03:18.797
          holyfoot Alexey Botchkov made changes -
          Fix Version/s 10.2.7 [ 22543 ]
          Fix Version/s 10.2 [ 14601 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          holyfoot Alexey Botchkov made changes -
          Fix Version/s 10.2.8 [ 22544 ]
          Fix Version/s 10.2.7 [ 22543 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 80491 ] MariaDB v4 [ 152026 ]

          People

            holyfoot Alexey Botchkov
            apreiml Armin Preiml
            Votes:
            1 Vote for this issue
            Watchers:
            4 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.