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
