Union All Issue

Union All Issue

Postby efaruk » Tue 18 Nov 2014 13:03

Hi,

This is the code we are using:

Code: Select all
var query =
               context.AuditLogs.Where(al => ids.Contains(al.PkAuditLogId))
                  .Include(al => al.AuditLogDetails)
                  .Include(al => al.AuditLogRelations);


Following query generated by dotConnect for MySQL (with EF6 (6.1.1));
Code: Select all
{SELECT
UnionAll1.C2 AS C1,
UnionAll1.PkAuditLogId AS C2,
UnionAll1.ApplicationId AS C3,
UnionAll1.RelatedUserId AS C4,
UnionAll1.CreatedOn AS C5,
UnionAll1.AuditType AS C6,
UnionAll1.OwnerTableName AS C7,
UnionAll1.C1 AS C8,
UnionAll1.PkAuditLogDetailId AS C9,
UnionAll1.FkAuditLogId AS C10,
UnionAll1.FieldName AS C11,
UnionAll1.OldValue AS C12,
UnionAll1.NewValue AS C13,
UnionAll1.C3 AS C14,
UnionAll1.C4 AS C15,
UnionAll1.C5 AS C16,
UnionAll1.C6 AS C17
FROM  (SELECT
   CASE WHEN Extent2.PkAuditLogDetailId IS NULL THEN CAST(NULL AS SIGNED) ELSE 1 END AS C1,
   1 AS C2,
   Extent1.PkAuditLogId,
   Extent1.ApplicationId,
   Extent1.RelatedUserId,
   Extent1.CreatedOn,
   Extent1.AuditType,
   Extent1.OwnerTableName,
   Extent2.PkAuditLogDetailId,
   Extent2.FkAuditLogId,
   Extent2.FieldName,
   Extent2.OldValue,
   Extent2.NewValue,
   CAST(NULL AS SIGNED) AS C3,
   CAST(NULL AS SIGNED) AS C4,
   CAST(NULL AS CHAR) AS C5,
   CAST(NULL AS CHAR) AS C6
   FROM  AuditLog AS Extent1
   LEFT OUTER JOIN AuditLogDetail AS Extent2 ON Extent1.PkAuditLogId = Extent2.FkAuditLogId
   WHERE Extent1.PkAuditLogId IN (1,3,4,5)
UNION ALL
   SELECT
   2 AS C1,
   2 AS C2,
   Extent3.PkAuditLogId,
   Extent3.ApplicationId,
   Extent3.RelatedUserId,
   Extent3.CreatedOn,
   Extent3.AuditType,
   Extent3.OwnerTableName,
   CAST(NULL AS SIGNED) AS C3,
   CAST(NULL AS SIGNED) AS C4,
   CAST(NULL AS CHAR) AS C5,
   CAST(NULL AS CHAR) AS C6,
   CAST(NULL AS CHAR) AS C7,
   Extent4.PkAuditRelationLogId,
   Extent4.FkAuditLogId,
   Extent4.RelatedTableName,
   Extent4.RelatedRowId
   FROM  AuditLog AS Extent3
   INNER JOIN AuditLogRelation AS Extent4 ON Extent3.PkAuditLogId = Extent4.FkAuditLogId
   WHERE Extent3.PkAuditLogId IN (1,3,4,5)) AS UnionAll1
ORDER BY UnionAll1.PkAuditLogId ASC, UnionAll1.C1 ASC}


And it cause "Illegal mix of collations for operation 'UNION' exception

Bellow link is google drive shared projects zip files;

https://drive.google.com/file/d/0B6eYILvmPWkXOFhwbFU1TDhzTTQ/view?usp=sharing

Please some one help, this is an urgent case. I wrote two times to devart support about this issue. But there is no answer...

Regards...
E. Faruk Pehlivanlı
efaruk
 
Posts: 2
Joined: Tue 18 Nov 2014 12:52

Re: Union All Issue

Postby MariiaI » Wed 19 Nov 2014 13:35

We couldn't reproduce this issue with the latest build of dotConnect for MySQL 8.3.283 and your test project. Please try upgrading your dotConnect for MySQL and try your scenario again. The latest build can be downloaded from http://www.devart.com/dotconnect/mysql/download.html (trial version) or from Registered Users' Area (for users with active subscription only).

Looking forward to your reply.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Union All Issue

Postby efaruk » Fri 21 Nov 2014 13:10

I have tried with 8.3.293 (which is latest for now). Nothing change, we have same exception, still...

Exception
Code: Select all
    An error occurred while executing the command definition. See the inner exception for details.
    Inner Exception
       Illegal mix of collations for operation 'UNION'


Generated SQL
Code: Select all
{SELECT
UnionAll1.C2 AS C1,
UnionAll1.PkAuditLogId AS C2,
UnionAll1.ApplicationId AS C3,
UnionAll1.RelatedUserId AS C4,
UnionAll1.CreatedOn AS C5,
UnionAll1.AuditType AS C6,
UnionAll1.OwnerTableName AS C7,
UnionAll1.C1 AS C8,
UnionAll1.PkAuditLogDetailId AS C9,
UnionAll1.FkAuditLogId AS C10,
UnionAll1.FieldName AS C11,
UnionAll1.OldValue AS C12,
UnionAll1.NewValue AS C13,
UnionAll1.C3 AS C14,
UnionAll1.C4 AS C15,
UnionAll1.C5 AS C16,
UnionAll1.C6 AS C17
FROM  (SELECT
   CASE WHEN Extent2.PkAuditLogDetailId IS NULL THEN CAST(NULL AS SIGNED) ELSE 1 END AS C1,
   1 AS C2,
   Extent1.PkAuditLogId,
   Extent1.ApplicationId,
   Extent1.RelatedUserId,
   Extent1.CreatedOn,
   Extent1.AuditType,
   Extent1.OwnerTableName,
   Extent2.PkAuditLogDetailId,
   Extent2.FkAuditLogId,
   Extent2.FieldName,
   Extent2.OldValue,
   Extent2.NewValue,
   CAST(NULL AS SIGNED) AS C3,
   CAST(NULL AS SIGNED) AS C4,
   CAST(NULL AS CHAR) AS C5,
   CAST(NULL AS CHAR) AS C6
   FROM  AuditLog AS Extent1
   LEFT OUTER JOIN AuditLogDetail AS Extent2 ON Extent1.PkAuditLogId = Extent2.FkAuditLogId
   WHERE Extent1.PkAuditLogId IN (1,3,4,5)
UNION ALL
   SELECT
   2 AS C1,
   2 AS C2,
   Extent3.PkAuditLogId,
   Extent3.ApplicationId,
   Extent3.RelatedUserId,
   Extent3.CreatedOn,
   Extent3.AuditType,
   Extent3.OwnerTableName,
   CAST(NULL AS SIGNED) AS C3,
   CAST(NULL AS SIGNED) AS C4,
   CAST(NULL AS CHAR) AS C5,
   CAST(NULL AS CHAR) AS C6,
   CAST(NULL AS CHAR) AS C7,
   Extent4.PkAuditRelationLogId,
   Extent4.FkAuditLogId,
   Extent4.RelatedTableName,
   Extent4.RelatedRowId
   FROM  AuditLog AS Extent3
   INNER JOIN AuditLogRelation AS Extent4 ON Extent3.PkAuditLogId = Extent4.FkAuditLogId
   WHERE Extent3.PkAuditLogId IN (1,3,4,5)) AS UnionAll1
ORDER BY UnionAll1.PkAuditLogId ASC, UnionAll1.C1 ASC}


Are you sure, you are trying "EntityFrameWorkEagerLoadingTestDevart" solution right ?

Because other one "EntityFrameWorkEagerLoadingTest" is example of the same code for EF 6 and MSSQL.

Please be sure, I created a project for you please try do...

Thanks, regards...
E. Faruk Pehlivanlı
efaruk
 
Posts: 2
Joined: Tue 18 Nov 2014 12:52

Re: Union All Issue

Postby MariiaI » Mon 24 Nov 2014 13:43

We have managed to reproduce the issue and this issue is not related to dotConnect for MySQL.
To resolve the issue, we have changed your database tables, namely, we have added "CHARACTER SET utf8" to the DDL scripts for all varchar columns.
Please refer to:
http://bugs.mysql.com/bug.php?id=21505
http://dev.mysql.com/doc/refman/5.0/en/charset-column.html
http://dev.mysql.com/doc/refman/5.1/en/charset-literal.html
http://stackoverflow.com/questions/20456152/mysql-error-illegal-mix-of-collations-for-operation-union

We are also sending you a modified DDL script with which the issue is not reproducible in our environment to the e-mail address you have provided in your forum profile. Please check that the letter is not blocked by your mail filter.

Please tell us if this helps.
MariiaI
Devart Team
 
Posts: 1472
Joined: Mon 13 Feb 2012 08:17


Return to dotConnect for MySQL