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

JSON_TABLE: Wrong value with implicit conversion

    XMLWordPrintable

Details

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

    Description

      select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt;
       
      select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by converted;
       
      select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original;
      

      In all queries below, the value 1 is valid for the tinyint column, while the values "foo" and 1000 are not, so they get implicitly converted.

      The first query, without ORDER BY, converts both to NULL and returns them in the order of appearance:

      bb-10.6-mdev17399-hf a96203921343 with a local patch producing the conversion warnings

      +-----------+----------+
      | converted | original |
      +-----------+----------+
      |      NULL | foo      |
      |         1 | 1        |
      |      NULL | 1000     |
      +-----------+----------+
      3 rows in set, 2 warnings (0.001 sec)
      

      However, the same query with ORDER BY (by either column) converts 1000 to NULL, but "foo" to 0:

      MariaDB [test]> select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by original;
      +-----------+----------+
      | converted | original |
      +-----------+----------+
      |         1 | 1        |
      |      NULL | 1000     |
      |         0 | foo      |
      +-----------+----------+
      3 rows in set (0.001 sec)
      

      Moreover, if the converted values are used in ORDER BY, the final order appears to be incorrect:

      MariaDB [test]> select * from json_table('{"a":"foo", "b":1, "c":1000}', '$.*' columns(converted tinyint path '$', original text path '$')) as jt order by converted;
      +-----------+----------+
      | converted | original |
      +-----------+----------+
      |         0 | foo      |
      |      NULL | 1000     |
      |         1 | 1        |
      +-----------+----------+
      3 rows in set, 2 warnings (0.001 sec)
      

      (In fact, 0 is probably still NULL during ORDER BY, hence the order, but it doesn't look right).

      Attachments

        Issue Links

          Activity

            People

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