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

Json table columns accept redundant COLLATE syntax

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.6, 10.7(EOL), 10.8(EOL)
    • 10.6.7, 10.7.3, 10.8.2
    • JSON
    • None

    Description

      The following queries return result sets, although they are expected to return syntax errors:

      -- BLOB cannot have a COLLATE clause
      SELECT * FROM json_table('[{"name":"str"}]', '$[*]'
        COLUMNS (
          name BLOB COLLATE `binary` PATH '$.name' 
        )
      ) AS jt;
      

      +------+
      | name |
      +------+
      | str  |
      +------+
      

      -- Double COLLATE clause
      SELECT * FROM json_table('[{"name":"str"}]', '$[*]' 
        COLUMNS (
          name VARCHAR(10) COLLATE latin1_bin COLLATE latin1_swedish_ci PATH '$.name' 
        )
      ) AS jt;
      

      +------+
      | name |
      +------+
      | str  |
      +------+
      

      -- Redundant collation: BINARY (abbreviation meaning binary sorting) + standard COLLATE
      SELECT * FROM json_table('[{"name":"str"}]', '$[*]'
        COLUMNS (
         name VARCHAR(10) BINARY COLLATE utf8_czech_ci path '$.name'
        )
      ) AS jt;
      

      +------+
      | name |
      +------+
      | str  |
      +------+
      

      Attachments

        Issue Links

          Activity

            bar Alexander Barkov added a comment - - edited

            Note, normal fields also support some of the above syntax. But this is a mistake and should also be fixed eventually. For now let's just don't propagate the wrong syntax to JSON TABLE.

            According to the standard:

            • Binary string data types, e.g. BLOB, cannot have COLLATE clause.
            • Character string data types can have only one COLLATE clause.
            bar Alexander Barkov added a comment - - edited Note, normal fields also support some of the above syntax. But this is a mistake and should also be fixed eventually. For now let's just don't propagate the wrong syntax to JSON TABLE. According to the standard: Binary string data types, e.g. BLOB, cannot have COLLATE clause. Character string data types can have only one COLLATE clause.

            People

              bar Alexander Barkov
              bar Alexander Barkov
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.