Skip to content

SELECT * FROM subquery ignores ordering #13904

Closed

Description

@TheBuilderJR

Describe the bug

Generally for almost all other sql query engines I expect

SELECT * FROM (
  SOME_ORDERED_SUB_QUERY_HERE
) AS query LIMIT 10000

to maintain the same order as the subquery, eg.

SELECT * FROM (
SELECT
  session.access_token,
  COUNT(1) as c
FROM quizchat_chats
GROUP BY session.access_token
ORDER BY c
) AS query LIMIT 10000

But in datafusion, it doesn't! It seemingly returns a randomly ordered list.

To Reproduce

Run a SELECT * FROM over an ordered subquery

Expected behavior

ordering is preserved

Additional context

No response

Activity

  1. 2010YOUY01 commented on Dec 26, 2024

    @2010YOUY01
    Member

    I think SQL standard doesn't enforce such ordering, but it's possible most engines implemented it as ordered

  2. jonahgao commented on Dec 26, 2024

    @jonahgao
    Member

    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:

  3. ozankabak commented on Dec 27, 2024

    @ozankabak
    Contributor

    @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.

  4. comphead commented on Dec 28, 2024

    @comphead
    Contributor

    @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
    

    it should work

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Labels

    bugSomething isn't working

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions