Support nested `ORDER BY`

[joe-mann]

What would you like to be added or enhanced

To support this syntax:

SELECT * FROM (SELECT * FROM table_name ORDER BY column_name ASC|DESC ...) ORDER BY column_name ASC|DESC

Currently this results in an error: rpc error: code = Unknown desc = order is limit to one indexed column.

Whereas a simple ORDER BY works fine:

SELECT * FROM table_name ORDER BY column_name ASC|DESC ...

Why is this needed

Reordering results before they're returned removes the need to perform this action on the client side.

[byo]

Currently the ORDER BY clause can only be applied to columns that are indexed - which is the case for the inner SELECT statement.

The ORDER BY of the outer statement works on a temporary result returned from the inner one - in fact the bond between two queries is done with a streaming approach (like processing one inner row at a time to generate at most one outer row). To order the outer query result we should either use temporary tables (which immudb does not support now) or apply same ordering for the outer and inner query.

If the ordering criteria is same for both inner and outer queries, the outer query will already produce ordered results - it can only process rows from the inner query in the same order as the inner query produced them. Thus in such case the ORDER BY for the outer query is not necessary.

We could add a check if the ordering criteria for the outer and inner queries match - will that be sufficient enough? If so, skipping the ORDER BY for the outer query would be a good workaround until such matcher is implemented.