cozmo opened a new issue, #13125: URL: https://github.com/apache/druid/issues/13125
It seems like the SQL translation layer doesn't correctly add subtotalsSpec to the native query, at least when `useApproximateCountDistinct` is false. ### Affected Version `0.23` ### Description Lets take a datasource that looks like the following:  If we make a query against it with `GROUPING SETS ((field), ())` it doesn't work as expected. Notably, the "null" row isn't returned.  *It does work if you use approximate distinct*  Lets take a look at the generated native query for the query that doesn't work (this is copied directly from the "explain" functionality in the UI) ```json { "queryType": "groupBy", "dataSource": { "type": "query", "query": { "queryType": "groupBy", "dataSource": { "type": "table", "name": "events" }, "intervals": { "type": "intervals", "intervals": [ "2022-08-01T00:00:00.001Z/146140482-04-24T15:36:27.903Z" ] }, "filter": { "type": "selector", "dimension": "client_id", "value": "11db091c-975b-4908-9f67-b1ceb126acdf" }, "granularity": { "type": "all" }, "dimensions": [ { "type": "default", "dimension": "customer_id", "outputName": "d0", "outputType": "STRING" }, { "type": "default", "dimension": "transaction_id", "outputName": "d1", "outputType": "STRING" } ], "limitSpec": { "type": "NoopLimitSpec" }, "context": { "sqlOuterLimit": 1001, "sqlQueryId": "5ab4448b-95ee-4878-85a4-7e327e6970e1", "useApproximateCountDistinct": false, "useApproximateTopN": false, "useNativeQueryExplain": true } } }, "intervals": { "type": "intervals", "intervals": [ "-146136543-09-08T08:23:32.096Z/146140482-04-24T15:36:27.903Z" ] }, "granularity": { "type": "all" }, "dimensions": [ { "type": "default", "dimension": "d0", "outputName": "_d0", "outputType": "STRING" } ], "aggregations": [ { "type": "filtered", "aggregator": { "type": "count", "name": "a0" }, "filter": { "type": "not", "field": { "type": "selector", "dimension": "d1", "value": null } }, "name": "a0" } ], "limitSpec": { "type": "default", "columns": [], "limit": 1001 }, "context": { "sqlOuterLimit": 1001, "sqlQueryId": "5ab4448b-95ee-4878-85a4-7e327e6970e1", "useApproximateCountDistinct": false, "useApproximateTopN": false, "useNativeQueryExplain": true } } ``` Now, lets add *subtotalsSpec*. ```diff @@ -76,20 +76,21 @@ "type": "not", "field": { "type": "selector", "dimension": "d1", "value": null } }, "name": "a0" } ], + "subtotalsSpec": [["_d0"], []], "limitSpec": { "type": "default", "columns": [], "limit": 1001 }, "context": { "sqlOuterLimit": 1001, "sqlQueryId": "5ab4448b-95ee-4878-85a4-7e327e6970e1", "useApproximateCountDistinct": false, "useApproximateTopN": false, ```  # Summary My naive read here is that the SQL translation layer (calcite?) doesn't correctly add `subtotalsSpec`, at least for `useApproximateCountDistinct` false. -- 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. To unsubscribe, e-mail: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
