Invalid MySQL SQL generated: MySql.Data.MySqlClient.MySqlException : You can't specify target table 'A' for update in FROM clause

Given the following model:

A has a nullable one-to-one association with itself

And the following testcase

[Test]
public void TestA()
{
    var context = new LightSpeedContext<LightSpeedModel1UnitOfWork>("Context") { Logger = new ConsoleLogger(), };

    var b = new B() {};

    using (var uow = context.CreateUnitOfWork())
    {
        uow.Add(b);
        uow.SaveChanges();
    }

    using (var uow = context.CreateUnitOfWork())
    {
        uow.Remove(b);
        uow.SaveChanges();
    }
}

The following SQL is generated

UPDATE
  A
SET
  TargetId = NULL
WHERE
  EXISTS (
    SELECT
      A.*
    FROM
      A
    WHERE
      A.Id = A.TargetId AND
      A.BId = 61
  )

Which results in

MySql.Data.MySqlClient.MySqlException : You can't specify target table 'A' for update in FROM clause
   at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
   at MySql.Data.MySqlClient.NativeDriver.GetResult(ref Int32 affectedRow, ref Int32 insertedId)
   at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId)
   at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
   at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
   at ...‡()
   at Mindscape.LightSpeed.Profiling.Interceptor.ExecuteCommand(Func`1 baseExecutor, IUnitOfWork unitOfWork, IDbCommand command)
   at ..‡(IUnitOfWork , IDbCommand , Func`1 )
   at ..‡( )
   at ..‡(IEnumerable`1 )
   at ..()
   at ..‡(ICollection`1 )
   at ..‡(UnitOfWorkBase , IEnumerable`1 , IEnumerable`1 )
   at Mindscape.LightSpeed.UnitOfWork.SaveChanges(Boolean reset)
   at LightSpeedRepro.Class1.TestA() in Class1.cs: line 149

I found some additional information at http://stackoverflow.com/questions/4429319/you-cant-specify-target-table-for-update-in-from-clause

I assume this is a bug =)

//Johan W

Unfortunately this is a limitation that crops up with MySQL (but not with other providers). There isn't anything we can do about this Im afraid.

Jeremy

According to the StackOverflow question here: http://stackoverflow.com/a/14302701, the MySql issue can be worked around by placing the inner query in another sub-query. I assume that would only have to be done for for self referencing entities. Wouldn't that solve this issue?

For now, we have worked around the issue by making a new entity with two one-to-one associations with the target entity.

Edit: In case there is no plan to support this, could you at least throw an exception during query generation for MySql in this scenario?

//JohanW

I will have a look at what we can do to try and detect this to throw an exception but I suspect it wont be possible as at the point where detection would be sensible we are in a provider agnostic context, likewise when an exception is surfaced however it may be possible to add something higher up the stack to allow the exception to be inspected by the provider which would allow for a rethrow with a better error message.

Will let you know if we make an update here.

Jeremy