Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
N/A
-
None
Description
The query works:
bb-10.6-mdev17399-hf 8611fb30bc6 |
MariaDB [test]> SELECT * FROM JSON_TABLE(CONVERT('[]' USING dec8), '$' COLUMNS(b VARCHAR(8) CHARSET utf8 PATH '$')) AS jt2 WHERE (CONVERT('[]' USING cp1256) = b); |
Empty set (0.001 sec) |
But the same query via a view fails with ER_CANT_AGGREGATE_2COLLATIONS:
MariaDB [test]> CREATE OR REPLACE VIEW v AS |
SELECT * FROM JSON_TABLE(CONVERT('[]' USING dec8), '$' COLUMNS(b VARCHAR(8) CHARSET utf8 PATH '$')) AS jt2 WHERE (CONVERT('[]' USING cp1256) = b); |
Query OK, 0 rows affected (0.015 sec) |
|
MariaDB [test]>
|
MariaDB [test]> SELECT * FROM v; |
ERROR 1267 (HY000): Illegal mix of collations (cp1256_general_ci,IMPLICIT) and (dec8_swedish_ci,IMPLICIT) for operation '=' |
On MySQL both queries work (with warnings urging to use utf8mb4 instead of utf8, but that's unrelated):
MySQL 8.0.23 |
MySQL [test]> SELECT * FROM JSON_TABLE(CONVERT('[]' USING dec8), '$' COLUMNS(b VARCHAR(8) CHARSET utf8 PATH '$')) AS jt2 WHERE (CONVERT('[]' USING cp1256) = b); |
Empty set, 1 warning (0.001 sec) |
|
MySQL [test]>
|
MySQL [test]> CREATE OR REPLACE VIEW v AS |
-> SELECT * FROM JSON_TABLE(CONVERT('[]' USING dec8), '$' COLUMNS(b VARCHAR(8) CHARSET utf8 PATH '$')) AS jt2 WHERE (CONVERT('[]' USING cp1256) = b); |
Query OK, 0 rows affected, 1 warning (0.033 sec) |
|
MySQL [test]>
|
MySQL [test]> SELECT * FROM v; |
Empty set, 2 warnings (0.002 sec) |
|
MySQL [test]>
|
MySQL [test]> show warnings;
|
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Level | Code | Message | |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. | |
| Warning | 3719 | 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. | |
+---------+------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ |
2 rows in set (0.000 sec) |
Attachments
Issue Links
- relates to
-
MDEV-25353 JSON_TABLE: Illegal mix of collations upon executing PS once, or SP/function twice
- Closed
-
MDEV-17399 Add support for JSON_TABLE
- Closed