GROUP BY () not supported

[michael-2956]

Hello. First of all, thanks for this amazing project!
I've ran into an issue that explicit GROUP BY () is not supported.

For example, when we write:
SELECT SUM (value) from sales,
it is supposed to be the same as:
SELECT SUM (value) from sales group by (),
but the latter is not parseable by the parser. It produces:
ParserError("Expected an expression:, found: )").

I suppose that the issue is that it expects a row expression there, but () is just a special syntax for grouping by nothing.
You technically can specify GROUP BY (), value, which would just group by value, but if only () is present it treats all rows like a single group.

How would you suggest fixing this?

[alamb]

I would recommend finding a sql reference for the syntax (aka which database invented / used this syntax) to understand what () means in this context and then update the parser accordingly

[michael-2956]

() is usually used in GROUPING SETS, ROLLUP, etc. It is used to include "single group" into the list of sets, for example:

SELECT count(*), o_orderkey, o_custkey
FROM orders
GROUP BY GROUPING SETS ((), (o_orderkey, o_custkey));

Will return something like:

 count | o_orderkey | o_custkey 
-------+------------+-----------
     4 |            |          
     1 |          1 |         1
     1 |          4 |         4
     1 |          3 |         3
     1 |          2 |         2

It seems that the parser supports this kind of syntax though. GROUP BY () is rarely used on its own and is more of a side-product of GROUPING SETS.

It seems that the behaviour is different on different backends, which means that this feature can be implemented differently for different backends really... For Postgres, it will be enough to treat GROUP BY () as an absence of group by, but for MySQL it would actually make a difference.

Since this feature is rarely used and I can do without it on my end, I guess you can close this issue @alamb .

[alamb]

Thanks for the update @michael-2956 -- since this is an actual feature gap I'll plan to leave this issue open in case anyone else runs into the limitation and might benefit from your research.

[git-hulk]

Hi @alamb @iffyio, I would like to take this task, and I want to hear your thoughts before starting the implementation.

We can implement this feature with following ways:

1. Add a dedicated group by type like GroupByExpr::All, maybe call it GroupByExpr::Nothing
2. Regard () as a special case for GroupByExpr::Expressions and add an value like Value::Nothing, then return GroupByExpr::Expressions(vec![Value::Nothing]) to represent the group by nothing clause

I think the better solution is to use GroupByExpr::Expressions(vec![]) to represent the group by nothing condition, but it's now used to represent the GROUP BY clause doesn't exist and we should not break this pattern. So I prefer using solution 1 which adds a new enum value GroupByExpr::Nothing for this purpose. What do you think?

[iffyio]

Sounds good! Representing it as an new variant sounds reasonable to me! (i.e. option 1. with GroupBy::Nothing)

[git-hulk]

@iffyio Thank you!