MERGE job: `psycopg2.errors.UndefinedTable: Relation '"testdrive".d' unknown`

[amotl]

Problem

Originating at dlt's SqlJobClientBase. _create_merge_followup_jobs and descendants, the usual merge job emits an SQL statement which fails on CrateDB.

RelationUnknown[Relation 'schema.table' unknown]

Details

We probed two different dialects.

PostgreSQL dialect

DELETE
FROM "testdrive"."exchange_latest" AS d
WHERE EXISTS
    (SELECT 1
     FROM "testdrive_staging"."exchange_latest" AS s
     WHERE d."date" = s."date"
       AND d."currency_code" = s."currency_code"
       AND d."base_currency" = s."base_currency");

RelationUnknown[Relation 'doc.d' unknown]

Redshift dialect

DELETE
FROM "testdrive"."exchange_latest"
WHERE EXISTS
    (SELECT 1
     FROM "testdrive_staging"."exchange_latest"
     WHERE "testdrive"."exchange_latest"."date" = "testdrive_staging"."exchange_latest"."date"
       AND "testdrive"."exchange_latest"."currency_code" = "testdrive_staging"."exchange_latest"."currency_code"
       AND "testdrive"."exchange_latest"."base_currency" = "testdrive_staging"."exchange_latest"."base_currency");

RelationUnknown[Relation 'testdrive.exchange_latest' unknown]

[amotl]

We haven't been able to compress this into a minimal reproducer in order to create a ticket at crate/crate. We think it could qualify as a bug, so it might receive elevated attention out of the box.

[hlcianfagna]

If "date","currency_code","base_currency" happens to be the PK and it is the same for "testdrive"."exchange_latest" and "testdrive_staging"."exchange_latest" we could do:

DELETE
FROM "testdrive"."exchange_latest"
WHERE _id in (SELECT  stagingtable._id FROM "testdrive_staging"."exchange_latest" stagingtable);

[amotl]

We submitted a report to upstream, but will also evaluate your proposed workaround on the next iteration.

• Support DELETE with correlated subqueries crate#18107