OK, please log a jira case. Please describe cases where it should give an error. Describe any pertinent flags.
> On May 29, 2024, at 8:58 AM, Itiel Sadeh <iti...@sqreamtech.com.INVALID> > wrote: > > Thanks for the response Julian. > > I agree that it is a pretty unuseful use case, but it is a valid SQL as far > as I can tell. PostgreSQL does support it. > Maybe Calcite shouldn't support it, but I think that the error message is a > bit unintuitive in this case. > > Thanks again, > Itiel > > On Tue, May 28, 2024 at 10:02 PM Julian Hyde <jhyde.apa...@gmail.com> wrote: > >> It doesn’t seem particularly useful to use “*” in a “GROUP BY” query. The >> query will be invalid if you don’t include all the columns in the GROUP BY >> clause. >> >> (In a few databases, one of which is MySQL, the query “SELECT empno, >> deptno, sal FROM emp GROUP BY empno” would be valid if “empno” is a primary >> key and the DBMS recognizes functionally dependent columns. But Calcite >> does not do this.) >> >> Do any other DBMSs support “*” in “GROUP BY” queries? >> >>> Is the issue above a bug? >> >> No, it is not a bug. It may be a missing feature. This discussion will >> decide whether it is desirable feature. >> >>> If so, can we do the star expansion before the GROUP BY validation? >>> And when can a select item is depends on the GROUP BY list? >> >> >> Probably not. If you change the order of expansion, a lot of things that >> used to work will stop working. I think that this fix will be tricky to >> accomplish. >> >> Julian >> >> >>> On May 28, 2024, at 12:22 AM, Itiel Sadeh <iti...@sqreamtech.com.INVALID> >> wrote: >>> >>> Hello calcite team, >>> >>> Consider the following: >>> Let's say I have a table "t" with two columns: >>> "CREATE TABLE t(x int, y int);" >>> >>> Now, the following query will result in validation error: >>> "SELECT * FROM t GROUP BY 1,2", >>> But if I'm not using "*" it will work: >>> "SELECT x, y FROM t GROUP BY 1,2" >>> >>> The issue is that the group by validation happens before we do star >>> expansion. >>> Note that ORDER BY doesn't have the same issue, as the ORDER BY >> validation >>> happens after star expansion. >>> >>> The star expansion is done inside `ValidateSelectList`, and there is a >>> comment above stating that: >>> >>>> // Validate the SELECT clause late, because a select item might >>>> // depend on the GROUP BY list, or the window function might >> reference >>>> // window name in the WINDOW clause etc. >>>> >>> >>> Given that I have 3 questions: Is the issue above a bug? >>> If so, can we do the star expansion before the GROUP BY validation? >>> And when can a select item is depends on the GROUP BY list? >>> >>> Thanks, >>> Itiel >> >>
