Generated query for SQLite is incorrect when deleting records with JOIN and table aliases

[pra-kk1w]

• Laravel Version: 5.8.28
• PHP Version: 7.2.10
• Database Driver & Version: SQLite 3.20.1

Description:

When using Query Builder to generate a delete query with JOINs and table aliases for a SQLite database results in a incorrect SQL query that on execution throws an exception. When the database was switched from SQLite to MySQL the generated query was executed successfully.

From what I understood this line in Illuminate/Database/Query/Grammars/SQLiteGrammar.php (L194) is responsible for the malformed queries.

This issue has some similarities to #18098, though its seems to require a different solution, since DELETE a FROM table_a AS a appears to execute fine on MySQL, but not on SQLite.

Generated query:

delete from "table_a" as "a" where "rowid" in (select "table_a" as "a.rowid" from "table_a" as "a" inner join "table_b" as "b" on "a"."id" = "b"."table_a_id")

Exception:
SQLSTATE[HY000]: General error: 1 near "as": syntax error (SQL: delete from "table_a" as "a" where "rowid" in (select "table_a" as "a.rowid" from "table_a" as "a" inner join "table_b" as "b" on "a"."id" = "b"."table_a_id"))

Steps To Reproduce:

Setup project to use SQLite database and create the following tables:

Schema::create('table_a', function (Blueprint $table) {
    $table->increments('id');
});

Schema::create('table_b', function (Blueprint $table) {
    $table->increments('id');
    $table->unsignedInteger('table_a_id');
});

Run following query builder code:

DB::table('table_a as a')
    ->join('table_b AS b', 'a.id', '=', 'b.table_a_id')
    ->delete();

Workaround:

Disclaimer: this workaround wasn't thoroughly tested so it might not work in all scenarios.

Code:

DB::table('table_a')
    ->join('table_b AS b', 'table_a.id', '=', 'b.table_a_id')
    ->delete();

Generated query:

delete from "table_a" where "rowid" in (select "table_a"."rowid" from "table_a" inner join "table_b" as "b" on "table_a"."id" = "b"."table_a_id")

[driesvints]

Appreciating a PR with a regression test for this if that would be possible.

[staudenmeir]

The syntax error has been fixed in SQLite 3.23 or 3.24, I can't find it in the changelog.

Even then, the query doesn't work properly. I'll fix it.