What happens?

The array(subquery) function doesn't maintain ordering when combining DISTINCT with ORDER BY

To Reproduce

create table t (i int);
insert into t values (1),(2),(3),(4),(4);
select 
  array(select distinct i from t order by i desc) as a,
  array(select distinct i from t order by i desc) as b,
  array(select distinct i from t order by i desc) as c;

Every time I run the query, I get a different result, e.g.

|a        |b        |c        |
|---------|---------|---------|
|[2,1,4,3]|[4,3,2,1]|[2,4,1,3]|

This doesn't happen when I remove DISTINCT:

|a        |b        |c        |
|---------|---------|---------|
|[4,3,2,1]|[4,3,2,1]|[4,3,2,1]|

Using a derived table doesn't help, the problem still exists:

select 
  array(select * from (select distinct i from t) order by i desc) as a,
  array(select * from (select distinct i from t) order by i desc) as b,
  array(select * from (select distinct i from t) order by i desc) as c;

As a workaround, I can use GROUP BY:

select 
  array(select i from t group by i order by i desc) as a,
  array(select i from t group by i order by i desc) as b,
  array(select i from t group by i order by i desc) as c;

Note, I couldn't test the latest snapshots because of:

• DuckDB JDBC 0.10.1 Not Available in Maven Central #11244

OS:

Microsoft Windows [Version 10.0.22631.3296]

DuckDB Version:

0.10.0

DuckDB Client:

JDBC

Full Name:

Lukas Eder

Affiliation:

Data Geekery

Have you tried this on the latest nightly build?

I have tested with a release build (and could not test with a nightly build)

Have you tried the steps to reproduce? Do they include all relevant data and configuration? Does the issue you report still appear there?

• Yes, I have