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

String functions don't respect character set of JSON_VALUE

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2.16, 10.2(EOL), 10.3(EOL)
    • 10.2.17
    • Character Sets, JSON
    • None

    Description

      Let's say that we have the following table:

      CREATE TABLE json_test (
      		 
      json_col TEXT
      		 
      ) DEFAULT CHARSET=latin1;
      		 
       
      		 
      INSERT INTO json_test VALUES ('{"key1":"Sí"}');

      We can get the value of key1 by executing the following:

      SELECT JSON_VALUE(json_col, '$.key1') FROM json_test;

      And this works fine:

      MariaDB [db1]> SELECT JSON_VALUE(json_col, '$.key1') FROM json_test;
      		 
      +--------------------------------+
      		 
      | JSON_VALUE(json_col, '$.key1') |
      		 
      +--------------------------------+
      		 
      | Sí                             |
      		 
      +--------------------------------+
      		 
      1 row in set (0.00 sec)

      But if we combine this with string functions, then the character set gets messed up. For example, run the following queries instead:

      SELECT REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') FROM json_test;
      		 
      SELECT CONCAT(json_value(json_col, '$.key1'), 'a') FROM json_test;

      And we get something unexpected:

      MariaDB [db1]> SELECT REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') FROM json_test;
      		 
      +-----------------------------------------------------+
      		 
      | REPLACE(JSON_VALUE(json_col, '$.key1'), 'null', '') |
      		 
      +-----------------------------------------------------+
      		 
      | Sí                                                 |
      		 
      +-----------------------------------------------------+
      		 
      1 row in set (0.00 sec)
      		 
       
      		 
      MariaDB [db1]> SELECT CONCAT(json_value(json_col, '$.key1'), 'a') FROM json_test;
      		 
      +---------------------------------------------+
      		 
      | CONCAT(json_value(json_col, '$.key1'), 'a') |
      		 
      +---------------------------------------------+
      		 
      | Sía                                        |
      		 
      +---------------------------------------------+
      		 
      1 row in set (0.00 sec)

      We can workaround this problem by explicitly converting the JSON_VALUE return value to a given character set:

      MariaDB [db1]> SELECT REPLACE(CONVERT(JSON_VALUE(json_col, '$.key1') USING utf8), 'null', '') FROM json_test;
      		 
      +-------------------------------------------------------------------------+
      		 
      | REPLACE(CONVERT(JSON_VALUE(json_col, '$.key1') USING utf8), 'null', '') |
      		 
      +-------------------------------------------------------------------------+
      		 
      | Sí                                                                      |
      		 
      +-------------------------------------------------------------------------+
      		 
      1 row in set (0.00 sec)

      Attachments

        Activity

          Ascending order - Click to sort in descending order
          GeoffMontee Geoff Montee (Inactive) created issue -
          elenst Elena Stepanova made changes -
          Field Original Value New Value
          Status Open [ 1 ] Confirmed [ 10101 ]
          elenst Elena Stepanova added a comment -

          JSON_EXTRACT, for example, works okay.

          elenst Elena Stepanova added a comment - JSON_EXTRACT , for example, works okay.
          elenst Elena Stepanova made changes -
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Affects Version/s 10.2 [ 14601 ]
          Affects Version/s 10.3 [ 22126 ]
          Assignee Alexander Barkov [ bar ]
          bar Alexander Barkov added a comment - - edited

          This script demonstrates the problem:

          CREATE OR REPLACE TABLE t1 (j TEXT) DEFAULT CHARSET=latin1;
          		 
          INSERT INTO t1 VALUES ('{"key1":"Sí"}');
          		 
          SELECT CONCAT(json_value(j, '$.key1'), 'a') FROM t1;


          +--------------------------------------+
          		 
          | CONCAT(json_value(j, '$.key1'), 'a') |
          		 
          +--------------------------------------+
          		 
          | Sía                                 |
          		 
          +--------------------------------------+

          Note, if I change the character set of the column to utf8mb4, it returns the expected value:

          CREATE OR REPLACE TABLE t1 (j TEXT CHARACTER SET utf8mb4) DEFAULT CHARSET=latin1;
          		 
          INSERT INTO t1 VALUES ('{"key1":"Sí"}');
          		 
          SELECT CONCAT(json_value(j, '$.key1'), 'a') FROM t1;


          +--------------------------------------+
          		 
          | CONCAT(json_value(j, '$.key1'), 'a') |
          		 
          +--------------------------------------+
          		 
          | Sía                                  |
          		 
          +--------------------------------------+

          bar Alexander Barkov added a comment - - edited This script demonstrates the problem: CREATE OR REPLACE TABLE t1 (j TEXT) DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES ( '{"key1":"Sí"}' ); SELECT CONCAT(json_value(j, '$.key1' ), 'a' ) FROM t1; +--------------------------------------+ | CONCAT(json_value(j, '$.key1'), 'a') | +--------------------------------------+ | Sía | +--------------------------------------+ Note, if I change the character set of the column to utf8mb4, it returns the expected value: CREATE OR REPLACE TABLE t1 (j TEXT CHARACTER SET utf8mb4) DEFAULT CHARSET=latin1; INSERT INTO t1 VALUES ( '{"key1":"Sí"}' ); SELECT CONCAT(json_value(j, '$.key1' ), 'a' ) FROM t1; +--------------------------------------+ | CONCAT(json_value(j, '$.key1'), 'a') | +--------------------------------------+ | Sía | +--------------------------------------+
          bar Alexander Barkov made changes -
          Assignee Alexander Barkov [ bar ] Alexey Botchkov [ holyfoot ]
          holyfoot Alexey Botchkov made changes -
          Status Confirmed [ 10101 ] In Progress [ 3 ]
          holyfoot Alexey Botchkov added a comment -

          http://lists.askmonty.org/pipermail/commits/2018-August/012774.html

          holyfoot Alexey Botchkov added a comment - http://lists.askmonty.org/pipermail/commits/2018-August/012774.html
          holyfoot Alexey Botchkov made changes -
          issue.field.resolutiondate 2018-08-05 15:08:34.0 2018-08-05 15:08:34.499
          holyfoot Alexey Botchkov made changes -
          Fix Version/s 10.2.17 [ 23111 ]
          Fix Version/s 10.2 [ 14601 ]
          Fix Version/s 10.3 [ 22126 ]
          Resolution Fixed [ 1 ]
          Status In Progress [ 3 ] Closed [ 6 ]
          serg Sergei Golubchik made changes -
          Workflow MariaDB v3 [ 88674 ] MariaDB v4 [ 154739 ]
          mariadb-jira-automation Jira Automation (IT) made changes -
          Zendesk Related Tickets 183139

          People

            holyfoot Alexey Botchkov
            GeoffMontee Geoff Montee (Inactive)
            Votes:
            0 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.