You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
A subquery is a derived table/relation. A relation is unordered, and you can't rely on its tuples being ordered; different database implementations or queries have different behaviors. In #12003, DataFusion intentionally optimized away unnecessary ordering in subqueries.
@jonahgao is correct -- this is not a bug. If the top-level query doesn't specify an ordering, the engine is free to optimize the subquery ordering away.
@TheBuilderJR thanks for opening the issue and please let us know if anything still needed. as @ozankabak correctly mentioned the if there is no top level ordering the engine cannot guarantee the final order.
The LIMIT clause gets evaluated after the ORDER clause, so if you rewrite your query
SELECT * FROM (
SELECT
session.access_token,
COUNT(1) as c
FROM quizchat_chats
GROUP BY session.access_token
) AS query ORDER BY c LIMIT 10000
Activity
2010YOUY01 commentedon Dec 26, 2024
I think SQL standard doesn't enforce such ordering, but it's possible most engines implemented it as ordered
jonahgao commentedon Dec 26, 2024
A subquery is a derived table/relation. A relation is unordered, and you can't rely on its tuples being ordered; different database implementations or queries have different behaviors. In #12003, DataFusion intentionally optimized away unnecessary ordering in subqueries.
Some discussions of other databases:
ozankabak commentedon Dec 27, 2024
@jonahgao is correct -- this is not a bug. If the top-level query doesn't specify an ordering, the engine is free to optimize the subquery ordering away.
comphead commentedon Dec 28, 2024
@TheBuilderJR thanks for opening the issue and please let us know if anything still needed. as @ozankabak correctly mentioned the if there is no top level ordering the engine cannot guarantee the final order.
The LIMIT clause gets evaluated after the ORDER clause, so if you rewrite your query
it should work