I stumbled upon this question on a Twitter conversation with Lukas Eder.
Although the correct behavior would be to apply the ORDER BY clause on the outermost query, because, here, we are not using DISTINCT, GROUP BY, JOIN or any other WHERE clause in the outermost query, why wouldn't a RDBMS just pass the incoming data as it was sorted by the inner query?
SELECT * FROM ( SELECT * FROM table ORDER BY time DESC ) AS t
When running this example on PostgreSQL, at least, you get the same Execution Plan for both the inner query and this derived table example, as well as the same result set.
So, I would assume that the Planner will simply discard the outermost query because it's redundant or simply pass through the results from the inner table.
Does anyone think this might not be the case?