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

Incorrect data type handling in JSON_TABLE

    XMLWordPrintable

Details

    • Bug
    • Status: Needs Feedback (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.11.16
    • None
    • Data types, JSON
    • None
    • AWS RDS

    Description

      Assuming this JSON

      {"upd_fields": [{"path": "$.path1", "rbac": 10, "is_strict": true}, {"path": "$.path2", "rbac": 8}]}
      

      A JSON_TABLE like this

      JSON_TABLE (
                      t.json,
                      '$.upd_fields[*]' COLUMNS (
                          field_path VARCHAR(30) PATH '$.path',
                          field_rbac BIT (16) PATH '$.rbac' NULL ON EMPTY,
                          field_strict BOOLEAN PATH '$.is_strict' DEFAULT FALSE ON EMPTY
                      )
      ) jt

      will output incortect values in field_rbac, while the values become correct if using TINYINT UNSIGNED.
      The table is in a from clause after another table t containing json column.

      We also had to switch the last field to boolean as an enum rappresenting the mode generated this error:
      (conn:48282, no: 1064, SQLState: 42000) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'ENUM('value1', 'value2', 'value3') PATH '$.enum' DEFAULT NULL ON EMPT

      Attachments

        1. vscode.png
          37 kB
          Francesco Camuffo

        Issue Links

          Activity

            People

              Unassigned Unassigned
              fcamuffo Francesco Camuffo
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

              Dates

                Created:
                Updated:

                Git Integration

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