MERGE job: `psycopg2.errors.UndefinedTable: Relation '"testdrive".d' unknown`
- Dominant language
- Python
- Stars
- 0
- Forks
- 1
- PR merge metrics
- No merged PRs in 30d
Description
## Problem
Originating at dlt's `SqlJobClientBase. _create_merge_followup_jobs` and descendants, the usual merge job emits an SQL statement which fails on CrateDB.
```text
RelationUnknown[Relation 'schema.table' unknown]
```
## Details
We probed two different dialects.
### PostgreSQL dialect
```sql
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");
```
```text
RelationUnknown[Relation 'doc.d' unknown]
```
### Redshift dialect
```sql
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");
```
```text
RelationUnknown[Relation 'testdrive.exchange_latest' unknown]
```
Contributor guide
No contributing guide indexed for this repository
Research direction
Start at dlt's _create_merge_followup_jobs and its descendants, then compare the PostgreSQL and Redshift SQL shown in the report with CrateDB's supported DELETE syntax. Trace how the merge follow-up statement is generated and reproduce the failing merge against CrateDB. Done means the generated merge job no longer reports an unknown relation.
Written by the indexing model from the issue text.
Assessment
- Tech stack
- python, sql
- Domain
- data-engineering, database
- Issue type
- Bug
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Activity status
- Quiet
- Clarity
- Mostly clear
- Newbie friendliness
- 48/100