Sqlite: "target object/alias may not appear in FROM clause" in ExecuteUpdate

@ajcvickers

For example, test Replace_ColumnExpression_in_column_setter:

await ss.Set<Owner>()
    .SelectMany(e => e.OwnedCollections)
    .ExecuteUpdateAsync(s => s.SetProperty(o => o.Value, "SomeValue"))

... produces the following SQL:

UPDATE "OwnedCollection" AS "o0"
SET "Value" = 'SomeValue'
FROM "Owner" AS "o"
INNER JOIN "OwnedCollection" AS "o0" ON "o"."Id" = "o0"."OwnerId"

... which fails on SQLite with "target object/alias may not appear in FROM clause: o0" (SQL Server produces a slightly different SQL that does work).

Note that this SQL may be optimizable, removing the Owner altogether (see #33946), at which point the simplified SQL would work; but this doesn't necessarily mean the problem wouldn't exist in some other query form.

Originally flagged by @ajcvickers in #33937

Same problem in PG: npgsql/efcore.pg#3253

Fix dotnet#33947. Need to match on whether the expressions are pointi…

From my comment over at npgsl/efcore.pg#3253 this can be fixed by a simple change in one line
if (ReferenceEquals(updateExpression.Table, joinExpression?.Table ?? table))
becomes
if (updateExpression.Table.Alias == (joinExpression?.Table.Alias ?? table.Alias))

This test ends up with updateExpression.Table not being the same instance as joinExpression?.Table even though they may be pointing at the same table with the same alias

I will do a PR for this shortly

@roji

@roji Was just checking things before I do the PR and found another ReferenceEquals in the same function.

 if (selectExpression is
     {
         Offset: null,
         Limit: null,
         Having: null,
         Orderings: [],
         GroupBy: [],
         Projection: [],
     }
     && (selectExpression.Tables.Count == 1
         || !ReferenceEquals(selectExpression.Tables[0], updateExpression.Table)
         || selectExpression.Tables[1] is InnerJoinExpression
         || selectExpression.Tables[1] is CrossJoinExpression))
 {

Is this meant to be checking on the exact same instance, or is it the case of as long as it refers to the same table irrespective or whether or not it is the same instance

Actually I'm not sure if that line is fully needed. You can comment it out and all current tests pass - both for sqlite and on efcore.pg (SQL Server uses its own override that doesnt have those last couple of lines). Given that all tests pass means that it isnt the sole statement that is causing that section to be true. There is always another expression (table count, inner join, cross join) being true. Otherwise it would drop down to the InvalidOperationException

Any thoughts on that part?

Navigation Menu

Repository navigation

Sqlite: "target object/alias may not appear in FROM clause" in ExecuteUpdate #33947

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Sqlite: "target object/alias may not appear in FROM clause" in ExecuteUpdate #33947

Description

Activity

roji commented on Sep 1, 2024

ChrisJollyAU commented on Sep 2, 2024

ChrisJollyAU commented on Sep 3, 2024

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions