News for hackers. Stuff that matters.
Re:PRQL a simple, powerful, pipelined SQL replacement by felixge on Friday December 30, 2022 @05:58AM (#34183901)
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

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).
Re:PRQL a simple, powerful, pipelined SQL replacement by VelJun on Friday December 30, 2022 @03:28PM (#34189682)
With a large enough employees table, Azure SQL could parallelize the scan operation, in which case the ordering would not necessarily be maintained.
Re:PRQL a simple, powerful, pipelined SQL replacement by fuy on Saturday December 31, 2022 @02:58AM (#34194543)
without ORDER BY in the outermost query the ordering is definitely not guaranteed and may be influenced by optimizations