alamb commented on code in PR #15131:
URL: https://github.com/apache/datafusion/pull/15131#discussion_r1987868432
##########
datafusion/sqllogictest/test_files/order.slt:
##########
@@ -986,17 +986,26 @@ statement ok
create table t(a0 int, a int, b int, c int) as values (1, 2, 3, 4), (5, 6, 7,
8);
# expect this query to run successfully, not error
+query IIII
+select * from (select c, a, NULL::int as a0, b from t order by a, c) t1
+union all
+select * from (select c, NULL::int as a, a0, b from t order by a0, c) t2
+order by c, a, a0, b
+limit 2;
+----
+4 2 NULL 3
+4 NULL 1 3
+
query III
select * from (select c, a, NULL::int as a0 from t order by a, c) t1
union all
select * from (select c, NULL::int as a, a0 from t order by a0, c) t2
-order by c, a, a0, b
+order by c, a, a0
Review Comment:
This query is not valid as `b` does not appear in the subquery select lists
Postgres agrees
```sql
postgres=# create table t(a0 int, a int, b int, c int);
CREATE TABLE
postgres=# insert into t values (1, 2, 3, 4);
INSERT 0 1
postgres=# insert into t values (5, 6, 7, 8);
INSERT 0 1
postgres=# select * from (select c, a, NULL::int as a0 from t order by a, c)
t1
union all
select * from (select c, NULL::int as a, a0 from t order by a0, c) t2
order by c, a, a0, b
limit 2;
ERROR: column "b" does not exist
LINE 4: order by c, a, a0, b
^
```
The `b` needs to be in the subquery select list:
```sql
postgres=# select * from (select c, a, NULL::int as a0, b from t order by a,
c) t1
union all
select * from (select c, NULL::int as a, a0, b from t order by a0, c) t2
order by c, a, a0, b
limit 2;
c | a | a0 | b
---+---+----+---
4 | 2 | | 3
4 | | 1 | 3
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
