FILTER Clause Support

[atris]

FILTER clause (SELECT COUNT(*) FILTER (WHERE foo > bar) FROM fooBar) allows complex reports to be generated efficiently without recomputation of the core predicate and rescans of the original data set.

https://docs.google.com/document/d/1qJzG1CmyVZpLN6rdI8V0zUWEaIjQ86hDL9XwrcPKcTs/edit?usp=sharing

[siddharthteotia]

Thanks @atris . Will review the doc

[xiangfu0]

@yupeng9

[Jackie-Jiang]

Reference to the query syntax: https://modern-sql.com/feature/filter
Postgres supports it: https://medium.com/little-programming-joys/the-filter-clause-in-postgres-9-4-3dd327d3c852

[Jackie-Jiang]

The aggregation only support is merged #7916

We should support group-by as the next step

[egalpin]

Is there a known path/existing plan for how to implement group-by support? Seems interesting to work on if it were Pinot-dev-beginner friendly

[atris]

I am currently working on a PR for the same (Side note - it is not a beginner friendly PR)

…

On Sat, 21 May 2022, 05:10 Evan Galpin, ***@***.***> wrote: Is there a known path/existing plan for how to implement group-by support? Seems interesting to work on if it were Pinot-dev-beginner friendly — Reply to this email directly, view it on GitHub <#7519 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/AANE5Y47N6DXIZXBV4VMR73VLAPH3ANCNFSM5FLB2EPA> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[egalpin]

@atris Any update on the progress of a PR to support GROUP BY? Would be a very exciting feature to see 😊

[egalpin]

I wanted to mention that I'm currently exploring/investigating GROUP BY support with FILTER expressions. As best as I can tell so far, GroupByPlanNode will need to have similar modifications as those made to AggregationPlanNode in @atris original PR (plus all implicated changes). I realize it's been advised that this is not a beginner-friendly PR but support for FILTER + GROUP BY will be high impact for my use of Pinot, so it's worth a shot.

I welcome any collaboration!