left side of time series join doesn't have ASC timestamp order

Describe the bug

When I try to ORDER BY on a table in an ASOF JOIN together with an IN-clause with multiple arguments, I receive the following error: "left side of time series join doesn't have ASC timestamp order".

When I rewrite the IN-clause into multiple OR clauses the query works as expected:

SELECT *
FROM outer_table
ASOF JOIN inner_table ON (symbol)
WHERE (outer_table.symbol = 'symbol' OR outer_table.symbol = 'other')
ORDER BY outer_table.time ASC
LIMIT 10;

The query also works when I remove the ORDER BY clause:

SELECT *
FROM outer_table
ASOF JOIN inner_table ON (symbol)
WHERE (outer_table.symbol IN ('symbol', 'other'))
--ORDER BY outer_table.time ASC
LIMIT 10;

The query also works when I only use a single argument for the IN-clause:

SELECT *
FROM outer_table
ASOF JOIN inner_table ON (symbol)
WHERE (outer_table.symbol IN ('symbol'))
ORDER BY outer_table.time ASC
LIMIT 10;

I am trying to enforce authorization filters on cubes using Cube.js, so I do not have much control over the semantics of the query.

This is a real show-stopper for us and would mean we have to write our own QuestDB provider in Cube.js that translates the query to multiple OR-conditions or force us to move away from QuestDB.

To reproduce

-- create tables
CREATE TABLE 'outer_table' (
time TIMESTAMP,
symbol SYMBOL capacity 256 CACHE index capacity 128,
value FLOAT
) timestamp (time) PARTITION BY DAY WAL;

CREATE TABLE 'inner_table' (
time TIMESTAMP,
symbol SYMBOL capacity 256 CACHE index capacity 128,
value FLOAT
) timestamp (time) PARTITION BY DAY WAL;

-- seed data
INSERT INTO outer_table(time, symbol, value)
SELECT timestamp_sequence(
to_timestamp('2023-01-01T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
1000000L * 60L * 60L),
case when x % 2 = 0 then 'symbol' else 'other' end, x
FROM long_sequence(10);

INSERT INTO inner_table(time, symbol, value)
SELECT timestamp_sequence(
to_timestamp('2023-01-01T00:00:00', 'yyyy-MM-ddTHH:mm:ss'),
1000000L * 60L * 60L),
case when x % 2 = 0 then 'symbol' else 'other' end, x
FROM long_sequence(10);

-- query
SELECT *
FROM outer_table
ASOF JOIN inner_table ON (symbol)
WHERE (outer_table.symbol IN ('symbol', 'other'))
ORDER BY outer_table.time ASC
LIMIT 10;

Expected Behavior

I would expect the ASOF JOIN to function as expected and filter the results based on the provided set of symbol values.

Environment

- **QuestDB version**: 7.2
- **OS**: Ubuntu 20.04 in WSL2 on Windows 10
- **Browser**: Chrome

I use docker compose with image: questdb/questdb:7.2

Additional context

bis-questdb | 2023-06-20T09:56:40.227887Z I http-server connected [ip=172.18.0.1, fd=147]
bis-questdb | 2023-06-20T09:56:40.230696Z I i.q.c.h.p.JsonQueryProcessorState [147] exec [q='SELECT *
bis-questdb | FROM outer_table
bis-questdb | ASOF JOIN inner_table ON (symbol)
bis-questdb | WHERE (outer_table.symbol IN ('symbol', 'other'))
bis-questdb | ORDER BY outer_table.time ASC
bis-questdb | LIMIT 10;']
bis-questdb | 2023-06-20T09:56:40.230846Z I i.q.c.h.p.QueryCache miss [thread=questdb-shared-2, sql=SELECT *
bis-questdb | FROM outer_table
bis-questdb | ASOF JOIN inner_table ON (symbol)
bis-questdb | WHERE (outer_table.symbol IN ('symbol', 'other'))
bis-questdb | ORDER BY outer_table.time ASC
bis-questdb | LIMIT 10;]
bis-questdb | 2023-06-20T09:56:40.233890Z I i.q.c.p.ReaderPool open 'outer_table55' [at=0:0]
bis-questdb | 2023-06-20T09:56:40.248923Z I i.q.c.p.ReaderPool open 'inner_table56' [at=0:0]
bis-questdb | 2023-06-20T09:56:40.278550Z I i.q.g.SqlCompiler plan [q=select-choose outer_table.time time, outer_table.symbol symbol, outer_table.value value, inner_table.time time1, inner_table.symbol symbol1, inner_table.value value1 from (select [time, symbol, value] from outer_table timestamp (time) asof join select [time, symbol, value] from inner_table timestamp (time) on inner_table.symbol = outer_table.symbol where symbol in ('symbol','other')) order by time limit 10, fd=147]
bis-questdb | 2023-06-20T09:56:40.313169Z I i.q.c.h.p.JsonQueryProcessorState [147] sql error [q=SELECT * bis-questdb | FROM outer_table bis-questdb | ASOF JOIN inner_table ON (symbol) bis-questdb | WHERE (outer_table.symbol IN ('symbol', 'other')) bis-questdb | ORDER BY outer_table.time ASC bis-questdb | LIMIT 10;, at=28, message=left side of time series join doesn't have ASC timestamp order]
bis-questdb | 2023-06-20T09:56:40.314129Z I i.q.c.h.p.JsonQueryProcessor all sent [fd=147, lastRequestBytesSent=472, nCompletedRequests=1, totalBytesSent=0]

Start by running the provided ASOF JOIN reproduction against QuestDB 7.2 and compare the IN-clause query with the equivalent OR query. Inspect the SQL compiler plan and the time-series join ordering check indicated by the error. Done means an ASC ORDER BY on the left side succeeds when the IN clause contains multiple symbols.