Bug #99259 Invalid SQL generated for Entity Framework 6 query
Submitted: 14 Apr 2020 18:30 Modified: 7 Aug 2021 11:43
Reporter: Eric Johannsen Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version: OS:Windows
Assigned to: MySQL Verification Team CPU Architecture:Any

[14 Apr 2020 18:30] Eric Johannsen 
Description:
I'm attempting to migrate a .NET Framework / Entity Framework 6 application to use MySQL instead of SQL Server. The existing app works fine in SQL Server. An apparently straightforward EF query causes MySQL to generate invalid SQL. The exception is:

MySqlException: Unknown column 'Join1.Id1' in 'on clause'

How to repeat:
var syncQuery =
    ctx.SurveySyncGroups.Where(sg => sg.Name == name)
    .SelectMany(sg => sg.Syncs)
    .Include(s => s.StorageQuQuestions)
    .Include(s => s.StorageQuQuestions.Select(q => q.RowOptions))
    .Include(s => s.StorageQuQuestions.Select(q => q.ColumnOptions))
    .Include(s => s.StorageLabelToId)
    ;

SELECT
`UnionAll2`.`Id` AS `C1`,
`UnionAll2`.`Id1` AS `C2`,
`UnionAll2`.`Id2` AS `C3`,
`UnionAll2`.`Id3` AS `C4`,
`UnionAll2`.`QuInternalQuestionIdsToInclude_SerializedValue` AS `C5`,
`UnionAll2`.`SviSurveyId` AS `C6`,
`UnionAll2`.`QuSurveyId` AS `C7`,
`UnionAll2`.`SyncFileId` AS `C8`,
`UnionAll2`.`QuSurveyName` AS `C9`,
`UnionAll2`.`CreatedInQuUtc` AS `C10`,
`UnionAll2`.`LatestProcessedTimeUtc` AS `C11`,
`UnionAll2`.`Name` AS `C12`,
`UnionAll2`.`IsNves` AS `C13`,
`UnionAll2`.`IsFollowupSurvey` AS `C14`,
`UnionAll2`.`IsNonresponseSurvey` AS `C15`,
`UnionAll2`.`LastResponseId` AS `C16`,
`UnionAll2`.`LastModified` AS `C17`,
`UnionAll2`.`DefinitionDirectlyFromQu_Id` AS `C18`,
`UnionAll2`.`SurveySyncGroup_Id` AS `C19`,
`UnionAll2`.`C1` AS `C20`,
`UnionAll2`.`C2` AS `C21`,
`UnionAll2`.`C3` AS `C22`,
`UnionAll2`.`C4` AS `C23`,
`UnionAll2`.`C5` AS `C24`,
`UnionAll2`.`C6` AS `C25`,
`UnionAll2`.`C7` AS `C26`,
`UnionAll2`.`C8` AS `C27`,
`UnionAll2`.`C9` AS `C28`,
`UnionAll2`.`C10` AS `C29`,
`UnionAll2`.`C11` AS `C30`,
`UnionAll2`.`C12` AS `C31`,
`UnionAll2`.`C13` AS `C32`,
`UnionAll2`.`C14` AS `C33`,
`UnionAll2`.`C15` AS `C34`,
`UnionAll2`.`C16` AS `C35`,
`UnionAll2`.`C17` AS `C36`,
`UnionAll2`.`C18` AS `C37`,
`UnionAll2`.`C19` AS `C38`,
`UnionAll2`.`C20` AS `C39`,
`UnionAll2`.`C21` AS `C40`,
`UnionAll2`.`C22` AS `C41`,
`UnionAll2`.`C23` AS `C42`,
`UnionAll2`.`C24` AS `C43`,
`UnionAll2`.`C25` AS `C44`,
`UnionAll2`.`C26` AS `C45`,
`UnionAll2`.`C27` AS `C46`,
`UnionAll2`.`C28` AS `C47`,
`UnionAll2`.`C29` AS `C48`,
`UnionAll2`.`C30` AS `C49`
FROM ((SELECT
CASE WHEN (`UnionAll1`.`Id` IS NOT NULL) THEN (1)  ELSE (NULL) END AS `C1`,
`Project1`.`Id`,
`Project1`.`Id` AS `Id1`,
`Project1`.`Id1` AS `Id2`,
`Project1`.`Id1` AS `Id3`,
`Project1`.`QuInternalQuestionIdsToInclude_SerializedValue`,
`Project1`.`SviSurveyId`,
`Project1`.`QuSurveyId`,
`Project1`.`SyncFileId`,
`Project1`.`QuSurveyName`,
`Project1`.`CreatedInQuUtc`,
`Project1`.`LatestProcessedTimeUtc`,
`Project1`.`Name`,
`Project1`.`IsNves`,
`Project1`.`IsFollowupSurvey`,
`Project1`.`IsNonresponseSurvey`,
`Project1`.`LastResponseId`,
`Project1`.`LastModified`,
`Project1`.`DefinitionDirectlyFromQu_Id`,
`Project1`.`SurveySyncGroup_Id`,
`UnionAll1`.`Id` AS `C2`,
`UnionAll1`.`Id1` AS `C3`,
`UnionAll1`.`QuCode` AS `C4`,
`UnionAll1`.`SviQuestionCode` AS `C5`,
`UnionAll1`.`Text` AS `C6`,
`UnionAll1`.`LastModified` AS `C7`,
`UnionAll1`.`QuSurveySync_Id` AS `C8`,
`UnionAll1`.`C1` AS `C9`,
`UnionAll1`.`Id2` AS `C10`,
`UnionAll1`.`Id3` AS `C11`,
`UnionAll1`.`Number` AS `C12`,
`UnionAll1`.`Label` AS `C13`,
`UnionAll1`.`QuChoiceId` AS `C14`,
`UnionAll1`.`LastModified1` AS `C15`,
`UnionAll1`.`QuQuestion_Id` AS `C16`,
`UnionAll1`.`QuQuestion_Id1` AS `C17`,
`UnionAll1`.`C2` AS `C18`,
`UnionAll1`.`C3` AS `C19`,
`UnionAll1`.`C4` AS `C20`,
`UnionAll1`.`C5` AS `C21`,
`UnionAll1`.`C6` AS `C22`,
`UnionAll1`.`C7` AS `C23`,
`UnionAll1`.`C8` AS `C24`,
`UnionAll1`.`C9` AS `C25`,
NULL AS `C26`,
NULL AS `C27`,
NULL AS `C28`,
NULL AS `C29`,
NULL AS `C30`
FROM (SELECT
`Extent1`.`Id`,
`Extent2`.`Id` AS `Id1`,
`Extent2`.`QuInternalQuestionIdsToInclude_SerializedValue`,
`Extent2`.`SviSurveyId`,
`Extent2`.`QuSurveyId`,
`Extent2`.`SyncFileId`,
`Extent2`.`QuSurveyName`,
`Extent2`.`CreatedInQuUtc`,
`Extent2`.`LatestProcessedTimeUtc`,
`Extent2`.`Name`,
`Extent2`.`IsNves`,
`Extent2`.`IsFollowupSurvey`,
`Extent2`.`IsNonresponseSurvey`,
`Extent2`.`LastResponseId`,
`Extent2`.`LastModified`,
`Extent2`.`DefinitionDirectlyFromQu_Id`,
`Extent2`.`SurveySyncGroup_Id`
FROM `SurveySyncGroups` AS `Extent1` INNER JOIN `QuSurveySyncs` AS `Extent2` ON `Extent1`.`Id` = `Extent2`.`SurveySyncGroup_Id`
 WHERE (`Extent1`.`Name` = @p__linq__0) OR ((`Extent1`.`Name` IS  NULL) AND (@p__linq__0 IS  NULL))) AS `Project1` LEFT OUTER JOIN ((SELECT
CASE WHEN (`Extent4`.`Id` IS NOT NULL) THEN (1)  ELSE (NULL) END AS `C1`,
`Extent3`.`Id`,
`Extent3`.`Id` AS `Id1`,
`Extent3`.`QuCode`,
`Extent3`.`SviQuestionCode`,
`Extent3`.`Text`,
`Extent3`.`LastModified`,
`Extent3`.`QuSurveySync_Id`,
`Extent4`.`Id` AS `Id2`,
`Extent4`.`Id` AS `Id3`,
`Extent4`.`Number`,
`Extent4`.`Label`,
`Extent4`.`QuChoiceId`,
`Extent4`.`LastModified` AS `LastModified1`,
`Extent4`.`QuQuestion_Id`,
`Extent4`.`QuQuestion_Id1`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
NULL AS `C9`
FROM `QuQuestions` AS `Extent3` LEFT OUTER JOIN `QuQuestionOptions` AS `Extent4` ON `Extent3`.`Id` = `Extent4`.`QuQuestion_Id1`
 WHERE 1 = 1) UNION ALL (SELECT
2 AS `C1`,
`Extent5`.`Id`,
`Extent5`.`Id` AS `Id1`,
`Extent5`.`QuCode`,
`Extent5`.`SviQuestionCode`,
`Extent5`.`Text`,
`Extent5`.`LastModified`,
`Extent5`.`QuSurveySync_Id`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
NULL AS `C9`,
`Extent6`.`Id` AS `Id2`,
`Extent6`.`Id` AS `Id3`,
`Extent6`.`Number`,
`Extent6`.`Label`,
`Extent6`.`QuChoiceId`,
`Extent6`.`LastModified` AS `LastModified1`,
`Extent6`.`QuQuestion_Id`,
`Extent6`.`QuQuestion_Id1`
FROM `QuQuestions` AS `Extent5` INNER JOIN `QuQuestionOptions` AS `Extent6` ON `Extent5`.`Id` = `Extent6`.`QuQuestion_Id`
 WHERE 1 = 1)) AS `UnionAll1` ON `Join1`.`Id1` = `UnionAll1`.`QuSurveySync_Id`) UNION ALL (SELECT
2 AS `C1`,
`Extent7`.`Id`,
`Extent7`.`Id` AS `Id1`,
`Extent8`.`Id` AS `Id2`,
`Extent8`.`Id` AS `Id3`,
`Extent8`.`QuInternalQuestionIdsToInclude_SerializedValue`,
`Extent8`.`SviSurveyId`,
`Extent8`.`QuSurveyId`,
`Extent8`.`SyncFileId`,
`Extent8`.`QuSurveyName`,
`Extent8`.`CreatedInQuUtc`,
`Extent8`.`LatestProcessedTimeUtc`,
`Extent8`.`Name`,
`Extent8`.`IsNves`,
`Extent8`.`IsFollowupSurvey`,
`Extent8`.`IsNonresponseSurvey`,
`Extent8`.`LastResponseId`,
`Extent8`.`LastModified`,
`Extent8`.`DefinitionDirectlyFromQu_Id`,
`Extent8`.`SurveySyncGroup_Id`,
NULL AS `C2`,
NULL AS `C3`,
NULL AS `C4`,
NULL AS `C5`,
NULL AS `C6`,
NULL AS `C7`,
NULL AS `C8`,
NULL AS `C9`,
NULL AS `C10`,
NULL AS `C11`,
NULL AS `C12`,
NULL AS `C13`,
NULL AS `C14`,
NULL AS `C15`,
NULL AS `C16`,
NULL AS `C17`,
NULL AS `C18`,
NULL AS `C19`,
NULL AS `C20`,
NULL AS `C21`,
NULL AS `C22`,
NULL AS `C23`,
NULL AS `C24`,
NULL AS `C25`,
`Extent9`.`Id` AS `Id4`,
`Extent9`.`Id` AS `Id5`,
`Extent9`.`Label`,
`Extent9`.`QuId`,
`Extent9`.`QuSurveySync_Id`
FROM `SurveySyncGroups` AS `Extent7` INNER JOIN `QuSurveySyncs` AS `Extent8` ON `Extent7`.`Id` = `Extent8`.`SurveySyncGroup_Id` INNER JOIN `QuLabelToIds` AS `Extent9` ON `Extent8`.`Id` = `Extent9`.`QuSurveySync_Id`
 WHERE (`Extent7`.`Name` = @p__linq__0) OR ((`Extent7`.`Name` IS  NULL) AND (@p__linq__0 IS  NULL)))) AS `UnionAll2`
 ORDER BY
`UnionAll2`.`Id` ASC,
`UnionAll2`.`Id2` ASC,
`UnionAll2`.`C1` ASC,
`UnionAll2`.`C3` ASC,
`UnionAll2`.`C9` ASC
[7 Jul 2021 11:43] MySQL Verification Team 
Hello Eric Johannsen,

Thank you for the bug report.
Could you please provide complete repeatable test case (exact steps, sample project file, etc. - please make it as private if you prefer) to confirm this issue at our end?

Regards,
Ashwini Patil
[8 Aug 2021 1:00] Bugs System 
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".