Has pinot supported subquery?

[shuitai]

I find the doc link https://docs.pinot.apache.org/users/user-guide-query/gap-fill-functions
In section: Aggregation/Gapfill/Aggregation

The query supports subquery or nested sql, which means pinot support nested sql now?

SELECT time_col, SUM(status) AS occupied_slots_count
FROM (
SELECT GAPFILL(time_col,'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','2021-10-01 09:00:00.000',
'2021-10-01 12:00:00.000','30:MINUTES', FILL(status, 'FILL_PREVIOUS_VALUE'),
TIMESERIESON(lotId)), lotId, status
FROM (
SELECT DATETIMECONVERT(event_time,'1:MILLISECONDS:EPOCH',
'1:MILLISECONDS:SIMPLE_DATE_FORMAT:yyyy-MM-dd HH:mm:ss.SSS','30:MINUTES') AS time_col,
lotId, lastWithTime(is_occupied, event_time, 'INT') AS status
FROM parking_data
WHERE event_time >= 1633078800000 AND event_time <= 1633089600000
GROUP BY 1, 2
ORDER BY 1
LIMIT 100)
LIMIT 100)
GROUP BY 1
LIMIT 100

[Jackie-Jiang]

Currently that is supported only within GAP_FILL. We are evaluating the correctness and overhead of enabling it for general queries. If it works well, we can enable it for all queries.

[Jackie-Jiang]

Same topic as #7043

[shuitai]

@Jackie-Jiang Thanks for your reply, the topic In #7043 is about two years ago, so what about the release plan? We are comparing druid and pinot, druid could support subquery, but pinot has some features which druid doesn't have, so if pinot could support subquery, we will prefer to pinot.

[Jackie-Jiang]

The new multi-stage query engine (with join support) should support sub-query (cc @walterddr to confirm). One concern of directly supporting sub-query in the single-stage query engine is that the outer query is executed on the single broker, which might potentially consume too much resource.
If this is a deal breaker and multi-stage engine doesn't work, we can consider prioritizing this feature request.

[walterddr]

sub-query is supported. the problem is this particular sub-query is not supported :-) reason is exactly in GAP-FILL

[shuitai]

@walterddr @Jackie-Jiang if sub-query is supported or not support fully, could you please provide detail examples in document?

[donPain]

👀

[Jackie-Jiang]

General subquery is supported in multi-stage engine, but not in single-stage engine.

Gap-fill is a special syntactic sugar which uses subquery syntax and only works in single-stage engine.