[GitHub] [druid] gianm commented on issue #9122: Add SQL GROUPING SETS support.

[GitBox <gi...@apache.org>]

gianm commented on issue #9122: Add SQL GROUPING SETS support.
URL: https://github.com/apache/druid/pull/9122#issuecomment-590652648
 
 
   > If a limitSpec (containing sort spec) is applied in the end then that will mixup the rows and it would be difficult to identify which rows came from overall or which subtotalSpec . Oracle appears to support GROUPING function (Ref: https://docs.oracle.com/cd/B28359_01/server.111/b28314/tdpdw_sql.htm#TDPDW00714 ) to solve that problem in addition to having the behavior you described from `ROLLUP` and `CUBE` functions by adding '1' for dims not present in subtotal spec for rows created by subtotalSpec.
   > For native query users, what would be the alternative to achieve similar effect? For this reason, it might be good to have a feature flag for enabling `old` behavior .
   
   I think we will have to add something similar to `GROUPING` (and `GROUPING_ID`: https://docs.microsoft.com/en-us/sql/t-sql/functions/grouping-id-transact-sql?view=sql-server-ver15). I'm not sure what the best way is to do this. Maybe model it as an expression function, that people could use in a postaggregator or a having filter? But then we'd need some way of providing the current-grouping-set context to postaggregators and having filters somehow. I guess there's no way around that. Native query users would use the new function directly, and SQL users would get it through `GROUPING` and `GROUPING_ID`.
   
   I'd like to make that addition, but probably not in this patch. For now, you think I should add the 'legacy' flag or do you think I should document that things will get jumbled if you have a limitSpec? Personally, I'd rather do the latter, if people don't have use cases that depend on the legacy limitSpec behavior.

----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
 
For queries about this service, please contact Infrastructure at:
users@infra.apache.org


With regards,
Apache Git Services

---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@druid.apache.org
For additional commands, e-mail: commits-help@druid.apache.org