ozankabak commented on issue #15886: URL: https://github.com/apache/datafusion/issues/15886#issuecomment-2837673359
I remember that we discussed this before and the conclusion that this is actually valid behavior. Quoting from Wikipedia: > ORDER BY is the only way to sort the rows in the result set. Without this clause, the relational [database system](https://en.wikipedia.org/wiki/Database_system) may return the rows in any order. If an ordering is required, the ORDER BY must be provided in the SELECT statement sent by the application. Although some database systems allow the specification of an ORDER BY clause in subqueries or [view](https://en.wikipedia.org/wiki/View_(database)) definitions, the presence there has no effect on the final result-set order, but makes sense when combined with a result offset clause or a fetch first clause. A view is a logical relational table, and the [relational model](https://en.wikipedia.org/wiki/Relational_model) mandates that a table is a set of rows, implying no sort order whatsoever. IIRC the SQL standard defines things way. Granted, a lot of people do not like this behavior and find it surprising, but it is not wrong AFAICT -- and a lot of DBs behave this way. Personally, I think the behavior is *not* surprising in @comphead's example, but it is somewhat surprising in @maxburke's example. For users that don't like the standard behavior, maybe we can add a configuration flag to DF that they can switch, which would make the `optimize_subquery_sort` function respect subqueries. -- This is an automated message from the Apache Git Service. To respond to the message, please log on to GitHub and use the URL above to go to the specific comment. To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For queries about this service, please contact Infrastructure at: us...@infra.apache.org --------------------------------------------------------------------- To unsubscribe, e-mail: github-unsubscr...@datafusion.apache.org For additional commands, e-mail: github-h...@datafusion.apache.org
