Bug #69751 Invalid SQL query generated for query with Contains, OrderBy, and Take
Submitted: 15 Jul 2013 17:21 Modified: 5 Sep 2013 20:58
Reporter: Youssef Abidi Email Updates:
Status: Closed Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.7.4.1 OS:Microsoft Windows
Assigned to: Fernando Gonzalez.Sanchez CPU Architecture:Any

[15 Jul 2013 17:21] Youssef Abidi
Description:
Hi,

I am using MySQL.Data.Entities version 6.7.4.1, MySQL.Data version 6.7.4, and EntityFramework (code first) version 5.0.0, in a .Net 4.5 project.

When I try to execute a Linq query containing an order by clause and a call to the "Contains" method, the generated SQL query is invalid: it references an invalid alias "Extent1" (instead of "Project1").

How to repeat:
To repeat the issue, you need a DbContext with an entity:

class DataModel : DbContext
{
    public DbSet<Client> Clients { get; set; }
}

[Table("client")]
class Client
{
    [Column("id")]
    public int Id { get; set; }

    [Column("name")]
    public string Name { get; set; }
}

You then need to create the following IQueryable (topClients):

var clientName = "test";

var filteredClients =
    from client in model.Clients
    where client.Name.Contains(clientName)
    orderby client.Id descending
    select client;

var topClients = filteredClients.Take(10);

But the generated SQL for the topClients IQueryable is invalid:

SELECT
`Project1`.`id`, 
`Project1`.`name`
FROM `client` AS `Project1`
 WHERE (LOCATE(@p__linq__0, `Extent1`.`name`)) > 0
 ORDER BY 
`Project1`.`id` DESC LIMIT 10

As you can see, the WHERE clause references the "Extent1" alias, which doesn't exist.

Please note that the error only happens if the three conditions are met:
1. The order by clause is present.
2. The limit clause is present (through the call to the "Take" method).
3. The variable "clientName" is a variable and not a constant (const) and is not replaced with a string literal.

Suggested fix:
Replace the "Extent1" alias in the generated SQL query with the right one, i.e. "Project1".
[15 Jul 2013 17:25] Youssef Abidi
Changed bug summary to reference the "Take" method.
[16 Jul 2013 9:19] Youssef Abidi
Solution to repeat the issue (after building to retrieve the NuGet packages and after replacing the MySQL connection string)

Attachment: ConnectorBugReport.zip (application/octet-stream, text), 269.98 KiB.

[22 Jul 2013 3:24] Fernando Gonzalez.Sanchez
Verified, thanks for the bug report; and yes, this appeared after fix for http://bugs.mysql.com/bug.php?id=65723.

The bug is only reproducible in Code First (not in Database First).

A fix will appear in Connector/NET versions 6.5.7, 6.6.6, 6.7.5 & 6.8.0.
[6 Aug 2013 16:27] Ryan Stecker
This issue is fairly critical for us. When can we expect to see the fixed versions released?

Additionally, there had been either a svn or bazaar code repository for Connector/Net at some point. Is this available anymore?
[6 Aug 2013 17:05] Fernando Gonzalez.Sanchez
Hi,

We don't have scheduled a 6.7.5 for a while (probably until October), a 6.6.6 will appear soon including the fix.

However the patch corresponding to the fix is this:

=== modified file 'Source/MySql.Data.Entity/Generators/SqlGenerator.cs'
--- a/Source/MySql.Data.Entity/Generators/SqlGenerator.cs	2013-06-18 05:47:39 +0000
+++ b/Source/MySql.Data.Entity/Generators/SqlGenerator.cs	2013-07-22 01:14:52 +0000
@@ -525,6 +525,7 @@
     {
       BinaryFragment bf = sf as BinaryFragment;
       ColumnFragment cf = sf as ColumnFragment;
+      LiteralFragment lf = sf as LiteralFragment;
       if (bf != null)
       {
         VisitAndReplaceTableName(bf.Left, oldTable, newTable);
@@ -534,6 +535,13 @@
       {
         cf.TableName = newTable;
       }
+      else if (lf != null)
+      {
+        // In Code first, functions like IEnumerable.Contains, are translated to strings (LiteralFragment)
+        // instead of a FunctionFragment.
+        lf.Literal = lf.Literal.Replace(string.Format("`{0}`", oldTable),
+          string.Format("`{0}`", newTable));
+      }
     }

We don't have a public bazaar repository, the source code can be downloaded from  the same location than installer: http://dev.mysql.com/downloads/connector/net/

If you prefer, I can send you by email a custom build.

Regards.
[7 Aug 2013 1:43] Ryan Stecker
Hi Fernando,

Your patch has been very useful, thank you!

However, I'm seeing an issue with VisitAndReplaceTableName when the function traverses into a IsNullFragment, that fragment is ignored as it's not an instance of LiteralFragment or ColumnFragment.

The following LINQ:

    var bans = db.Bans
        .Where( b => !string.IsNullOrEmpty( b.Name ) && b.Name.Contains( input ) )
        .OrderByDescending( b => b.ID )
        .Skip( page * BANS_PER_PAGE )
        .Take( BANS_PER_PAGE )
        .ToList();

Generates invalid SQL:

SELECT
`Project1`.`ID`, 
`Project1`.`Name`
FROM `bans` AS `Project1`
 WHERE (NOT ((`Extent1`.`Name` IS  NULL) OR ((LENGTH(`Project1`.`Name`)) = 0))) AND ((LOCATE(@p__linq__0, `Project1`.`Name`)) > 0)
 ORDER BY 
`Project1`.`ID` DESC LIMIT 0,20

The table name `Extent1` is still present after the VisitAndReplaceTableName logic.

I believe this may also be an issue for other fragment types, but I'm not familiar enough with the connector/net code to make a judgement.

Regards,
Ryan
[7 Aug 2013 8:10] Ryan Stecker
I can confirm this also affects LikeFragment.

But this issue apparently goes a little further, such as when Connector/NET tries to fuse deeper subqueries.

For instance, in my case it is visiting this WHERE fragment:

((@gp2 = `Project4`.`SteamID`) OR (EXISTS(SELECT
1 AS `C1`
FROM (SELECT
1 AS `X`) AS `SingleRowTable2`
 WHERE `Extent2`.`IP` LIKE '127.0.0.%'))) AND ((`Extent1`.`SteamID` = `Extent2`.`SteamID`) AND (`Extent1`.`IP` = `Extent2`.`IP`))

Eventually the logic will visit the inner EXISTS fragment, which is also not handled in VisitAndReplaceTableName (leading to invalid generated SQL), and at some point should handle the inner SELECT fragment correctly.

Hopefully these posts have provided enough information.

Regards,
Ryan
[8 Aug 2013 1:39] Fernando Gonzalez.Sanchez
Hi Ryan,

Thanks so much for your feedback, after reviewing the code & test cases, I found the previous fix was too naive, included below a patch for same fix that seems to work for all scenarios (basically implemented Visitor pattern for SqlFragment and derived classes), we appreciate if you can give it a try (again, if you prefer, I can send you a custom build by email)...

(since the patch does not fit in comment I am sending it by email)
[16 Aug 2013 21:02] George Domingos dos Ramos
Hi, Fernando could you please sendme the custom build too?
[16 Aug 2013 22:18] Fernando Gonzalez.Sanchez
Sent already by mail George. Regards.
[2 Sep 2013 16:19] Jeremy Pollard
When will this bug fix be released? Can I also have the custom build?
[4 Sep 2013 6:32] Pjotrs Okunevs
Hi Fernando Gonzalez Sanchez,
could you please send me your custom build also?
[4 Sep 2013 9:53] Fernando Gonzalez.Sanchez
Hi guys,

This bug has been fixed and released already in Connector/NET 6.5.7 & 6.6.6.
Will also appear in a future 6.7.5 & 6.8.0.

Thanks.
[4 Sep 2013 10:08] Youssef Abidi
Thanks Fernando!

Do you know where the 6.7.5 release can be downloaded?

At this moment (2013-09-04 10:08 AM UTC), only the 6.7.4 version seem to be available in these websites:

http://www.nuget.org/packages/MySql.Data/
http://www.nuget.org/packages/MySQL.Data.Entities/
http://dev.mysql.com/downloads/connector/net/#downloads
[4 Sep 2013 10:10] Youssef Abidi
Sorry Fernando, you can disregard my previous comment, I didn't read your comment about the released versions correctly. Thanks for your help.
[4 Sep 2013 13:51] Jeremy Pollard
Is there a timeline for the 6.7.5 release? We're currently using 6.7.4 and have several use cases on our project this bug is effecting...we need a fix asap.
[4 Sep 2013 15:33] Fernando Gonzalez.Sanchez
Hi Jeremy,

The release 6.7.5 is due to October approximately.

If you prefer I can send you the patch code or a custom build.
[4 Sep 2013 15:51] Jeremy Pollard
Yes, please send me a custom build.
[5 Sep 2013 20:58] Daniel So
Added the following changelog entry to Connector/NET versions 6.5.7, 6.6.6, and 6.7.5:

Executing a LINQ query containing an 'order by' clause and a call to the 'Contains' method using the 'Take' method resulted in a bad SQL query, which made reference to a no-existent alias.
[31 Jan 2014 12:55] Joberto Diniz
I'm using 6.7.4.1 and this is still happening.
Is this really fixed?
[31 Jan 2014 13:25] Joberto Diniz
Couldn't find version 6.7.5, so I suppose it isn't released, right?
Can I have a custom build?
[31 Jan 2014 17:44] Fernando Gonzalez.Sanchez
Hi, 

The release of Cnet 6.7.5 has been delayed.
But the fix is also available in 6.6.x (since 6.6.6) and 6.8.x (since 6.8.0, currently 6.8.3) both recommended for production.

Regards.