[MDEV-27696] Json table columns accept redundant COLLATE syntax Created: 2022-01-31  Updated: 2022-01-31  Resolved: 2022-01-31

Status: Closed
Project: MariaDB Server
Component/s: JSON
Affects Version/s: 10.6, 10.7, 10.8
Fix Version/s: 10.6.7, 10.7.3, 10.8.2

Type: Bug Priority: Major
Reporter: Alexander Barkov Assignee: Alexander Barkov
Resolution: Fixed Votes: 0
Labels: None

Issue Links:
Relates
relates to MDEV-27009 Add UCA-14.0.0 collations Closed

 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  |
 
+------+



 Comments   
Comment by Alexander Barkov [ 2022-01-31 ]

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.
Generated at Thu Feb 08 09:54:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.