MERGE job: `UnsupportedFeatureException[Cannot ORDER BY '(SELECT NULL FROM (empty_row))': invalid data type 'undefined'.]`
- 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
UnsupportedFeatureException[Cannot ORDER BY '(SELECT NULL FROM (empty_row))': invalid data type 'undefined'.]
```
## Details
We probed the PostgreSQL dialect. The Redshift dialect isn't different, and emits the same error message.
### PostgreSQL dialect
```sql
SELECT "date",
"currency_code",
"rate",
"base_currency",
"__dlt_load_id",
"__dlt_id"
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY "date", "currency_code", "base_currency"
ORDER BY
(SELECT NULL)) AS _dlt_dedup_rn,
"date",
"currency_code",
"rate",
"base_currency",
"__dlt_load_id",
"__dlt_id"
FROM "testdrive_staging"."exchange_latest") AS _dlt_dedup_numbered
WHERE _dlt_dedup_rn = 1
AND (1 = 1) ;
```
Contributor guide
No contributing guide indexed for this repository
Assessment
This issue has not been assessed yet.