jonahgao commented on code in PR #15300: URL: https://github.com/apache/datafusion/pull/15300#discussion_r2002876157
########## datafusion/sqllogictest/test_files/union.slt: ########## @@ -907,11 +907,56 @@ SELECT * FROM (SELECT y FROM u1 UNION ALL SELECT y FROM u2) ORDER BY y; 20 40 +query TT +explain SELECT * FROM (SELECT y FROM u1 UNION ALL SELECT y FROM u2) ORDER BY y; +---- +logical_plan +01)Sort: y ASC NULLS LAST +02)--Union +03)----Projection: CAST(u1.y AS Int64) AS y +04)------TableScan: u1 projection=[y] +05)----TableScan: u2 projection=[y] +physical_plan +01)SortPreservingMergeExec: [y@0 ASC NULLS LAST] +02)--UnionExec +03)----SortExec: expr=[y@0 ASC NULLS LAST], preserve_partitioning=[true] +04)------ProjectionExec: expr=[CAST(y@0 AS Int64) as y] +05)--------RepartitionExec: partitioning=RoundRobinBatch(4), input_partitions=1 +06)----------DataSourceExec: partitions=1, partition_sizes=[1] +07)----SortExec: expr=[y@0 ASC NULLS LAST], preserve_partitioning=[false] +08)------DataSourceExec: partitions=1, partition_sizes=[1] + +# optimize_subquery_sort in create_relation removes Sort so the result is not sorted. query I SELECT * FROM v1; Review Comment: I think either is fine. A view is a type of relation, and a relation is unordered. In the SQL Server [doc](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-view-transact-sql?view=sql-server-ver16#as), it states > The ORDER BY clause does not guarantee ordered results when the view is queried, unless ORDER BY is also specified in the query itself. Postgres also does not drop the Sort operation in subqueries but optimize_subquery_sort does. ``` psql=> explain select * from (select from u1 order by x); QUERY PLAN ---------------------------------------------------------------------------- Subquery Scan on unnamed_subquery (cost=158.51..186.76 rows=2260 width=0) -> Sort (cost=158.51..164.16 rows=2260 width=4) Sort Key: u1.x -> Seq Scan on u1 (cost=0.00..32.60 rows=2260 width=4) (4 rows) ``` -- 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: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
