cast as SIGNED when comparing enums

cast as SIGNED when comparing enums

Postby 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.
nickolsky
 
Posts: 5
Joined: Tue 23 Dec 2014 15:32

Re: cast as SIGNED when comparing enums

Postby 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?
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44

Re: cast as SIGNED when comparing enums

Postby nickolsky » Tue 02 Jun 2015 01:10

Nevermind. This was a bug of EntityFramework - https://entityframework.codeplex.com/workitem/186
nickolsky
 
Posts: 5
Joined: Tue 23 Dec 2014 15:32

Re: cast as SIGNED when comparing enums

Postby Shalex » Tue 02 Jun 2015 16:05

Thank you for letting us know.
Shalex
Devart Team
 
Posts: 7530
Joined: Thu 14 Aug 2008 12:44


Return to dotConnect for MySQL