cast as SIGNED when comparing enums

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
nickolsky
Posts: 5
Joined: Tue 23 Dec 2014 15:32

cast as SIGNED when comparing enums

Post by nickolsky » Mon 25 May 2015 16:58

Hello,

I am using entity framework 6, dotConnect MySql 8.3.407.6.

Here is LINQ query I am running from C# app:

Code: Select all

processedDocumentIDs = db.ProcessedDocuments
                    .Where(o => o.IsForwardResponded == DBBool.False
                                && o.IsReadyToRespond == DBBool.True)
                    .Select(o => new { o.ProcessedDocumentID, o.ReceivedDocument.OwnerInstanceID })
                    .Where(o => o.OwnerInstanceID == InstanceContext.Instance.RunningInstanceID || o.OwnerInstanceID == null)
                    .Select(o => o.ProcessedDocumentID)
                    .Take(100000)
                    .ToList();
Columns IsForwardResponded, IsReadyToRespond are defined as:

Code: Select all

public DBBool IsForwardResponded { get; set; }
public DBBool IsReadyToRespond { get; set; }
They are using DBBool enum (for historical reasons)

Code: Select all

public enum DBBool : byte
    {
        [Display(Name = "No")]
        False = 0,
        [Display(Name = "Yes")]
        True = 1
    }
On database side, columns defined as:

Code: Select all

    IsForwardResponded TINYINT UNSIGNED NOT NULL,
    IsReadyToRespond TINYINT UNSIGNED DEFAULT 0 NOT NULL,
Also, there is index defined on IsForwardResponded, IsReadyToRespond.

Now the question. When I run query mentioned above, and use SHOW FULL PROCESSLIST, I see the following query executed in MySQL:

Code: Select all

SELECT
Limit1.ProcessedDocumentID
FROM ( SELECT
	Extent1.ProcessedDocumentID
	FROM  ProcessedDocument AS Extent1
	INNER JOIN ReceivedDocument AS Extent2 ON Extent1.ReceivedDocumentID = Extent2.ReceivedDocumentID
	WHERE (((CAST(Extent1.IsForwardResponded AS SIGNED)) = 0) AND ((CAST(Extent1.IsReadyToRespond AS SIGNED)) = 1)) AND (((Extent2.OwnerInstanceID = 3) OR ((Extent2.OwnerInstanceID IS NULL) AND (3 IS NULL))) OR (Extent2.OwnerInstanceID IS NULL))
	LIMIT 100000
)  AS Limit1;

Question is - why in this query unnecessary cast to SIGNED is added, i.e. (CAST(Extent1.IsForwardResponded AS SIGNED) ? Both values are unsigned tinyint (or byte on c# side). This cast causes performance issues (mysql fails to use proper index, and query with cast runs several seconds (same query, but without cast runs fractions of second). Is there any setting in devart mysql provider to prevent this behavior (unnecessary cast)?


Mysql version is 5.6.22

Thanks,
Artem.

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: cast as SIGNED when comparing enums

Post by Shalex » Wed 27 May 2015 14:23

We cannot reproduce the issue. Could you please send us a small complete test project with the corresponding DDL/DML script?

nickolsky
Posts: 5
Joined: Tue 23 Dec 2014 15:32

Re: cast as SIGNED when comparing enums

Post by nickolsky » Tue 02 Jun 2015 01:10

Nevermind. This was a bug of EntityFramework - https://entityframework.codeplex.com/workitem/186

Shalex
Site Admin
Posts: 9543
Joined: Thu 14 Aug 2008 12:44

Re: cast as SIGNED when comparing enums

Post by Shalex » Tue 02 Jun 2015 16:05

Thank you for letting us know.

Post Reply