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

JSON_TABLE: Unexpectedly padded values in a PATH column

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • N/A
    • 10.6.0
    • JSON
    • None

    Description

      bb-10.6-mdev17399-psergey2 8b533cc1d5

      		 
      MariaDB [test]> select a, b from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$', b for ordinality)) t;
      		 
      +--------------+------+
      		 
      | a            | b    |
      		 
      +--------------+------+
      		 
      | foo         |    1 |
      		 
      | bar         |    2 |
      		 
      +--------------+------+
      		 
      2 rows in set (0.001 sec)

      The result already looks damaged. Looking into it further,

      MariaDB [test]> select hex(a), b from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$', b for ordinality)) t;
      		 
      +--------------------------+------+
      		 
      | hex(a)                   | b    |
      		 
      +--------------------------+------+
      		 
      | 666F6F202020202001000000 |    1 |
      		 
      | 626172202020202002000000 |    2 |
      		 
      +--------------------------+------+
      		 
      2 rows in set (0.001 sec)

      I consider it totally unexpected.

      SQL mode doesn't enforce any padding (and even if it did, it should have been different):

      MariaDB [test]> select @@sql_mode;
      		 
      +-------------------------------------------------------------------------------------------+
      		 
      | @@sql_mode                                                                                |
      		 
      +-------------------------------------------------------------------------------------------+
      		 
      | STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
      		 
      +-------------------------------------------------------------------------------------------+
      		 
      1 row in set (0.000 sec)

      MySQL doesn't do anything like this:

      MySQL 8.0.23

      		 
      MySQL [test]> select a, b from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$', b for ordinality)) t;
      		 
      +------+------+
      		 
      | a    | b    |
      		 
      +------+------+
      		 
      | foo  |    1 |
      		 
      | bar  |    2 |
      		 
      +------+------+
      		 
      2 rows in set (0.001 sec)
      		 
       
      		 
      MySQL [test]> select hex(a), b from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$', b for ordinality)) t;
      		 
      +--------+------+
      		 
      | hex(a) | b    |
      		 
      +--------+------+
      		 
      | 666F6F |    1 |
      		 
      | 626172 |    2 |
      		 
      +--------+------+
      		 
      2 rows in set (0.001 sec)

      Also, it doesn't happen on MariaDB either, if there is no 2nd column:

      bb-10.6-mdev17399-psergey2 8b533cc1d5

      		 
      MariaDB [test]> select a from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$')) t;
      		 
      +------+
      		 
      | a    |
      		 
      +------+
      		 
      | foo  |
      		 
      | bar  |
      		 
      +------+
      		 
      2 rows in set (0.001 sec)
      		 
       
      		 
      MariaDB [test]> select hex(a) from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$')) t;
      		 
      +--------+
      		 
      | hex(a) |
      		 
      +--------+
      		 
      | 666F6F |
      		 
      | 626172 |
      		 
      +--------+
      		 
      2 rows in set (0.001 sec)

      Attachments

        Issue Links

          relates to

          Task - A task that needs to be done. MDEV-17399 Add support for JSON_TABLE

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            elenst Elena Stepanova added a comment -

            This is an MTR version:

            SET NAMES utf8;
            		 
            select hex(a), b from json_table('["foo","bar"]','$[*]' columns (a char(8) path '$', b for ordinality)) t;

            elenst Elena Stepanova added a comment - This is an MTR version: SET NAMES utf8; select hex(a), b from json_table( '["foo","bar"]' , '$[*]' columns (a char (8) path '$' , b for ordinality)) t;

            People

              holyfoot Alexey Botchkov
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 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.