MySQL Bugs: #69922: Unknown column Extent1...

Bug #69922	Unknown column Extent1...
Submitted:	4 Aug 2013 10:16	Modified:	24 Sep 2014 23:32
Reporter:	John Korres	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.7.4.0	OS:	Windows
Assigned to:	Fernando Gonzalez.Sanchez	CPU Architecture:	Any

Description:
Entity framework 5 code first. Linq query to check whether a nullable datetime column is null results in a faulty sql query.

How to repeat:
Schema:

 public class Database : DbContext
    {

        public virtual DbSet<HomePromo> HomePromoes { get; set; }

    }

 public class HomePromo
    {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Key]
        public int ID { get; set; }
        
        public string Image { get; set; }

        public string Url { get; set; }

        public int DisplayOrder { get; set; }

        [Column("Active")]
        public bool Active { get; set; }
        [Column("ActiveFrom")]
        public DateTime? ActiveFrom { get; set; }
        [Column("ActiveTo")]
        public DateTime? ActiveTo { get; set; }
    }

Linq query:

 db
.HomePromoes
.Where(x =>
   x.Active
     &&
   (x.ActiveFrom == null || x.ActiveFrom <= now)
     &&
   (x.ActiveTo == null || x.ActiveTo >= now)
)
.OrderBy(x => x.DisplayOrder)
.ToArray()

Sql query generated:

SELECT
`Project1`.`ID`, 
`Project1`.`Image`, 
`Project1`.`Url`, 
`Project1`.`DisplayOrder`, 
`Project1`.`Active`, 
`Project1`.`ActiveFrom`, 
`Project1`.`ActiveTo`
FROM `HomePromoes` AS `Project1`
 WHERE ((`Project1`.`Active` = 1) AND ((`Extent1`.`ActiveFrom` IS  NULL) OR (`Project1`.`ActiveFrom` <= '2013-08-04 13:04:05.869'))) AND ((`Extent1`.`ActiveTo` IS  NULL) OR (`Project1`.`ActiveTo` >= '2013-08-04 13:04:05.869'))
 ORDER BY 
`Project1`.`DisplayOrder` ASC

Suggested fix:
It should generated `Project1`.`ActiveFrom` IS  NULL instead of `Extent1`.`ActiveFrom` IS  NULL

To extend this reports, checking for IS NULL on a column of *any* type, not just datetime, will cause this error, because it is looking for a non-existent Extent1 rather than Project1.

I downgraded to 6.6.5 and this problem went away.

Even using 6.8.0 still exhibits the 'Unknown Column' for Extent1 in quite a few circumstances, such as checking for null, using a datetime field in a where clause, using a subquery (.Any()) or using an OrderBy over a query.

Following upgrading the dev machine to Windows 8.1, Connector 6.6.5 isn't available to download. Running 6.6.6 exhibits the same problem.

TIP: I recovered the 6.6.5 dlls from another machine and used these manually and everything is working again.

So it appears that the issue was introduced between 6.6.5 > 6.6.6.

Hi Sam, 

Thanks for the feedback, the fix for 69922 for the test case in the bug report was released in 6.6.6 & 6.8.0.

It seems it still fails in other scenarios, I'll check them. 

Meanwhile can you provide sample queries for the Any / OrderBy cases?

thanks.

Added the following entry into the Connector/Net 6.6.7, 6.7.5, 6.8.4 changelogs:

"When using the Code First approach in Entity Framework 5, a LINQ query that checked whether a nullable column was null resulted in a faulty SQL query being generated."