LIMIT is not doing it right when using GROUP BY

[fgelcer]

as mentioned on #5361 , this is an effort for testing #2206 , and LIMIT is not behaving as expected (and not accordingly with C*):

cqlsh:test_paging_with_group_by> PAGING 1000
Page size: 1000
cqlsh:test_paging_with_group_by> SELECT a, b, c, d FROM test GROUP BY a, b LIMIT 4;

 a | b | c | d
---+---+---+---
 1 | 2 | 1 | 3
 1 | 4 | 2 | 6

(2 rows)
cqlsh:test_paging_with_group_by> SELECT a, b, c, d FROM test GROUP BY a, b LIMIT 5;

 a | b | c | d
---+---+---+---
 1 | 2 | 1 | 3
 1 | 4 | 2 | 6
 2 | 2 | 3 | 3

(3 rows)
cqlsh:test_paging_with_group_by> SELECT a, b, c, d FROM test GROUP BY a, b LIMIT 3;

 a | b | c | d
---+---+---+---
 1 | 2 | 1 | 3
 1 | 4 | 2 | 6

(2 rows)
cqlsh:test_paging_with_group_by> SELECT a, b, c, d FROM test GROUP BY a, b LIMIT 8;

 a | b | c | d
---+---+---+----
 1 | 2 | 1 |  3
 1 | 4 | 2 |  6
 2 | 2 | 3 |  3
 2 | 4 | 3 |  6
 4 | 8 | 2 | 12

(5 rows)

[fgelcer]

could be resolved by same PR as #5361 , but reporting apart as it doesn't behave the same.

[dekimir]

See diagnosis here: #5361 (comment)

[nyh]

This issue is also reproduced by the Cassandra unit test which I'm translating now, cassandra_tests/validation/operations/compact_storage_test.py::testGroupByWithoutPaging.

[avelanarius]

This issue is also reproduced by an integration test in Java Driver 3.x: https://github.com/scylladb/java-driver/blob/b3f3ebaf161b21e5c4840ec294595d4e4b39d9bf/driver-core/src/test/java/com/datastax/driver/core/querybuilder/QueryBuilderExecutionTest.java#L636

[nyh]

Updated list of tests reproducing this issue:

cql-pytest:
Tests specificially for this issue:

• test_group_by.py::test_group_by_clustering_prefix_with_limit

Tests that currently fail on many different issues (so fixing this issue won't make them pass):

• cassandra_tests/validation/operations/compact_storage_test.py::testGroupByWithoutPaging
• cassandra_tests/validation/operations/select_group_by_test.py::testGroupByWithoutPaging
• cassandra_tests/validation/operations/select_group_by_test.py::testGroupByWithStaticColumnsWithoutPaging
• cassandra_tests/validation/operations/select_group_by_test.py::testGroupByWithPaging
• cassandra_tests/validation/operations/select_group_by_test.py::testGroupByWithStaticColumnsWithPaging

Java Driver integration test:
https://github.com/scylladb/java-driver/blob/b3f3ebaf161b21e5c4840ec294595d4e4b39d9bf/driver-core/src/test/java/com/datastax/driver/core/querybuilder/QueryBuilderExecutionTest.java#L636

Additionally, dtest's paging_test.py appears to contain similar tests to those that Cassandra later translated to their unit tests and I later translated into cql-pytest tests (see above), but as noted above by @fgelcer these dtests failed and were modified to pass (!?) on Scylla, and now have several FIXMEs referencing issue 5362. When we fix this bug we'll probably need to fix those tests back (or just remove them, if they are duplicates of the unit tests?).