ArrayIndexOutOfBoundsException on ASOF JOIN

[puzpuzpuz]

To reproduce

DDL (other tables are similar):

CREATE TABLE 'inverter_1' (
  timestamp TIMESTAMP,
  current_a LONG
) timestamp (timestamp) PARTITION BY DAY WAL;

Problematic query:

SELECT inverter_1.timestamp,
    inverter_1.current_a AS current_a_1
FROM inverter_1 ASOF JOIN inverter_2 
    ASOF JOIN inverter_3 ASOF JOIN inverter_10
    ASOF JOIN inverter_11 ASOF JOIN inverter_12
    ASOF JOIN inverter_13 ASOF JOIN inverter_14
    ASOF JOIN inverter_15
WHERE inverter_1.timestamp BETWEEN '2023-01-05 20:00' AND '2023-01-05 23:55'
ORDER BY timestamp DESC

Run the above query and see the error:

2024-01-16T17:17:03.634868Z C i.q.c.h.p.JsonQueryProcessorState [128] internal error [ex=
java.lang.AssertionError
	at io.questdb.griffin.SqlCodeGenerator.generateTableQuery0(SqlCodeGenerator.java:4776)
	at io.questdb.griffin.SqlCodeGenerator.generateTableQuery(SqlCodeGenerator.java:4314)
	at io.questdb.griffin.SqlCodeGenerator.generateNoSelect(SqlCodeGenerator.java:2310)
	at io.questdb.griffin.SqlCodeGenerator.generateSelect(SqlCodeGenerator.java:2902)
	at io.questdb.griffin.SqlCodeGenerator.generateQuery0(SqlCodeGenerator.java:2465)
	at io.questdb.griffin.SqlCodeGenerator.generateQuery(SqlCodeGenerator.java:2453)
	at io.questdb.griffin.SqlCodeGenerator.generateJoins(SqlCodeGenerator.java:1643)
	at io.questdb.griffin.SqlCodeGenerator.generateSelect(SqlCodeGenerator.java:2900)
	at io.questdb.griffin.SqlCodeGenerator.generateQuery0(SqlCodeGenerator.java:2465)
	at io.questdb.griffin.SqlCodeGenerator.generateQuery(SqlCodeGenerator.java:2453)
	at io.questdb.griffin.SqlCodeGenerator.generateSubQuery(SqlCodeGenerator.java:4283)
	at io.questdb.griffin.SqlCodeGenerator.generateSelectChoose(SqlCodeGenerator.java:2915)
	at io.questdb.griffin.SqlCodeGenerator.generateSelect(SqlCodeGenerator.java:2885)
	at io.questdb.griffin.SqlCodeGenerator.generateQuery0(SqlCodeGenerator.java:2465)
	at io.questdb.griffin.SqlCodeGenerator.generateQuery(SqlCodeGenerator.java:2453)
	at io.questdb.griffin.SqlCodeGenerator.generate(SqlCodeGenerator.java:203)
	at io.questdb.griffin.SqlCompilerImpl.generateFactory(SqlCompilerImpl.java:2841)
	at io.questdb.griffin.SqlCompilerImpl.generateWithRetries(SqlCompilerImpl.java:2858)
	at io.questdb.griffin.SqlCompilerImpl.compileUsingModel(SqlCompilerImpl.java:1470)
	at io.questdb.griffin.SqlCompilerImpl.compileInner(SqlCompilerImpl.java:1432)
	at io.questdb.griffin.SqlCompilerImpl.compile(SqlCompilerImpl.java:234)
	at io.questdb.cairo.pool.SqlCompilerPool$C.compile(SqlCompilerPool.java:104)
	at io.questdb.cutlass.http.processors.JsonQueryProcessor.compileAndExecuteQuery(JsonQueryProcessor.java:436)
	at io.questdb.cutlass.http.processors.JsonQueryProcessor.execute0(JsonQueryProcessor.java:189)
	at io.questdb.cutlass.http.processors.JsonQueryProcessor.onRequestComplete(JsonQueryProcessor.java:247)
	at io.questdb.cutlass.http.HttpConnectionContext.handleClientRecv(HttpConnectionContext.java:869)
	at io.questdb.cutlass.http.HttpConnectionContext.handleClientOperation(HttpConnectionContext.java:253)
	at io.questdb.cutlass.http.HttpServer$1.lambda$$0(HttpServer.java:81)
	at io.questdb.network.AbstractIODispatcher.processIOQueue(AbstractIODispatcher.java:199)
	at io.questdb.cutlass.http.HttpServer$1.run(HttpServer.java:85)
	at io.questdb.mp.Worker.run(Worker.java:148)
, q=`SELECT inverter_1.timestamp,
    inverter_1.current_a AS current_a_1
FROM inverter_1 ASOF JOIN inverter_2 
    ASOF JOIN inverter_3 ASOF JOIN inverter_10
    ASOF JOIN inverter_11 ASOF JOIN inverter_12
    ASOF JOIN inverter_13 ASOF JOIN inverter_14
    ASOF JOIN inverter_15
WHERE inverter_1.timestamp BETWEEN '2023-01-05 20:00' AND '2023-01-05 23:55'
ORDER BY timestamp DESC`]

QuestDB version:

7.3.9

OS, in case of Docker specify Docker and the Host OS:

Linux

File System, in case of Docker specify Host File System:

ext4

Full Name:

Andrei Pechkurov

Affiliation:

QuestDB

Have you followed Linux, MacOs kernel configuration steps to increase Maximum open files and Maximum virtual memory areas limit?

• [X] Yes, I have

Additional context

No response

[nwoolmer]

With current master, the current error is now:

2024-05-28T09:34:08.456372Z E i.q.g.e.QueryProgress err [id=-1, sql=`SELECT inverter_1.timestamp,
    inverter_1.current_a AS current_a_1
FROM inverter_1 ASOF JOIN inverter_2 
    ASOF JOIN inverter_3 ASOF JOIN inverter_10
    ASOF JOIN inverter_11 ASOF JOIN inverter_12
    ASOF JOIN inverter_13 ASOF JOIN inverter_14
    ASOF JOIN inverter_15
WHERE inverter_1.timestamp BETWEEN '2023-01-05 20:00' AND '2023-01-05 23:55'
ORDER BY timestamp DESC`, principal=admin, cache=false, time=4580959, msg=[85] left side of time series join doesn't have ASC timestamp order, errno=0, pos=85]

Probably related to updates pushing down order by advice.

[puzpuzpuz]

Probably related to updates pushing down order by advice.

Yes, most likely we need to make the order by pushdown more strict, so that it doesn't mess up ASOF joins.