5 ms·
Having written lots of advanced SQL (tons of CTEs, window functions, user defined aggregates, json manipulation, etc.) in my previous job, this looks really pro
by felixge 4y ago
Having written lots of advanced SQL (tons of CTEs, window functions, user defined aggregates, json manipulation, etc.) in my previous job, this looks really promising.
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 https://github.com/PRQL/prql/issues/1363
- taffer 4y agoThis is on purpose. Forcing the propagation of row order from subqueries would make many or even most optimizations impossible.
- tremon 4y agoI'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).