Illegal mix of collations for operation 'UNION' bug
Posted: Thu 01 Aug 2019 06:37
Hi,
we are using dotConnector for mysql 8.13.1402 and found the bug over there.
If to change the DB collation to the utf8-mb4 it fails on requests with unions. For example, to reproduce the request with union you need to create the LINQ with some classes constructor in select and return there some lists and array.
Example of such request is here:
It looks like the problem is not in union itself, but in CAST statement. It looks like the the CAST statememnt in combination with UNION and charset utf8-mb5 cause the exception : Illegal mix of collations for operation 'UNION'.
How can we solve this issue? How to avoid the cast or the unions in the request? Oer maybe have you another solution?
How fasr can it be fixes? We really need it for our next release and it will be very soon.
we are using dotConnector for mysql 8.13.1402 and found the bug over there.
If to change the DB collation to the utf8-mb4 it fails on requests with unions. For example, to reproduce the request with union you need to create the LINQ with some classes constructor in select and return there some lists and array.
Example of such request is here:
Code: Select all
SELECT
UnionAll1.customer_id AS C1,
UnionAll1.customer_id1 AS C2,
UnionAll1.customer_name AS C3,
UnionAll1.C1 AS C4,
UnionAll1.customer_id2 AS C5,
UnionAll1.customer_terminology_key AS C6,
UnionAll1.customer_terminology_value AS C7,
UnionAll1.C2 AS C8
FROM (SELECT
CASE WHEN Extent2.customer_id IS NULL THEN CAST(NULL AS SIGNED) ELSE 1 END AS C1,
Extent1.customer_id,
Extent1.customer_id AS customer_id1,
Extent1.customer_name,
Extent2.customer_id AS customer_id2,
Extent2.customer_terminology_key,
Extent2.customer_terminology_value,
CAST(NULL AS SIGNED) AS C2
FROM customer AS Extent1
LEFT OUTER JOIN customer_terminology AS Extent2 ON Extent1.customer_id = Extent2.customer_id
UNION ALL
SELECT
2 AS C1,
Extent3.customer_id,
Extent3.customer_id AS customer_id1,
Extent3.customer_name,
CAST(NULL AS SIGNED) AS C2,
CAST(NULL AS CHAR) AS C3,
CAST(NULL AS CHAR) AS C4,
Extent4.user_id
FROM customer AS Extent3
INNER JOIN user AS Extent4 ON (Extent3.customer_id = Extent4.customer_id) AND (Extent4.role_id IN (5,6))) AS UnionAll1
ORDER BY UnionAll1.customer_id1 ASC, UnionAll1.C1 ASC
How can we solve this issue? How to avoid the cast or the unions in the request? Oer maybe have you another solution?
How fasr can it be fixes? We really need it for our next release and it will be very soon.