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 > >
