MySQL Bugs: #75828: Incorrect sorting when using GROUP BY and COUNT

Bug #75828	Incorrect sorting when using GROUP BY and COUNT
Submitted:	9 Feb 2015 18:32	Modified:	24 Feb 2015 21:17
Reporter:	Hans Wurst	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.9.5	OS:	Windows (Win7, x64)
Assigned to:	Roberto Ezequiel Garcia Ballesteros	CPU Architecture:	Any
Tags:	column renaming, connector, GROUP BY, sorting

Description:
The query builder uses an incorrect column alias for sorting. This happens when ordering by a result of the COUNT-method in a GROUP BY statement.

How to repeat:
EntityFramework 6.1.2 / VS 2013 SP3 / 5.5.33a-MariaDB / MySQL Connector .NET 6.9.5 (nuget)

Code-first model:

public partial class User
{
  [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public Int32 Id { get; set; }

  [Required, StringLength(100)]
  public String Name { get; set; }

  [Column("project")]
  public Int32? ProjectId { get; set; }

  [ForeignKey("ProjectId ")] 
  public virtual Project Project { get; set; }
}

public class Project
{
  [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
  public Int32 Id { get; set; }

  [Required, StringLength(20)]
  public String Name { get; set; }

  public String Description { get; set; }

  [InverseProperty("Project")]
  public virtual ICollection<User> Users { get; set; }
}

Linq-query:

var query = from u in base.Context.User
            group u by u.Project into pGroup
            orderby pGroup.Count() descending, pGroup.Key.Name
            select new Foobar
            {
                 Id = pGroup.Key.Id,
                 Name = pGroup.Key.Name ?? "<without project>"
            };

return query.ToList();

resulting sql query:

SELECT
  `Project1`.`C2` AS `C1`,
  `Project1`.`Id`,
  `Project1`.`C3` AS `C2`
FROM (
  SELECT
    `GroupBy1`.`A1` AS `C1`,
    `GroupBy1`.`K1` AS `Id`,
    `GroupBy1`.`K2` AS `Name`,
    1 AS `C2`,
    CASE WHEN (`GroupBy1`.`K2` IS  NULL) THEN (@gp1)  ELSE (`GroupBy1`.`K2`) END AS `C3`
  FROM (
    SELECT
      `Extent2`.`Id` AS `K1`,
      `Extent2`.`Name` AS `K2`,
      `Extent2`.`Description` AS `K3`,
      COUNT(1) AS `A1`
    FROM `user` AS `Extent1`
    LEFT OUTER JOIN `project` AS `Extent2` ON `Extent1`.`project` = `Extent2`.`Id`
    GROUP BY
      `Extent2`.`Id`,
      `Extent2`.`Name`,
      `Extent2`.`Description`
    ) AS `GroupBy1`
  ) AS `Project1`
ORDER BY
`Project1`.`C1` DESC,
`Project1`.`Name` ASC

The orderby has to target on "`GroupBy1`.`A1` AS `C1`", but in the most outer query another column uses the same alias "`Project1`.`C2` AS `C1`" and the sorting gets incorrect.

Suggested fix:
Don't reuse column aliases in this way.

Hello Hans Wurst,

Thank you for the bug report.
Verified this behavior on Visual Studio 2013, EntityFramework 6.1.2 with  MySQL Connector/Net 6.9.5.

Thanks,
Chiranjeevi.

SQL query:
SELECT
`Project1`.`Name`, 
`Project1`.`Id`, 
`Project1`.`C2` AS `C1`, 
`Project1`.`Id1`, 
`Project1`.`Name1`
FROM (SELECT
`GroupBy1`.`A1` AS `C1`, 
`GroupBy1`.`K1` AS `Id`, 
`GroupBy1`.`K2` AS `Name`, 
`Extent2`.`Id` AS `Id1`, 
`Extent2`.`Name` AS `Name1`, 
CASE WHEN (`Extent2`.`Id` IS  NULL) THEN (NULL)  ELSE (1) END AS `C2`
FROM (SELECT
`Extent1`.`Id` AS `K1`, 
`Extent1`.`Name` AS `K2`, 
COUNT(1) AS `A1`
FROM `project` AS `Extent1`
 GROUP BY 
`Extent1`.`Id`, 
`Extent1`.`Name`) AS `GroupBy1` LEFT OUTER JOIN `project` AS `Extent2` ON `GroupBy1`.`K1` = `Extent2`.`Id`) AS `Project1`
 ORDER BY 
`Project1`.`C1` DESC, 
`Project1`.`Name` ASC, 
`Project1`.`Id` ASC, 
`Project1`.`C2` ASC

This is not a bug since the order by is applied to `Project1`.`C1` even if a new alias `C1` is defined. `Project1`.`C1` is the same as `GroupBy1`.`A1` but in the outer select.

The query can be tested on any other query tool as console or Workbench.

After hours of updating and testing I got the problem. The query can be simplyfied a little bit:

SELECT
  `SubQuery`.`Static` AS `Random`,
  `SubQuery`.`Name`
FROM (
  SELECT
    RAND() AS `Random`,
    1 AS `Static`,
    `Name`
  FROM `foobar`
  ) AS `SubQuery`
ORDER BY
`SubQuery`.`Random`,
`SubQuery`.`Name`;

With the newest MySQL 5.6.22 the query is executed correctly and sorted at random. The problem is occuring in the newest MariaDB 10.0.16 where the result is sorted incorrectly by name.

But definitely no bug in MySQL Connector .NET.