Details
-
Bug
-
Status: Stalled (View Workflow)
-
Major
-
Resolution: Unresolved
-
11.7.2, 10.6, 10.11, 11.4, 11.8
-
Running MariaDB in a docker container using the latest tag
-
Unexpected results
Description
When using `json_table` to parse a json stored in a longtext column, the collation of the `json_table` columns differs from the default collation configured.
Starting with a clean database with charset `utf8mb4` and collation `utf8mb4_uca1400_ai_ci`:
CREATE DATABASE collation_testing CHARACTER SET='utf8mb4' COLLATE='utf8mb4_uca1400_ai_ci'; |
Create a table with a longtext column:
CREATE TABLE table_with_longtext_column (column_with_json LONGTEXT); |
Insert a very simple json just for testing:
INSERT INTO table_with_longtext_column VALUES ('[{"property": "value"}]'); |
Validate that the collation is correct at the moment:
select collation(column_with_json) from table_with_longtext_column; |
As expected, we get `utf8mb4_uca1400_ai_ci`:
For further collation validation, check that using json_value, returns the expected collation:
select collation(json_value(column_with_json, "$.value")) from table_with_longtext_column; |
But now, using json_table generates a table with an unexpected collation:
select collation(virtual_table_from_json.property) from json_table((select column_with_json |
from table_with_longtext_column),'$[*]' columns (property varchar(100) path '$.property')) as virtual_table_from_json; |
This is problematic when trying to join this `json_table` with a second table. If we create another table as it follows:
CREATE TABLE values_table (value varchar(100)); |
And insert some data to join with the previous `json_data`:
INSERT INTO values_table VALUES("value"); |
When trying to join both tables:
select * from json_table((select column_with_json from table_with_longtext_column),'$[*]' columns (property varchar(100) path '$.property')) as virtual_table_from_json inner join values_table on virtual_table_from_json.property = values_table.value; |
Get the following error:
`ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_uca1400_ai_ci,IMPLICIT) for operation '='`
A workaround was found by explicitly declaring the collation of the json_table columns:
select * from json_table((select column_with_json from table_with_longtext_column),'$[*]' columns (property varchar(100) collate utf8mb4_uca1400_ai_ci path '$.property')) as virtual_table_from_json inner join values_table on virtual_table_from_json.property = values_table.value; |
