crate / crate/dlt-cratedb

MERGE job: `UnsupportedFeatureException[Cannot ORDER BY '(SELECT NULL FROM (empty_row))': invalid data type 'undefined'.]`

Open
#7 6 comments 0 reactions 0 assignees View on GitHub
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.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.