Scylla: support for Group By to Select statement

[tzach]

Apache Cassandra added support for Group By in 3.10
https://issues.apache.org/jira/browse/CASSANDRA-10707

examples:

SELECT partitionKey, max(value) FROM myTable GROUP BY partitionKey;
SELECT partitionKey, clustering0, clustering1, max(value) FROM myTable GROUP BY partitionKey, clustering0, clustering1; 

[forkeer]

@tzach @slivne
any Update ?
announced a specific time for this issue ?

[tzach]

@forkeer we do not have a plan date for this feature yet

[dorlaor]

@tzach @slivne pls increase the priority and set a 2.x goal with X <=3

[robdefeo]

This would be amazing and provide a key feature!

[slivne]

@eliransin I think this can be reassigned to @dekimir

[dekimir]

Confusingly, the CQL reference allows two GROUP BY clauses in the same SELECT. Unless this is a bug, I don't understand what it means. The second GROUP BY appeared in CQL 6.0 -- the previous version (5.1) only had one GROUP BY.

[avikivity]

You're referring the the datastax documentation, not Cassandra. 5.1 and 6.0 are DSE version numbers.

Use the Cassandra docs instead.

[dekimir]

It looks like we currently forbid mixing aggregate and simple selectors, while Cassandra allows it. I think this is a problem for GROUP BY, since users are likely to want to know which row is for which group.

Some example queries in Cassandra:

cqlsh:ks> CREATE TABLE t(p int, c int, n int, primary key((p), c));
cqlsh:ks> select * from t;

 p | c | n
---+---+---

(0 rows)
cqlsh:ks> INSERT INTO t(p, c, n) VALUES ( 1, 11, 111);
cqlsh:ks> INSERT INTO t(p, c, n) VALUES ( 2, 21, 221);
cqlsh:ks> INSERT INTO t(p, c, n) VALUES ( 2, 22, 222);
cqlsh:ks> SELECT sum(n) from t GROUP BY p;

 system.sum(n)
---------------
           111
           443

(2 rows)

Warnings :
Aggregation query used without partition key

cqlsh:ks> SELECT sum(n), p from t GROUP BY p;

 system.sum(n) | p
---------------+---
           111 | 1
           443 | 2

(2 rows)

Warnings :
Aggregation query used without partition key

cqlsh:ks> SELECT sum(n), p from t;

 system.sum(n) | p
---------------+---
           554 | 1

(1 rows)

Warnings :
Aggregation query used without partition key

[eliransin]

@dekimir can you please open an issue with your findings? If possible please include what are the outputs for the same queries in Scylla (the error messages).

[dekimir]

Filed #4447.

[kabaluyot]

Is group by supported in scylladb?, I keep getting syntax error with the group by statement.

[denesb]

This feature is only merged to master as of now. It is currently scheduled to be released as part of 3.2 (see the milestone).