It is a valid SQL. It is discussed in many blogs , databases issues and so on. But most say it is part of SQL standard. One of the well written explanations is here: https://blog.jooq.org/2018/05/25/how-to-group-by-nothing-in-sql/, which also highlights that you may get different results depending on a database. The expected behavior is to get almost the same result as without grouping at all, except a situation when there are no rows matched. Then you should get either zero rows or 1 row (with the initial value of the aggregation, like 0 for count).
Some of the Calcite/Hive issues actually touch that functionality: https://issues.apache.org/jira/browse/CALCITE-1016 https://issues.apache.org/jira/browse/HIVE-12640 So IMHO, Kylin should also handle this query in one way or another. śr., 10 lip 2019 o 17:17 ShaoFeng Shi <[email protected]> napisał(a): > Is that a valid SQL? or say, what's the expected behavior if the grouping > is a constant? > > Best regards, > > Shaofeng Shi 史少锋 > Apache Kylin PMC > Email: [email protected] > > Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html > Join Kylin user mail group: [email protected] > Join Kylin dev mail group: [email protected] > > > > > Krzysztof Zarzycki <[email protected]> 于2019年7月10日周三 下午5:08写道： > >> Thanks Shaofeng, >> But that only changed the error to the one, that 1.10000001 cannot be >> handled: >> For query: select count(*) from report_sales.kylin_sales_model group by >> 1.1000001 >> >> I get: >> No DataTypeSerializer for type _literal_type while executing SQL: "select >> count(*) from report_sales.kylin_sales_model group by 1.1000001 LIMIT 50000" >> >> >> So the root error is still the same, that I cannot group by <constant> no >> matter what type constant is (int, float, boolean). >> >> >> wt., 9 lip 2019 o 11:53 ShaoFeng Shi <[email protected]> napisał(a): >> >>> Hi Kryzysztof, >>> >>> If you want to disable grouping by index, try to change the calcite >>> property from "LENIENT" back to "DEFAULT": >>> >>> kylin.query.calcite.extras-props.conformance=DEFAULT >>> >>> And then restart Kylin to take effect. Just take a try, >>> >>> Best regards, >>> >>> Shaofeng Shi 史少锋 >>> Apache Kylin PMC >>> Email: [email protected] >>> >>> Apache Kylin FAQ: https://kylin.apache.org/docs/gettingstarted/faq.html >>> Join Kylin user mail group: [email protected] >>> Join Kylin dev mail group: [email protected] >>> >>> >>> >>> >>> Krzysztof Zarzycki <[email protected]> 于2019年7月9日周二 下午4:42写道： >>> >>>> Does anyone in the group have a solution for this? >>>> The version of Tableau I have is 2018.3. >>>> I raised a jira ticket for this issue: >>>> https://issues.apache.org/jira/browse/KYLIN-4077 >>>> >>>> Thank you for help, >>>> Krzysztof >>>> >>>> >>>> śr., 26 cze 2019 o 18:34 Krzysztof Zarzycki <[email protected]> >>>> napisał(a): >>>> >>>>> Hi ShaoFeng, thanks for the answer. >>>>> The issue is I DON'T want group by index column. I want to execute sql >>>>> with GROUP BY <constant>, like "GROUP BY TRUE" or "GROUP BY 1.1000001" >>>>> which is something that Tableau produces. And I don't know how to achieve >>>>> this. >>>>> I run Kylin in version 2.6.2-hadoop3 (I have HDP 3.1) and Tableau I >>>>> don't remember, but I can get back this information later. >>>>> >>>>> >>>>> >>>>> >>>>> śr., 26 cze 2019 o 16:35 ShaoFeng Shi <[email protected]> >>>>> napisał(a): >>>>> >>>>>> Kylin does support "group by 1, 2, 3" (which is the index of the >>>>>> column). see https://issues.apache.org/jira/browse/KYLIN-3480 >>>>>> >>>>>> I remember that JIRA was to better support tableau, as it will >>>>>> generate such SQL, and it is lucky that Calcite supports this. >>>>>> >>>>>> BTW, what's your Kylin and Tableau version? There are many tableau >>>>>> users here, seems this is the first reporting on this "group by 1.0000" >>>>>> issue. >>>>>> >>>>>> Best regards, >>>>>> >>>>>> Shaofeng Shi 史少锋 >>>>>> Apache Kylin PMC >>>>>> Email: [email protected] >>>>>> >>>>>> Apache Kylin FAQ: >>>>>> https://kylin.apache.org/docs/gettingstarted/faq.html >>>>>> Join Kylin user mail group: [email protected] >>>>>> Join Kylin dev mail group: [email protected] >>>>>> >>>>>> >>>>>> >>>>>> >>>>>> Krzysztof Zarzycki <[email protected]> 于2019年6月26日周三 下午10:06写道： >>>>>> >>>>>>> Hi there Kylin community, >>>>>>> I'm a user of Kylin and I connect with Tableau to it. I have an >>>>>>> issue, that Tableau generates queries, that add "GROUP BY 1.100000...01" >>>>>>> clause, which I claim is unnecessary, but it's hard to fight with >>>>>>> Tableau. >>>>>>> Unfortunately this query cannot be executed by Kylin, showing an >>>>>>> error: >>>>>>> From line 1, column 8 to line 1, column 15: Aggregate expression is >>>>>>> illegal in GROUP BY clause while executing SQL: "select count(*) from >>>>>>> report_sales.kylin_sales_model GROUP BY 1.1000001 LIMIT 50000" >>>>>>> >>>>>>> Does anyone knows how to overcome this issue? Tableau integration is >>>>>>> probably one of the most common integrations, so probably someone >>>>>>> stumbled >>>>>>> upon this. >>>>>>> >>>>>>> What's even more interesting when I modify the query to "GROUP BY >>>>>>> 2.1000001", I get a different error message: >>>>>>> From line 2, column 10 to line 2, column 18: Ordinal out of range >>>>>>> while executing SQL: ... >>>>>>> >>>>>>> Which makes me thinking, that Kylin interprets this 1.100...01 >>>>>>> constant as 1 and then take it as column position from SELECT clause! >>>>>>> That >>>>>>> looks like a bug. >>>>>>> >>>>>>> I also tried group by constant, like GROUP BY TRUE, but that doesn't >>>>>>> work either: >>>>>>> No DataTypeSerializer for type _literal_type while executing SQL: >>>>>>> "select count(*) from report_sales.kylin_sales_model GROUP BY TRUE LIMIT >>>>>>> 50000" >>>>>>> >>>>>>> Only "GROUP BY ()" produced the right results. But I can't force >>>>>>> Tableau to change queries generated... >>>>>>> >>>>>>> >>>>>>> Thank you, >>>>>>> Krzysztof Zarzycki >>>>>>> >>>>>>
