[BUG] Multi-part identifier "XXX" could not be bound from an update statement

[tsileo-wdi]

Describe the bug
I'm trying to update a field based on values from an aliased column in a dynamic select statement that is included in the from clause. I'm getting this error, which the explain plan says is occuring in the FetchXML Query step:

Msg 4104, Level 16, State 1, Line 2
Sql error: Generic SQL error. CRM ErrorCode: -2147204784 Sql ErrorCode: -2146232060 Sql Number: 4104

Exception output:: System.Data.SqlClient.SqlException (0x80131904): The multi-part identifier "p.wdi_WorkType" could not be bound.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at Microsoft.Crm.Core.DataServices.Connection.QueryBlocking.CrmCommand.ExecuteReader()
   at Microsoft.Crm.Core.DataServices.Connection.CrmSqlDataAccessHelper.LockConnectionAndExecuteMethod[TResult](IDbConnection connection, Func`1 executeMethod)
   at Microsoft.Crm.CrmDbConnection.InternalExecuteWithRetryAbstract[TResult](Func`1 executeMethod, IDbCommand command, IConnectionRetryPolicy retryPolicy)
   at Microsoft.Crm.CrmDbConnection.<>c__DisplayClass236_0.<InternalExecuteReader>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute[TResult](ILogger logger, EventId eventId, ActivityType activityType, Func`1 func, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute[TResult](ILogger logger, XrmTelemetryActivityType activityType, Func`1 func)
   at Microsoft.Crm.CrmDbConnection.InternalExecuteReader(IDbCommand command, Nullable`1 commandBehavior, ICrmTransaction crmTransaction, IConnectionRetryPolicy retryPolicy, Int32 sourceLineNumber, String memberName, String sourceFilePath)
   at Microsoft.Crm.Platform.Server.DataEngine.SqlDataAccessManager.ExecuteQuery(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context, Boolean useTransaction, Nullable`1 commandTimeout, Int32 sourceLineNumber, String memberName, String sourceFilePath)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteQueryAbstract(CrmDbConnection dbConnection, IDbCommand command, ISqlExecutionContext context, Nullable`1 commandTimeout)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.ExecuteQueryAbstract(CrmDbConnection dbConnection, IDbCommand command, ExecutionContext context, Nullable`1 commandTimeout)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.QueryAndFillEntityCollection(CrmDbConnection dbConnection, IDbCommand dbCommand, BusinessEntityCollection entities, EntityExpression entityExp, PagingHelper pagingHelper, Boolean useEntityExpression, String aggregateLimitExceededName, ExecutionContext context)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.DoRetrieveMultiple(BusinessEntityCollection entities, EntityExpression entityExp, DatabaseQueryTarget queryTarget, PagingHelper pagingHelper, ExecutionContext context, Boolean needToSetRowVersion, Boolean isVersionNumberRequestedInRetrieve)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.<>c__DisplayClass588_0.<DoRetrieveMultiple>b__0()
   at Microsoft.PowerApps.CoreFramework.ActivityLoggerExtensions.Execute(ILogger logger, EventId eventId, ActivityType activityType, Action action, IEnumerable`1 additionalCustomProperties)
   at Microsoft.Xrm.Telemetry.XrmTelemetryExtensions.Execute(ILogger logger, XrmTelemetryActivityType activityType, Action action)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.RetrieveMultipleInternal(EntityExpression entityExpression, DatabaseQueryTarget queryTarget, ExecutionContext context)
   at Microsoft.Crm.BusinessEntities.BusinessProcessObject.RetrieveMultiple(EntityExpression entityExpression, DatabaseQueryTarget queryTarget, ExecutionContext context)
ClientConnectionId:17c5e417-1059-4e9a-8587-858384137dcf
Error Number:4104,State:1,Class:16
ClientConnectionId before routing:78d63469-bdf2-4f37-8939-8e97efe0ae16
Routing Destination:e9170aae1499.tr11384.westus1-a.worker.database.windows.net,11009
Error(s) details: 
Class: 0x10
LineNumber: 21
Message: The multi-part identifier "p.wdi_WorkType" could not be bound.
Number: 4104
Procedure: 
Server: e9170aae1499.tr11384.westus1-a.worker.database.windows.net,11009
Source: .Net SqlClient Data Provider
State: 1
ToString(): System.Data.SqlClient.SqlError: The multi-part identifier "p.wdi_WorkType" could not be bound.
--
: Microsoft Dynamics CRM has experienced an error. Reference number for administrators or support: #46DB66BC
See the Execution Plan tab for details of where this error occurred

To Reproduce
Query to reproduce the behavior:

update wdi_assignment 
set wdi_worktype = au.ParentWorkType
from wdi_assignment inner join 
(
    SELECT top 5 c.wdi_assignmentid,
                  p.wdi_worktype AS ParentWorkType
    FROM   wdi_assignment c INNER JOIN wdi_assignment AS p ON c.wdi_parentassignment = p.wdi_assignmentid
    WHERE  c.wdi_worktype <> p.wdi_worktype
    ) au on wdi_assignment.wdi_assignmentid = au.wdi_assignmentid

Expected behavior
The sub-select aliased as "au" works as expected, so I would expect the update to update the wdi_worktype on the matching records to the value of au.ParentWorkType.

Environment (please complete the following information):

• SQL 4 CDS edition: XrmToolBox
• Results of SELECT @@VERSION:
  Microsoft Dataverse - 9.2.24103.228
  SQL 4 CDS - 9.4.1.0
  Nov 12 2024 15:44:47
  Copyright © 2020 - 2024 Mark Carrington

Additional context
Note the incorrect case of wdi_WorkType in the error message - I suspect that's somehow related to the issue?
I also tried to do the same update without aliasing the p.wdi_worktype field in the subselect, but I get the same error.

[tsileo-wdi]

Update.... This version where I alias the table I'm updating generates the same error:

update c 
set c.wdi_worktype = au.ParentWorkType
from wdi_assignment as c inner join 
(
    SELECT top 5 c1.wdi_assignmentid,
                  p.wdi_worktype AS ParentWorkType
    FROM   wdi_assignment c1 INNER JOIN wdi_assignment AS p ON c1.wdi_parentassignment = p.wdi_assignmentid
    WHERE  c1.wdi_worktype <> p.wdi_worktype
    ) as au on c.wdi_assignmentid = au.wdi_assignmentid

However, this (far simpler!) query works as expected:

update c 
set wdi_worktype = p.wdi_worktype 
from wdi_assignment as c
inner join wdi_assignmenttype AS ty ON c.wdi_assignmenttype = ty.wdi_assignmenttypeid
inner join wdi_assignment AS p ON c.wdi_parentassignment = p.wdi_assignmentid
WHERE  ty.wdi_workflow = 957880002