Re:PRQL a simple, powerful, pipelined SQL replacement
by taffer on Friday December 30, 2022 @06:37AM
(#34184122)
This is on purpose. Forcing the propagation of row order from subqueries would make many or even most optimizations impossible.
Re:PRQL a simple, powerful, pipelined SQL replacement
by tremon on Friday December 30, 2022 @01:47PM
(#34188390)
I'm not sure I'd call that a footgun -- it's a fundamental property of relational algebra that relations are unordered.
That said, it's not universally true that subquery ordering is not propagated to the final result; in the absence of an ORDER BY clause, the query engine will return the results in whatever order is most convenient for execution. For example, this query:
select *, rank() over (partition by lastname order by firstname)
from employees;
will definitely return a result set ordered by lastname,firstname even though it's not explicitly specified (tested on MS' azure sql database).
The main issue I encountered while playing with it is that the ordering semantics of transforms are unclear to me. This is a notorious foot gun in SQL which does not propagate row order from sub queries or CTEs into parent queries unless those sort conditions are restated. Hopefully prql can come up with a better answer to this problem: https://github.com/PRQL/prql/issues/1363