Error on delete when joining a table due to missing join operation in query

[samdze]

Describe the bug

When performing a delete query on a table using a join to filter based on other tables' values, the resulting query doesn't include the join operation.
The query fails as the db can't find the joined tables' columns.

Doing the same query with a SELECT (all()) instead of DELETE (delete()) works as expected.

Reproduced on PostgreSQL and SQLite, but other drivers could be affected too.

To Reproduce

Steps to reproduce the behavior:

1. Build a query like this:

try await UserTaskPivot.query(on: req.db)
    .join(Task.self, on: \UserTaskPivot.$task.$id == \Task.$id)
    .filter(Task.self, \Task.$project.$id == project.requireID())
    .delete()

// or

try await UserTaskPivot.query(on: req.db)
    .join(parent: \.$task)
    .filter(Task.self, \Task.$project.$id == project.requireID())
    .delete()

2. The resulting query lacks the join statement, and generates an error:

PostgreSQL

[ codes.vapor.application ] [ DEBUG ] query delete user_task filters=[task[project_id] = 29498586-42B3-4D92-9D9D-E1D3E1EEAFE4] [database-id: raw, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (FluentKit/QueryBuilder.swift:293)
[ codes.vapor.application ] [ DEBUG ] DELETE FROM "user_task" WHERE "task"."project_id" = $1 [29498586-42B3-4D92-9D9D-E1D3E1EEAFE4] [database-id: raw, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (FluentPostgresDriver/FluentPostgresDatabase.swift:29)
[ codes.vapor.application ] [ TRACE ] Run action [database-id: raw, psql_connection_action: sendParseDescribeBindExecuteSync(PostgresNIO.PostgresQuery(sql: "DELETE FROM \"user_task\" WHERE \"task\".\"project_id\" = $1", binds: PostgresNIO.PostgresBindings(metadata: [PostgresNIO.PostgresBindings.Metadata(dataType: UUID, format: binary)], bytes: ByteBuffer { readerIndex: 0, writerIndex: 20, readableBytes: 20, capacity: 128, storageCapacity: 128, slice: _ByteBufferSlice { 0..<128 }, storage: 0x0000600002c24000 (128 bytes) }))), psql_connection_id: 3, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (PostgresNIO/New/PostgresChannelHandler.swift:217)
[ codes.vapor.application ] [ TRACE ] Backend message received [database-id: raw, psql_connection_id: 3, psql_message: .error(PostgresNIO.PostgresBackendMessage.ErrorResponse(fields: [Routine: "errorMissingRTE", Code: "42P01", Message: "missing FROM-clause entry for table \"task\"", Line: "3553", Position: "31", File: "parse_relation.c", Severity: "ERROR", Localized Severity: "ERROR"])), request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (PostgresNIO/New/PostgresChannelHandler.swift:103)
[ codes.vapor.application ] [ TRACE ] Run action [database-id: raw, psql_connection_action: failQuery(PostgresNIO.ExtendedQueryContext, with: PostgresNIO.PSQLError(base: PostgresNIO.PSQLError.Base.server(PostgresNIO.PostgresBackendMessage.ErrorResponse(fields: [Routine: "errorMissingRTE", Code: "42P01", Message: "missing FROM-clause entry for table \"task\"", Line: "3553", Position: "31", File: "parse_relation.c", Severity: "ERROR", Localized Severity: "ERROR"]))), cleanupContext: nil), psql_connection_id: 3, request-id: AB6D810B-26E6-42F1-A3E4-487CA5927A69] (PostgresNIO/New/PostgresChannelHandler.swift:217)
...
[ codes.vapor.application ] [ WARNING ] server: missing FROM-clause entry for table "task" (errorMissingRTE) [request-id: 89E7A41C-77AD-49AE-808D-E7B54491D626] (Vapor/Middleware/ErrorMiddleware.swift:42)

SQLite

[ codes.vapor.application ] [ DEBUG ] query delete user_task filters=[task[project_id] = C01D220F-42FD-437A-8C31-4D7EE47B781E] [database-id: raw, request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (FluentKit/QueryBuilder.swift:293)
[ codes.vapor.application ] [ DEBUG ] DELETE FROM "user_task" WHERE "task"."project_id" = ? ["C01D220F-42FD-437A-8C31-4D7EE47B781E"] [database-id: raw, request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (SQLiteNIO/SQLiteConnection.swift:161)
2022-09-22 17:13:35.708834+0200 Run[73062:11117473] [logging] no such column: task.project_id in "DELETE FROM "user_task" WHERE "task"."project_id" = ?"
[ codes.vapor.application ] [ WARNING ] error: no such column: task.project_id [request-id: 25C25462-8E19-47C8-80DE-442755C9CB39] (Vapor/Middleware/ErrorMiddleware.swift:42)

Expected behavior

The join operation should be included in the final query like it is done in a normal SELECT query.

Here's a SELECT query and resulting logs using the same filters on SQLite:

try await UserTaskPivot.query(on: req.db)
    .join(parent: \.$task)
    .filter(Task.self, \Task.$project.$id == project.requireID())
    .all()

Logs:

[ codes.vapor.application ] [ DEBUG ] query read user_task filters=[task[project_id] = C01D220F-42FD-437A-8C31-4D7EE47B781E] [database-id: raw, request-id: 55BE2766-3393-4FE5-855B-BC87F0FF30FD] (FluentKit/QueryBuilder.swift:293)
[ codes.vapor.application ] [ DEBUG ] SELECT "user_task"."id" AS "user_task_id", "user_task"."created_at" AS "user_task_created_at", "user_task"."user_id" AS "user_task_user_id", "user_task"."task_id" AS "user_task_task_id", "task"."id" AS "task_id", "task"."created_at" AS "task_created_at", "task"."updated_at" AS "task_updated_at", "task"."name" AS "task_name", "task"."project_id" AS "task_project_id", "task"."status" AS "task_status", "task"."start_date" AS "task_start_date", "task"."due_date" AS "task_due_date", "task"."content" AS "task_content" FROM "user_task" INNER JOIN "task" ON "user_task"."task_id" = "task"."id" WHERE "task"."project_id" = ? ["C01D220F-42FD-437A-8C31-4D7EE47B781E"] [database-id: raw, request-id: 55BE2766-3393-4FE5-855B-BC87F0FF30FD] (SQLiteNIO/SQLiteConnection.swift:161)

The query is built correctly.

Environment

• Vapor Framework version: 4.65.2
• Vapor Toolbox version: 18.5.1
• OS version: macOS 12.6

EDIT: seems the same as vapor/fluent-kit#506