MySQL Bugs: #69751: Invalid SQL query generated for query with Contains, OrderBy, and Take

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:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any

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".

Changed bug summary to reference the "Take" method.

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.

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.

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?

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.

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

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

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)

Hi, Fernando could you please sendme the custom build too?

Sent already by mail George. Regards.

When will this bug fix be released? Can I also have the custom build?

Hi Fernando Gonzalez Sanchez,
could you please send me your custom build also?

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.

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

Sorry Fernando, you can disregard my previous comment, I didn't read your comment about the released versions correctly. Thanks for your help.

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.

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.

Yes, please send me a custom build.

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.

I'm using 6.7.4.1 and this is still happening.
Is this really fixed?

Couldn't find version 6.7.5, so I suppose it isn't released, right?
Can I have a custom build?

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.