Aliases break on array(subquery) order by clauses in 0.10.2

[exaroj]

What happens?

Starting in 0.10.2 when a table alias is used in a subquery ORDER BY the resolution fails with duckdb.duckdb.BinderException: Binder Error: Referenced table not found!

WITH 
                input1 AS (
                    SELECT UNNEST([4, 3, 2, 1]) AS a
                ),
                input2 AS (
                    SELECT UNNEST([10, 20]) AS b
                ),
                proc AS (
                    SELECT
                        b,
                        ARRAY(
			    SELECT a
                            FROM input1 AS X
                            WHERE X.a < 100
                            ORDER BY X.a) AS agg
                    FROM input2
                )
                
                SELECT * FROM proc;

(guessing)
#11293
#11316 for ARRAY([subquery]) explicitly push the ORDER BY of the underlying subquery into the array aggregate

To Reproduce

import duckdb

def test() -> None:
    c : duckdb.DuckDBPyConnection
    with duckdb.connect() as c:
        print(c.execute("SELECT * FROM pragma_version()").fetchall())
        print(c.execute('''
              WITH 
                input1 AS (
                    SELECT UNNEST([4, 3, 2, 1]) AS a
                ),
                input2 AS (
                    SELECT UNNEST([10, 20]) AS b
                ),
                proc AS (
                    SELECT
                        b,
                        ARRAY(
			    SELECT a
                            FROM input1 AS X
                            WHERE X.a < 100
                            ORDER BY X.a) AS agg
                    FROM input2
                )
                
                SELECT * FROM proc;
              ''').fetchall())
        
        
test()

%

[('v0.10.1', '4a89d97db8')]
[(10, [1, 2, 3, 4]), (20, [1, 2, 3, 4])]

%

[('v0.10.2', '1601d94f94')]
Traceback (most recent call last):
  File "/tmp/x.py", line 30, in <module>
    test()
  File "/tmp/x.py", line 5, in test
    with duckdb.connect() as c:
  File "/tmp/x.py", line 7, in test
    print(c.execute('''
          ^^^^^^^^^^^^^
duckdb.duckdb.BinderException: Binder Error: Referenced table "X" not found!
Candidate tables: "input2"
LINE 16:                             ORDER BY X.a) AS agg

%

[('v0.10.3-dev137', '8c06530fb2')]
Traceback (most recent call last):
  File "/tmp/x.py", line 30, in <module>
    test()
  File "/tmp/x.py", line 5, in test
    with duckdb.connect() as c:
  File "/tmp/x.py", line 7, in test
    print(c.execute('''
          ^^^^^^^^^^^^^
duckdb.duckdb.BinderException: Binder Error: Referenced table "X" not found!
Candidate tables: "input2"
LINE 16:                             ORDER BY X.a) AS agg

OS:

OS

DuckDB Version:

0.10.2

DuckDB Client:

Python

Full Name:

Rob Jackson

Affiliation:

exaforce.com

What is the latest build you tested with? If possible, we recommend testing with the latest nightly build.

I have tested with a source build

Did you include all relevant data sets for reproducing the issue?

Yes

Did you include all code required to reproduce the issue?

• Yes, I have

Did you include all relevant configuration (e.g., CPU architecture, Python version, Linux distribution) to reproduce the issue?

• Yes, I have

[Mytherin]

Fixed in #12090