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

[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.

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

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) ;

[amotl]

A more minimal reproducer that works on PostgreSQL but fails on CrateDB. It doesn't need any DDL at all.

SELECT ROW_NUMBER() OVER (PARTITION BY TRUE ORDER BY (SELECT NULL));

CrateDB fails:

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

PostgreSQL works:

 row_number
------------
          1
(1 row)

@jeeminso, @matriv: Maybe you are already tracking this at crate/crate?
@hlcianfagna, @hammerhead: Do you by chance know any workaround?

With DDL

CREATE TABLE t01 (text TEXT);

SELECT
  ROW_NUMBER() OVER (PARTITION BY "text" ORDER BY (SELECT NULL))
FROM
  t01;

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

SELECT
  ROW_NUMBER() OVER (PARTITION BY "text" ORDER BY (SELECT NULL))
FROM
  t01;

 row_number
------------
(0 rows)

[hlcianfagna]

@hlcianfagna, @hammerhead: Do you by chance know any workaround?

SELECT
      ROW_NUMBER() OVER (PARTITION BY "text" ORDER BY (SELECT NULL::text))
    FROM
      t01;

(the type of the cast does not seem to matter - it works also with integer)

[amotl]

Thank you so much. I will check which data type to cast to is applicable at the original location of the code (I think the value is obviously not always NULL), but it's so good to hear there is a chance for a workaround today. 💯

[amotl]

The other more significant issue which currently defies the "merge" strategy of dlt with CrateDB is #6. It needs to be brought to attention at crate/crate, and hopefully will be processed as a bug. 🙏

[amotl]

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

• Support subqueries in ORDER BY clauses crate#18108