Apache Pinot

Hello :slightly_smiling_face:
Are subqueries supported in Pinot ?

Such as
1.  `SELECT * FROM table WHERE x in (SELECT ...)` 
and
 2.    `SELECT * FROM (SELECT ...)`  (i.e. working with the output of a subquery)

My use case in particular right now is 2.

Like:

`SELECT SUM(agg_value) FROM (SELECT dateString, AVG(value) as agg_value FROM table GROUP BY dateString)`

Or maybe there's another way to express this query ?

The data looks like this

```date    | 01 | 02 | 03
entity1 | 1  |  2 |  3 
entity2 | 3  |  4 |  5```
The idea is to AVG over entity1 and entity2 per day, then SUM those results over all days

Pinot does not support generic nested queries. There's some limited use case like: <https://docs.pinot.apache.org/basics/indexing/json-index#nested-filter-expression>

<@UDT7GFEG6> is there way to model this query using post-aggregation?

I've tried to find some info on post-aggregation (I've found a PR about this), but couldn't find much ^^
Would really appreciate <@UDT7GFEG6> input then :smile:

Unfortunately this cannot be achieved with the post-aggregation

<@UDQU92KBK> <@UDT7GFEG6> Is there another approach to solving that query ?
Ideally as much as possible in realtime (i.e. no precomputation), within Pinot

In the subquery, we need to return the results for all groups

Yes
Can this subquery result be "exploited" further in any way ?
Without resorting to client level aggregations ?

As a workaround, you may issue `SELECT AVG(value) FROM table GROUP BY dateString LIMIT 10000` (assuming the cardinality of `dateString` is smaller than 10000), then do the sum on the client side

Yes, luckily dateString cardinality is low (&lt;1000)
I'll see how performance fares in that case then

Do you think any upcoming feature would allow this query to be done in Pinot ?

We don't have a feature already planned to support this

One option is to use Pinot + Presto if you have nested queries like this.

We can probably build a simple query engine on the broker to support simple subquery like this

Yes, using Presto might be an available option supported now

&gt; One option is to use Pinot + Presto if you have nested queries like this.
Sounds a bit overkill for that use case as it's not currently available in my environment but thanks for the idea :slightly_smiling_face:

<@UDT7GFEG6> I see, maybe some day then ! :smile:

Can you please file an issue about this feature so that we keep track of it

Thank you very much for the feedback <@UDQU92KBK> &amp; <@UDT7GFEG6>

<https://github.com/apache/incubator-pinot/issues/7043>
I'm having a bit of trouble finding the appropriate terms to describe the feature request, feel free to edit &amp; add anything :slightly_smiling_face: