MySQL Bugs: #78798: Generated SQL requests column that doesnt exist -

Bug #78798	Generated SQL requests column that doesnt exist -
Submitted:	12 Oct 2015 1:33	Modified:	12 Nov 2015 7:10
Reporter:	Jeremy Drummond	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	6.9.7	OS:	Windows
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Using MySql with Entity Framework 6 and doing a nested query such as:

    var template = dbContext.Templates
        .Include(t => t.Sections)
        .Include(t => t.Sections.Select(s => s.Questions.Select(q => q.AnswerCertaintyOptions)))
        .Include(t => t.Sections.Select(s => s.Questions.Select(q => q.AnswerOptions)))
        .FirstOrDefault(template => template.TemplateId == 1);

Throws an exception. The error I get is: 

    "Unknown column 'Apply1.TemplateSectionId' in 'field list'"

Full Error details:

System.Data.Entity.Core.EntityCommandExecutionException was unhandled by user code
  HResult=-2146232004
  Message=An error occurred while executing the command definition. See the inner exception for details.
  Source=EntityFramework
  StackTrace:
       at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       at System.Data.Entity.Core.Objects.Internal.ObjectQueryExecutionPlan.Execute[TResultType](ObjectContext context, ObjectParameterCollection parameterValues)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__6()
       at System.Data.Entity.Core.Objects.ObjectContext.ExecuteInTransaction[T](Func`1 func, IDbExecutionStrategy executionStrategy, Boolean startLocalTransaction, Boolean releaseConnectionOnSuccess)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.<>c__DisplayClass7.<GetResults>b__5()
       at System.Data.Entity.Infrastructure.DefaultExecutionStrategy.Execute[TResult](Func`1 operation)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.GetResults(Nullable`1 forMergeOption)
       at System.Data.Entity.Core.Objects.ObjectQuery`1.<System.Collections.Generic.IEnumerable<T>.GetEnumerator>b__0()
       at System.Data.Entity.Internal.LazyEnumerator`1.MoveNext()
       at System.Linq.Enumerable.FirstOrDefault[TSource](IEnumerable`1 source)
       at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.<GetElementFunction>b__1[TResult](IEnumerable`1 sequence)
       at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.ExecuteSingle[TResult](IEnumerable`1 query, Expression queryRoot)
       at System.Data.Entity.Core.Objects.ELinq.ObjectQueryProvider.System.Linq.IQueryProvider.Execute[TResult](Expression expression)
       at System.Data.Entity.Internal.Linq.DbQueryProvider.Execute[TResult](Expression expression)
       at System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source, Expression`1 predicate)
       at pwc.vsat.api.WebApiApplication.TEMP_INIT_TEMPLATE() in C:\Dev\pwc.vsat\pwc.vsat.server\pwc.vsat.api\Global.asax.cs:line 40
       at pwc.vsat.api.WebApiApplication.Application_Start() in C:\Dev\pwc.vsat\pwc.vsat.server\pwc.vsat.api\Global.asax.cs:line 31
  InnerException: 
       ErrorCode=-2147467259
       HResult=-2147467259
       Message=Unknown column 'Apply1.TemplateSectionId' in 'field list'
       Number=1054
       Source=MySql.Data
       StackTrace:
            at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
            at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId)
            at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId)
            at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
            at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
            at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
            at MySql.Data.Entity.EFMySqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.<Reader>b__c(DbCommand t, DbCommandInterceptionContext`1 c)
            at System.Data.Entity.Infrastructure.Interception.InternalDispatcher`1.Dispatch[TTarget,TInterceptionContext,TResult](TTarget target, Func`3 operation, TInterceptionContext interceptionContext, Action`3 executing, Action`3 executed)
            at System.Data.Entity.Infrastructure.Interception.DbCommandDispatcher.Reader(DbCommand command, DbCommandInterceptionContext interceptionContext)
            at System.Data.Entity.Internal.InterceptableDbCommand.ExecuteDbDataReader(CommandBehavior behavior)
            at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
            at System.Data.Entity.Core.EntityClient.Internal.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
       InnerException: 

The generated SQL is:

SELECT
`Project4`.`TemplatePublicId`, 
`Project4`.`TemplateId`, 
`Project4`.`Name`, 
`Project4`.`Description`, 
`Project4`.`TemplateVersion`, 
`Project4`.`Date`, 
`Project4`.`C17` AS `C1`, 
`Project4`.`TemplateSectionPublicId`, 
`Project4`.`TemplateSectionId`, 
`Project4`.`Title`, 
`Project4`.`Description1`, 
`Project4`.`TemplateId1`, 
`Project4`.`IntroductionSteps`, 
`Project4`.`C16` AS `C2`, 
`Project4`.`C2` AS `C3`, 
`Project4`.`C3` AS `C4`, 
`Project4`.`C4` AS `C5`, 
`Project4`.`C5` AS `C6`, 
`Project4`.`C6` AS `C7`, 
`Project4`.`C7` AS `C8`, 
`Project4`.`C8` AS `C9`, 
`Project4`.`C9` AS `C10`, 
`Project4`.`C1` AS `C11`, 
`Project4`.`C10` AS `C12`, 
`Project4`.`C11` AS `C13`, 
`Project4`.`C12` AS `C14`, 
`Project4`.`C13` AS `C15`, 
`Project4`.`C14` AS `C16`, 
`Project4`.`C15` AS `C17`
FROM (SELECT
`Apply2`.`TemplateId`, 
`Apply2`.`Name`, 
`Apply2`.`Description`, 
`Apply2`.`TemplatePublicId`, 
`Apply2`.`TemplateVersion`, 
`Apply2`.`Date`, 
`Apply1`.`TemplateSectionId`, 
`Apply1`.`Title`, 
`Apply1`.`Description` AS `Description1`, 
`Apply1`.`TemplateSectionPublicId`, 
`Apply1`.`TemplateId` AS `TemplateId1`, 
`Apply1`.`IntroductionSteps`, 
`Apply1`.`C1`, 
`Apply1`.`QuestionNumber` AS `C2`, 
`Apply1`.`QuestionId` AS `C3`, 
`Apply1`.`TemplateSectionId` AS `C4`, 
`Apply1`.`QuestionNumber1` AS `C5`, 
`Apply1`.`QuestionText` AS `C6`, 
`Apply1`.`AssumptionText` AS `C7`, 
`Apply1`.`InformationSource` AS `C8`, 
`Apply1`.`Expertise` AS `C9`, 
`Apply1`.`Id` AS `C10`, 
`Apply1`.`TemplateQuestionId` AS `C11`, 
`Apply1`.`Certainty` AS `C12`, 
`Apply1`.`C2` AS `C13`, 
`Apply1`.`C3` AS `C14`, 
`Apply1`.`C4` AS `C15`, 
CASE WHEN (`Apply1`.`TemplateSectionId` IS  NULL) THEN (NULL)  WHEN (`Apply1`.`QuestionNumber` IS  NULL) THEN (NULL)  ELSE (1) END AS `C16`, 
CASE WHEN (`Apply1`.`TemplateSectionId` IS  NULL) THEN (NULL)  ELSE (1) END AS `C17`
FROM (SELECT
`Filter1`.`TemplateId`, 
`Filter1`.`Name`, 
`Filter1`.`Description`, 
`Filter1`.`TemplatePublicId`, 
`Filter1`.`TemplateVersion`, 
`Filter1`.`Date`
FROM (SELECT
`Extent1`.`TemplateId`, 
`Extent1`.`Name`, 
`Extent1`.`Description`, 
`Extent1`.`TemplatePublicId`, 
`Extent1`.`TemplateVersion`, 
`Extent1`.`Date`
FROM `Template` AS `Extent1`
 WHERE 1 = `Extent1`.`TemplatePublicId`) AS `Filter1`) AS `Apply2`) AS `Project4`
 ORDER BY 
`Project4`.`TemplateId` ASC, 
`Project4`.`C17` ASC, 
`Project4`.`TemplateSectionId` ASC, 
`Project4`.`C16` ASC, 
`Project4`.`C3` ASC, 
`Project4`.`C1` ASC

 

How to repeat:
Do nested includes as described above. 

Suggested fix:
This seems to be similar to Bug #72004 - http://bugs.mysql.com/bug.php?id=72004

You can successfully retrieve data if you simplify the query to:

         var template = dbContext.Templates
        .Include(t => t.Sections)
        .Include(t => t.Sections.Select(s => s.Questions.Select(q => q.AnswerCertaintyOptions)))
        .FirstOrDefault(template => template.TemplateId == 1);

    OR

        var template = dbContext.Templates
        .Include(t => t.Sections)
        .Include(t => t.Sections.Select(s => s.Questions.Select(q => q.AnswerOptions)))
        .FirstOrDefault(template => template.TemplateId == 1);

The issue seems to be with multiple nested queries at once.

Hello Jeremy Drummond,

Thank you for the bug report.
Could you please provide repeatable test case (exact create table statements, model, context class, etc.) to confirm this issue at our end?

Thanks,
Chiranjeevi.

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

@Chiranjeevi Battula,

It's been a long time since this was active but I can confirm that the bug isn't resolved yet. Since you asked for a repeatable test case, please let me provide that:

So, the model can literally be anything (e.g., entities with only an `Id` and navigation properties. Here's a complete and verifiable example that reproduces the bug:

1) Example of the query that causes the error:

    context.Countries.
        Include(c => c.States.Select(s => s.Cities.Select(ci => ci.Cars))).
        Include(c => c.States.Select(s => s.Cities.Select(ci => ci.Buildings))).ToList();

2) The context class:

    [DbConfigurationType(typeof(MySqlEFConfiguration))]
    class MyContext: DbContext
    {
        public MyContext():base("server={ServerName};port={Port};database={DbB=Name};uid={Username};password={Pass}")
        {
            Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>());
        }
        public DbSet<Country> Countries { get; set; }
    }

3) The entities used in the query:

    class Country
    {
        public int Id { get; set; }
        public virtual ICollection<State> States { get; set; }

    }

    class State
    {
        public int Id { get; set; }

        [ForeignKey("ParentCountry")]
        public int countryId { get; set; }
        public virtual Country ParentCountry { get; set; }

        public virtual ICollection<City> Cities { get; set; }
    }

    class City
    {
        public int Id { get; set; }

        [ForeignKey("ParentState")]
        public int StateId { get; set; }
        public virtual State ParentState { get; set; }

        public virtual ICollection<Car> Cars { get; set; }
        public virtual ICollection<Building> Buildings { get; set; }
    }

    class Car
    {
        public int Id { get; set; }

        [ForeignKey("ParentCity")]
        public int CityId { get; set; }
        public virtual State ParentCity { get; set; }
    }

    class Building
    {
        public int Id { get; set; }

        [ForeignKey("ParentCity")]
        public int CityId { get; set; }
        public virtual State ParentCity { get; set; }
    }

4) Important note:

Please note that this only happens at the third level (the second level of `Select`), for example, the following would work just fine:

    context.Countries.
        Include(c => c.States.Select(s => s.Cities.Select(ci => ci.Cars))).
        Include(c => c.States.Select(s => s.Laws.Select(l => l.PrivacyLaws))).ToList();

Assuming that `Law` is a second child of `State`.

Hope that provides enough information to identify the problem as it's definitely enough to reproduce the bug, but I'll be happy to provide more information if required.