Influxdb: Allow different order by time between sub-queries

[gunnaraasen]

__Proposal:__ Allow ORDER BY time to be different between different queries in a subquery. This would be an exception to allow some switching between subquery order by time that was restricted in https://github.com/influxdata/influxdb/pull/8754.

__Current behavior:__ Running the following query gives an error:

# using the following dataset
> select * from test
name: test
time                           prop1 prop2 prop3
----                           ----- ----- -----
2018-08-13T21:58:11.971383045Z aaa   1
2018-08-13T21:58:30.839534576Z aaa   1     100
2018-08-13T21:58:41.811846719Z bbb   1     200
2018-08-13T21:58:52.889919003Z bbb   2     300
> select * from (select * from m order by time desc limit 3)
ERR: subqueries must be ordered in the same direction as the query itself

__Desired behavior:__ I would like to return the data already sorted in ascending order.

> select * from (select * from test order by time desc limit 3) order by time asc
name: test
time                           prop1 prop2 prop3
----                           ----- ----- -----
2018-08-13T21:58:30.839534576Z aaa   1     100
2018-08-13T21:58:41.811846719Z bbb   1     200
2018-08-13T21:58:52.889919003Z bbb   2     300

__Use case:__ The client consuming the response will be unable to reorder the results client-side in-memory so this would eliminate that concern while allowing chunking the result set in the response. The requirement is only to reverse the order of results from the subquery in the outermost query.

[jsternberg]

The main concern I have with this is that the implementation I would choose would be to buffer things in memory. I don't think I can do this using the streaming method that currently exists in the query engine, but I do think this would be really easy if I could buffer all of the results from the subquery or at least write them to disk somewhere. Flux is planned along these lines, but the current query engine doesn't have any of those protections because we've never experimented with them to begin with.

With that, if speed isn't a big deal, I do think we could do this and I don't think it would be very difficult. I give it maybe a week since I already know the initial implementation in my head.

As long as switching the order of the subquery is an explicit operation though. So the default would be to propagate the query ordering inwards.

[gunnaraasen]

The main concern I have with this is that the implementation I would choose would be to buffer things in memory.

What would be the effort to utilize the streaming method? One of the drivers would be the ability to use chunking for large result sets (the client is generally memory-constrained).

As long as switching the order of the subquery is an explicit operation though. So the default would be to propagate the query ordering inwards.

Makes sense.

[jsternberg]

The streaming method likely just wouldn't work at all.

@nathanielc while this is possible with flux, we should add a method in limit to take the last n items instead of requiring the query to be ordered by descending time. If we just had a way to say LIMIT LAST 3 or something like that, we wouldn't need to resort the data (even though resorting the data is possible in flux).

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[stale[bot]]

This issue has been automatically closed because it has not had recent activity. Please reopen if this issue is still important to you. Thank you for your contributions.