Repository: duckdb/duckdb (language: python)

Issue:
Cannot use `rowid` or `row_number() OVER ()` in `ARRAY`
### What happens?

It isn't possible to use the `rowid` pseudo-column or a `row_number()` window function to work  around the fact that the `ARRAY` operator doesn't preserve order (see https://github.com/duckdb/duckdb/issues/15011).

### To Reproduce

```sql
CREATE OR REPLACE TABLE df AS (
    FROM range(1_000_000) SELECT range AS x
);
```
Then

```sql
SELECT ARRAY(FROM df SELECT x ORDER BY row_number() OVER ());
```
```
BinderException: Binder Error: aggregate function calls cannot contain window function calls
```

```sql
SELECT ARRAY(FROM df SELECT x ORDER BY rowid);
```
```
BinderException: Binder Error: Referenced column "rowid" not found in FROM clause!
Candidate bindings: "unnamed_subquery.x"
```

Despite both queries within the `ARRAY` operator working on their own:

```sql
FROM df SELECT x ORDER BY rowid;
```
```
┌────────────────────────┐
│           x            │
│         int64          │
├────────────────────────┤
│                      0 │
│                      1 │
│                      2 │
│                      3 │
│                      4 │
│                      5 │
│                      6 │
│                      7 │
│                      8 │
│                      9 │
│                      · │
│                      · │
```

and 

```sql
FROM df SELECT x ORDER BY row_number() OVER ()
```
```
┌────────────────────────┐
│           x            │
│         int64          │
├────────────────────────┤
│                      0 │
│                      1 │
│                      2 │
│                      3 │
│                      4 │
│                      5 │
│                      6 │
│                      7 │
│                      8 │
│                      9 │
│                      · │
```


PS:

It is possible to workaround all of this via

```sql
SELECT ARRAY(SELECT x FROM (SELECT x FROM df ORDER BY row_number() OVER ()));
```

but I don't understand why (if `ARRAY` generally preserved order, I wouldn't have this problem to begin with, so why does it preserve the order from within a nested subquery?)
and confusingly it's not possible to use the seemingly equivalent


```sql
SELECT ARRAY(SELECT x FROM (SELECT x FROM df ORDER BY rowid));
```

I've opened a separate issue about this (#15052).


### OS:

Linux

### DuckDB Version:

1.1.0

### DuckDB Client:

Python

### Hardware:

i5, x64

### Full Name:

Soeren Wolfers

### Affiliation:

G-Research

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have
Cannot use `rowid` or `row_number() OVER ()` in `ARRAY`
### What happens?

It isn't possible to use the `rowid` pseudo-column or a `row_number()` window function to work  around the fact that the `ARRAY` operator doesn't preserve order (see https://github.com/duckdb/duckdb/issues/15011).

### To Reproduce

```sql
CREATE OR REPLACE TABLE df AS (
    FROM range(1_000_000) SELECT range AS x
);
```
Then

```sql
SELECT ARRAY(FROM df SELECT x ORDER BY row_number() OVER ());
```
```
BinderException: Binder Error: aggregate function calls cannot contain window function calls
```

```sql
SELECT ARRAY(FROM df SELECT x ORDER BY rowid);
```
```
BinderException: Binder Error: Referenced column "rowid" not found in FROM clause!
Candidate bindings: "unnamed_subquery.x"
```

Despite both queries within the `ARRAY` operator working on their own:

```sql
FROM df SELECT x ORDER BY rowid;
```
```
┌────────────────────────┐
│           x            │
│         int64          │
├────────────────────────┤
│                      0 │
│                      1 │
│                      2 │
│                      3 │
│                      4 │
│                      5 │
│                      6 │
│                      7 │
│                      8 │
│                      9 │
│                      · │
│                      · │
```

and 

```sql
FROM df SELECT x ORDER BY row_number() OVER ()
```
```
┌────────────────────────┐
│           x            │
│         int64          │
├────────────────────────┤
│                      0 │
│                      1 │
│                      2 │
│                      3 │
│                      4 │
│                      5 │
│                      6 │
│                      7 │
│                      8 │
│                      9 │
│                      · │
```


PS:

It is possible to workaround all of this via

```sql
SELECT ARRAY(SELECT x FROM (SELECT x FROM df ORDER BY row_number() OVER ()));
```

but I don't understand why (if `ARRAY` generally preserved order, I wouldn't have this problem to begin with, so why does it preserve the order from within a nested subquery?)
and confusingly it's not possible to use the seemingly equivalent


```sql
SELECT ARRAY(SELECT x FROM (SELECT x FROM df ORDER BY rowid));
```

I've opened a separate issue about this (#15052).


### OS:

Linux

### DuckDB Version:

1.1.0

### DuckDB Client:

Python

### Hardware:

i5, x64

### Full Name:

Soeren Wolfers

### Affiliation:

G-Research

### What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a stable release

### Did you include all relevant data sets for reproducing the issue?

Not applicable - the reproduction does not require a data set

### Did you include all code required to reproduce the issue?

- [X] Yes, I have

### Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

- [X] Yes, I have


Discussion / hints:

Fix patch:
diff --git a/src/parser/transform/expression/transform_subquery.cpp b/src/parser/transform/expression/transform_subquery.cpp
index 9d93b54851ee..194ee5e1d4e8 100644
--- a/src/parser/transform/expression/transform_subquery.cpp
+++ b/src/parser/transform/expression/transform_subquery.cpp
@@ -74,16 +74,30 @@ unique_ptr<ParsedExpression> Transformer::TransformSubquery(duckdb_libpgquery::P
 	case duckdb_libpgquery::PG_ARRAY_SUBLINK: {
 		// ARRAY expression
 		// wrap subquery into
-		// "SELECT CASE WHEN ARRAY_AGG(COLUMNS(*)) IS NULL THEN [] ELSE ARRAY_AGG(COLUMNS(*)) END FROM (...) tbl"
+		// "SELECT CASE WHEN ARRAY_AGG(col) IS NULL THEN [] ELSE ARRAY_AGG(col) END FROM (...) tbl"
 		auto select_node = make_uniq<SelectNode>();
 
-		// COLUMNS(*)
-		auto columns_star = make_uniq<StarExpression>();
-		columns_star->columns = true;
+		unique_ptr<ParsedExpression> array_agg_child;
+		optional_ptr<SelectNode> sub_select;
+		if (subquery_expr->subquery->node->type == QueryNodeType::SELECT_NODE) {
+			// easy case - subquery is a SELECT
+			sub_select = subquery_expr->subquery->node->Cast<SelectNode>();
+			if (sub_select->select_list.size() != 1) {
+				throw BinderException(*subquery_expr, "Subquery returns %zu columns - expected 1",
+				                      sub_select->select_list.size());
+			}
+			array_agg_child = make_uniq<PositionalReferenceExpression>(1ULL);
+		} else {
+			// subquery is not a SELECT but a UNION or CTE
+			// we can still support this but it is more challenging since we can't push columns for the ORDER BY
+			auto columns_star = make_uniq<StarExpression>();
+			columns_star->columns = true;
+			array_agg_child = std::move(columns_star);
+		}
 
 		// ARRAY_AGG(COLUMNS(*))
 		vector<unique_ptr<ParsedExpression>> children;
-		children.push_back(std::move(columns_star));
+		children.push_back(std::move(array_agg_child));
 		auto aggr = make_uniq<FunctionExpression>("array_agg", std::move(children));
 		// push ORDER BY modifiers into the array_agg
 		for (auto &modifier : subquery_expr->subquery->node->modifiers) {
@@ -104,7 +118,12 @@ unique_ptr<ParsedExpression> Transformer::TransformSubquery(duckdb_libpgquery::P
 						idx_t positional_index = order_index < 0 ? NumericLimits<idx_t>::Maximum() : idx_t(order_index);
 						order.expression = make_uniq<PositionalReferenceExpression>(positional_index);
 					}
+				} else if (sub_select) {
+					// if we have a SELECT we can push the ORDER BY clause into the SELECT list and reference it
+					sub_select->select_list.push_back(std::move(order.expression));
+					order.expression = make_uniq<PositionalReferenceExpression>(sub_select->select_list.size() - 1);
 				} else {
+					// otherwise we remove order qualifications
 					RemoveOrderQualificationRecursive(order.expression);
 				}
 			}


Test patch:
diff --git a/test/sql/subquery/scalar/array_order_subquery.test b/test/sql/subquery/scalar/array_order_subquery.test
index 6e1f36f7e086..a0ca2fb4c7d0 100644
--- a/test/sql/subquery/scalar/array_order_subquery.test
+++ b/test/sql/subquery/scalar/array_order_subquery.test
@@ -36,6 +36,18 @@ select array(select unnest(l) AS i order by i desc nulls first) as a from (value
 []
 [12, 11, 10]
 
+# row id
+query I
+SELECT ARRAY(SELECT i FROM t ORDER BY rowid DESC)
+----
+[4, 4, 3, 2, 1]
+
+# qualified name
+query I
+SELECT ARRAY(SELECT i FROM t ORDER BY t.rowid)
+----
+[1, 2, 3, 4, 4]
+
 # use integer literals
 query I
 SELECT ARRAY