MySQL Bugs: #80127: Linq with order by specific field and take or first fails

Bug #80127	Linq with order by specific field and take or first fails
Submitted:	23 Jan 2016 11:24	Modified:	24 Feb 2016 12:26
Reporter:	Juan Llanso	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Optimizer	Severity:	S2 (Serious)
Version:	5.7.10-log	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	linq, orderby, take

Description:
I get the following error: "Unknown column 'Extent1.Id' in 'where clause'" when trying to perform a query that contains an orderby for a specific value and a take(1) in the same query.

We have checked that this error only happens in MySQL, in SQLServer the same query returns a correct list.

We work with ASP.NET MVC Entity Framework 6, using Visual Studio 2015. We have also test it using LInqPad obtaining the same result.

Also it seems that the SQL the Linq query is giving us is incorrect. We have copied that SQL sentence and test it in several mySQL clients such as Navicat and we have the same error:
"Unknown column 'Extent1.Id' in 'where clause'" 

How to repeat:
This is the query:
var queryString = (from entity in _dbContext.Links
                   from translations in _dbContext.LinksTrs.Where(p => p.LinkId == entity.Id).OrderBy(p => (p.LanguageCode == currentLanguage) ? 0 : 1).Take(1).DefaultIfEmpty()
                   select new LinksAdminListModel()
                   {
                     Id = entity.Id,
                     Name = (translations == null) ? "" : translations.Name,
                     Url = entity.Url,
                     Active = entity.Active
                   });

What I need is, instead sorting just by LanguageCode, I need to tell the query to sort by a specifid LanguageCode first and then the rest.
The problem is the combination of the ORDER with the TAKE(1), but I do need to take just the first one...

This is the SQL that corresponds to the linq the system is returning:
SELECT
`Apply1`.`Id`, 
`Apply1`.`Name`, 
`Apply1`.`Url`, 
`Apply1`.`Active`
FROM (SELECT
`Extent1`.`Id`, 
`Extent1`.`OrderPos`, 
`Extent1`.`Url`, 
`Extent1`.`Active`, 
`Extent1`.`DateCreated`, 
`Extent1`.`DateModified`, 
`Extent1`.`UserIdModified`, 
(SELECT
`Project1`.`C1`
FROM (SELECT
CASE WHEN ((`Extent2`.`LanguageCode` = 'es-ES') OR   ((`Extent2`.`LanguageCode` IS  NULL) AND ('es-ES' IS  NULL))) THEN (1)  WHEN (NOT ((`Extent2`.`LanguageCode` = 'es-ES') AND ((CASE WHEN (`Extent2`.`LanguageCode` IS  NULL) THEN (1)  ELSE (0) END) = (CASE WHEN ('es-ES' IS  NULL) THEN (1)  ELSE (0) END)))) THEN (0) END AS `C1`, 
`Extent2`.`Name`, 
`Extent2`.`LanguageCode`
FROM `LinkTr` AS `Extent2`
 WHERE `Extent2`.`LinkId` = `Extent1`.`Id`) AS `Project1`
 ORDER BY 
`Project1`.`C1` DESC, 
`Project1`.`LanguageCode` ASC LIMIT 1) AS `C1`, 
(SELECT
`Project1`.`Name`
FROM (SELECT
CASE WHEN ((`Extent2`.`LanguageCode` = 'es-ES') OR ((`Extent2`.`LanguageCode` IS  NULL) AND ('es-ES' IS  NULL))) THEN (1)  WHEN (NOT ((`Extent2`.`LanguageCode` = 'es-ES') AND ((CASE WHEN (`Extent2`.`LanguageCode` IS  NULL) THEN (1)  ELSE (0) END) = (CASE WHEN ('es-ES' IS  NULL) THEN (1)  ELSE (0) END)))) THEN (0) END AS `C1`, 
`Extent2`.`Name`, 
`Extent2`.`LanguageCode`
FROM `LinkTr` AS `Extent2`
 WHERE `Extent2`.`LinkId` = `Extent1`.`Id`) AS `Project1`
 ORDER BY 
`Project1`.`C1` DESC, 
`Project1`.`LanguageCode` ASC LIMIT 1) AS `Name`, 
(SELECT
`Project1`.`LanguageCode`
FROM (SELECT
CASE WHEN ((`Extent2`.`LanguageCode` = 'es-ES') OR ((`Extent2`.`LanguageCode` IS  NULL) AND ('es-ES' IS  NULL))) THEN (1)  WHEN (NOT ((`Extent2`.`LanguageCode` = 'es-ES') AND ((CASE WHEN (`Extent2`.`LanguageCode` IS  NULL) THEN (1)  ELSE (0) END) = (CASE WHEN ('es-ES' IS  NULL) THEN (1)  ELSE (0) END)))) THEN (0) END AS `C1`, 
`Extent2`.`Name`, 
`Extent2`.`LanguageCode`
FROM `LinkTr` AS `Extent2`
 WHERE `Extent2`.`LinkId` = `Extent1`.`Id`) AS `Project1`
 ORDER BY 
`Project1`.`C1` DESC, 
`Project1`.`LanguageCode` ASC LIMIT 1) AS `LanguageCode`
   FROM `Link` AS `Extent1`) AS `Apply1`

Suggested fix:
I don't know but it seems the MySQL is reading badly the linq and returning a SQL query with some errors.

I wonder if this could this be same as:

http://bugs.mysql.com/bug.php?id=79549
(Regression: “unknown column” for outer computed values used inside a subquery)?

Hello, thank you for the quick answer.

I don't know if it is the same issue, but our problem is, different from that post, we are not generating the SQL, EF6 is generating the SQL query from our LINQ query.

Is there maybe a configuration file where we can change a parameter to fix this?

Thanks.

Please provide a complete test case (create table, etc) so we can run the query or wait the next release. Thanks.

I do have the Entities...but please let me know if you need another thing

public class Link : EntityBase
{
    public int OrderPos { get; set; }
    public string Url { get; set; }
    public bool Active { get; set; }

    public virtual IEnumerable<LinkTr> Translations { get; set; }
}

public class LinkTr : EntityBaseTr
{
    public string Name { get; set; }
    public string Summary { get; set; }

    //[ForeignKey("Link")]
    public int LinkId { get; set; }
    public virtual Link Link { get; set; }

    //[ForeignKey("Language")]
    public string LanguageCode { get; set; }
    public virtual Language Languages { get; set; }
}

public class EntityBase
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)] //[Key, DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }

    public DateTime? DateCreated { get; set; }
    public DateTime? DateModified { get; set; }
    public int UserIdModified { get; set; }
}

public class EntityBaseTr
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public DateTime? DateCreated { get; set; }
}

Thank you for the feedback. Sorry if I wasn't clear I meant a test case a SQL script which can be run using the mysql.exe client, the test case should have the create table(s) statements, insert data if needed and the offended query. Thanks.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".