Bug #74904 Seemingly wrong SQL generated with LIKE and ORDER BY
Submitted: 17 Nov 2014 17:10 Modified: 16 Dec 2014 21:21
Reporter: Daniele Fusi Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / NET Severity:S1 (Critical)
Version:6.9.5.0 OS:Windows (8.1)
Assigned to: Assigned Account CPU Architecture:Any
Tags: Code First, entity framework, like

[17 Nov 2014 17:10] Daniele Fusi
Description:
My scenario is a code-first EF 6.1.1 data layer DLL (C# .NET 4.5.1). I'm using the latest versions of MySql connector (MySql.Data and MySql.Data.Entity.EF6, both at version 6.9.5.0), in a project upgraded from version 6.8.x. The project has a number of unit tests which were all successful before the upgrade; after it, I'm getting failures whenever (at least it seems so) a string comparison with LIKE should be used.

If I inspect the generated SQL with the debugger, I can see some artifacts and what seems an unresolved parameter in the built SQL; it looks like this (note the "p__linq__0" value between %% in the LIKE clause; apart from this, the subquery with C1 looks a little weird but I'm not sure this is related):

SELECT
`Project1`.`C1`, 
`Project1`.`Id`, 
`Project1`.`CategoryId`, 
`Project1`.`Title`, 
`Project1`.`Subtitle`, 
`Project1`.`Author`, 
`Project1`.`Tag`, 
`Project1`.`Isbn`, 
`Project1`.`Description`, 
`Project1`.`IsPresent`, 
`Project1`.`Year`, 
`Project1`.`Edition`, 
`Project1`.`Note`, 
`Project1`.`Id1`, 
`Project1`.`Name`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`CategoryId`, 
`Extent1`.`Title`, 
`Extent1`.`Subtitle`, 
`Extent1`.`Author`, 
`Extent1`.`Tag`, 
`Extent1`.`Isbn`, 
`Extent1`.`Description`, 
`Extent1`.`IsPresent`, 
`Extent1`.`Year`, 
`Extent1`.`Edition`, 
`Extent1`.`Note`, 
`Extent2`.`Id` AS `Id1`, 
`Extent2`.`Name`, 
1 AS `C1`
FROM `Book` AS `Extent1` INNER JOIN `Category` AS `Extent2` ON `Extent1`.`CategoryId` = `Extent2`.`Id`
 WHERE `Extent1`.`Title` LIKE '%p__linq__0%') AS `Project1`
 ORDER BY 
`Project1`.`Title` ASC, 
`Project1`.`Year` ASC LIMIT 0,20

The DDL SQL for the relevant tables is:

a) for category:

CREATE TABLE `category` (
  `Id` varchar(5) NOT NULL,
  `Name` varchar(50) NOT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

b) for book:

CREATE TABLE `book` (
  `Id` varchar(50) NOT NULL,
  `CategoryId` varchar(5) NOT NULL,
  `Title` varchar(100) NOT NULL,
  `Subtitle` varchar(200) NOT NULL,
  `Author` varchar(100) NOT NULL,
  `Tag` varchar(50) NOT NULL,
  `Isbn` varchar(20) NOT NULL,
  `Description` varchar(5000) NOT NULL,
  `IsPresent` tinyint(1) NOT NULL,
  `Year` smallint(6) NOT NULL,
  `Edition` smallint(6) NOT NULL,
  `Note` varchar(5000) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_CategoryId` (`CategoryId`) USING HASH,
  CONSTRAINT `FK_Book_Category_CategoryId` FOREIGN KEY (`CategoryId`) REFERENCES `category` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

The code I'm using in my repository looks like the excerpt pasted below; the method gets an options object with the paging and filtering parameters, and should return a page of items matching them:

public IEnumerable<Book> GetBooksPage(BookFilterOptions options)
{
    if (options == null) throw new ArgumentNullException("options");

    using (CatalogContext db = CreateCatalogContext())
    {
        var books = db.Books.Include("Category").AsNoTracking().AsQueryable();

#if DEBUG
        // logging for diagnosing generated SQL
        db.Database.Log = s => Debug.WriteLine(s);
#endif

        if (!String.IsNullOrEmpty(options.Title))
            books = books.Where(b => b.Title.Contains(options.Title));

        if (!String.IsNullOrEmpty(options.CategoryId))
            books = books.Where(b => b.CategoryId == options.CategoryId);

        if (options.IsPresent.HasValue)
            books = books.Where(b => b.IsPresent == options.IsPresent.Value);

        if (options.MinYear > 0)
            books = books.Where(b => b.Year >= options.MinYear);

        if (options.MaxYear > 0)
            books = books.Where(b => b.Year <= options.MaxYear);

        if (options.Edition > 0)
            books = books.Where(b => b.Edition == options.Edition);

        books = books.OrderBy(b => b.Title).ThenBy(b => b.Year);

        Book[] aPageBooks = (options.PageSize == 0
            ? books.ToArray()
            : books.Skip((options.PageNumber - 1) * options.PageSize).Take(options.PageSize).ToArray());

        return aPageBooks;
    }
}

If I avoid setting the title filter, this works fine as no string filtering is involved. As soon as I set the title filter, I get no record as the generated SQL seems broken.

Similarly, all the tests involving string comparisons with LIKE seem broken too, while others pass.

How to repeat:
At present I did not manage to reproduce the issue if I just use a couple of tables extracted from the real database. I tried using a fake data context with just these two tables and make a similar query, but this returned an SQL where the LIKE parameter correctly contains the string value of my filter. The whole repro code was:

const int nPageSize = 20, nPageNumber = 1;

using (CatalogContext db = new CatalogContext())
{
    var books = db.Books.Include("Category").AsNoTracking().AsQueryable();
    books = books.Where(b => b.Title.Contains("test"));
    books = books.OrderBy(b => b.Title).ThenBy(b => b.Year);
    Book[] aPageBooks = (nPageSize == 0
        ? books.ToArray()
        : books.Skip((nPageNumber - 1) * nPageSize).Take(nPageSize).ToArray());

    Console.WriteLine(aPageBooks.Length);
}
[16 Dec 2014 21:21] Gabriela Martinez Sanchez
Hi Daniele

Thank you for the bug report.
This is duplicate of Bug #74918.

Thanks,