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

JSON_TABLE: Unexpectedly padded values in a PATH column

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: N/A
    • Fix Version/s: 10.6.0
    • Component/s: JSON
    • Labels:
      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

            Activity

              People

              Assignee:
              holyfoot Alexey Botchkov
              Reporter:
              elenst Elena Stepanova
              Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: