Union All Issue

[efaruk]

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/0B6eYIL ... sp=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ı

[MariiaI]

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.

[efaruk]

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ı

[MariiaI]

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/ ... olumn.html
http://dev.mysql.com/doc/refman/5.1/en/ ... teral.html
http://stackoverflow.com/questions/2045 ... tion-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.