Sqlite: Using column from table being updated in left join predicate generates invalid SQL

[smitpatel]

Test: Update_with_cross_join_left_join_set_constant

        AssertExecuteUpdateSql(
            @"UPDATE ""Customers"" AS ""c""
SET ""ContactName"" = 'Updated'
FROM (
    SELECT ""c0"".""CustomerID"", ""c0"".""Address"", ""c0"".""City"", ""c0"".""CompanyName"", ""c0"".""ContactName"", ""c0"".""ContactTitle"", ""c0"".""Country"", ""c0"".""Fax"", ""c0"".""Phone"", ""c0"".""PostalCode"", ""c0"".""Region""
    FROM ""Customers"" AS ""c0""
    WHERE ""c0"".""City"" IS NOT NULL AND (""c0"".""City"" LIKE 'S%')
) AS ""t""
LEFT JOIN (
    SELECT ""o"".""OrderID"", ""o"".""CustomerID"", ""o"".""EmployeeID"", ""o"".""OrderDate""
    FROM ""Orders"" AS ""o""
    WHERE ""o"".""OrderID"" < 10300
) AS ""t0"" ON ""c"".""CustomerID"" = ""t0"".""CustomerID""
WHERE ""c"".""CustomerID"" LIKE 'F%'");

Now throws invalid SQL error earlier used to work. Change in PR #28884
Filed https://www.sqlite.org/forum/forumpost/09862e12c7

[roji]

😠

The person on that issue is asking for our schema. We're just like users when we file issues on other repos 🤣

[smitpatel]

The person on that issue is asking for our schema. We're just like users when we file issues on other repos 🤣

Not really. I don't think there is anything specific to schema there. It is like if some user file issue on us that string.IndexOf is not working on SQLServer, we don't ask for schema if they post the query and generated SQL or exception message.

[roji]

This seems like it's the same as npgsql/efcore.pg#2478, which I worked around in npgsql/efcore.pg#2478. Note that this limitation blocks some fairly basic scenarios, like two inner joins.

I've posted another comment on the SQLite issue, to clarify whether they intend to fix this or not. We can simply port my PostgreSQL workaround to SQLite to fix this.

[roji]

This doesn't seem to be a problem with MySQL/MariaDB (the test isn't skipped), @lauxjpn can you confirm?

So this seems to be a problem with PostgreSQL and SQLite, and I worked around it in PostgreSQL (npgsql/efcore.pg#2554). We can wait to see if the SQLite folks react on their issue, and if users complain, we can consider bringing the same workaround from PG to SQLite.

[lauxjpn]

This doesn't seem to be a problem with MySQL/MariaDB (the test isn't skipped), @lauxjpn can you confirm?

Yes, there are no issues with the Update_with_cross_join_left_join_set_constant test and any version of MySQL and MariaDB that we support.

[roji]

Thanks @lauxjpn