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
© 2021, Oracle Corporation and/or its affiliates
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