http://git-wip-us.apache.org/repos/asf/cassandra/blob/48ffad89/test/unit/org/apache/cassandra/cql3/validation/operations/SelectGroupByTest.java ---------------------------------------------------------------------- diff --cc test/unit/org/apache/cassandra/cql3/validation/operations/SelectGroupByTest.java index 177f286,0000000..b41b81f mode 100644,000000..100644 --- a/test/unit/org/apache/cassandra/cql3/validation/operations/SelectGroupByTest.java +++ b/test/unit/org/apache/cassandra/cql3/validation/operations/SelectGroupByTest.java @@@ -1,1817 -1,0 +1,1810 @@@ +/* + * Licensed to the Apache Software Foundation (ASF) under one + * or more contributor license agreements. See the NOTICE file + * distributed with this work for additional information + * regarding copyright ownership. The ASF licenses this file + * to you under the Apache License, Version 2.0 (the + * "License"); you may not use this file except in compliance + * with the License. You may obtain a copy of the License at + * + * http://www.apache.org/licenses/LICENSE-2.0 + * + * Unless required by applicable law or agreed to in writing, software + * distributed under the License is distributed on an "AS IS" BASIS, + * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. + * See the License for the specific language governing permissions and + * limitations under the License. + */ +package org.apache.cassandra.cql3.validation.operations; + +import org.junit.Test; + - import com.datastax.driver.core.SimpleStatement; - +import org.apache.cassandra.cql3.CQLTester; + +public class SelectGroupByTest extends CQLTester +{ + @Test + public void testGroupByWithoutPaging() throws Throwable + { + for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))" + + compactOption); + + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 3, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 4, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 4, 2, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (2, 2, 3, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (2, 4, 3, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (3, 3, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (4, 8, 2, 12, 24)"); + + // Makes sure that we have some tombstones + execute("DELETE FROM %s WHERE a = 1 AND b = 3 AND c = 2 AND d = 12"); + execute("DELETE FROM %s WHERE a = 3"); + + // Range queries + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a"), + row(1, 2, 6, 4L, 24), + row(2, 2, 6, 2L, 12), + row(4, 8, 24, 1L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b"), + row(1, 2, 6, 2L, 12), + row(1, 4, 12, 2L, 24), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE b = 2 GROUP BY a, b ALLOW FILTERING"), + row(1, 2, 6, 2L, 12), + row(2, 2, 6, 1L, 6)); + + assertEmpty(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE b IN () GROUP BY a, b ALLOW FILTERING")); + + // Range queries without aggregates + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b"), + row(1, 2, 1, 3), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + // Range query with LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b LIMIT 2"), + row(1, 2, 6, 2L, 12), + row(1, 4, 12, 2L, 24)); + + // Range queries with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 1"), + row(1, 2, 6, 2L, 12), + row(2, 2, 6, 1L, 6), + row(4, 8, 24, 1L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a PER PARTITION LIMIT 2"), + row(1, 2, 6, 4L, 24), + row(2, 2, 6, 2L, 12), + row(4, 8, 24, 1L, 24)); + + // Range query with PER PARTITION LIMIT and LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2"), + row(1, 2, 6, 2L, 12), + row(2, 2, 6, 1L, 6)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a PER PARTITION LIMIT 2"), + row(1, 2, 6, 4L, 24), + row(2, 2, 6, 2L, 12), + row(4, 8, 24, 1L, 24)); + + // Range queries without aggregates and with LIMIT + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c LIMIT 3"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6)); + + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b LIMIT 3"), + row(1, 2, 1, 3), + row(1, 4, 2, 6), + row(2, 2, 3, 3)); + + // Range queries without aggregates and with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b PER PARTITION LIMIT 1"), + row(1, 2, 1, 3), + row(2, 2, 3, 3), + row(4, 8, 2, 12)); + + // Range queries without aggregates, with PER PARTITION LIMIT and LIMIT + assertRows(execute("SELECT a, b, c, d FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2 LIMIT 3"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(2, 2, 3, 3)); + + // Range query with DISTINCT + assertRows(execute("SELECT DISTINCT a, count(a)FROM %s GROUP BY a"), + row(1, 1L), + row(2, 1L), + row(4, 1L)); + + assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries", + "SELECT DISTINCT a, count(a)FROM %s GROUP BY a, b"); + + // Range query with DISTINCT and LIMIT + assertRows(execute("SELECT DISTINCT a, count(a)FROM %s GROUP BY a LIMIT 2"), + row(1, 1L), + row(2, 1L)); + + assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries", + "SELECT DISTINCT a, count(a)FROM %s GROUP BY a, b LIMIT 2"); + + // Range query with ORDER BY + assertInvalidMessage("ORDER BY is only supported when the partition key is restricted by an EQ or an IN", + "SELECT a, b, c, count(b), max(e) FROM %s GROUP BY a, b ORDER BY b DESC, c DESC"); + + // Single partition queries + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY b, c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY a, b, c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY a, c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + // Single partition queries without aggregates + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b"), + row(1, 2, 1, 3), + row(1, 4, 2, 6)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY b, c"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 and token(a) = token(1) GROUP BY b, c"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6)); + + // Single partition queries with DISTINCT + assertRows(execute("SELECT DISTINCT a, count(a)FROM %s WHERE a = 1 GROUP BY a"), + row(1, 1L)); + + assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries", + "SELECT DISTINCT a, count(a)FROM %s WHERE a = 1 GROUP BY a, b"); + + // Single partition queries with LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 10"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + assertRows(execute("SELECT count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 1"), + row(1L, 6)); + + // Single partition queries with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 10"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + assertRows(execute("SELECT count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 1"), + row(1L, 6)); + + // Single partition queries without aggregates and with LIMIT + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b LIMIT 2"), + row(1, 2, 1, 3), + row(1, 4, 2, 6)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b LIMIT 1"), + row(1, 2, 1, 3)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2"), + row(1, 2, 1, 3), + row(1, 2, 2, 6)); + + // Single partition queries without aggregates and with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 2"), + row(1, 2, 1, 3), + row(1, 4, 2, 6)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 1"), + row(1, 2, 1, 3)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"), + row(1, 2, 1, 3), + row(1, 2, 2, 6)); + + // Single partition queries with ORDER BY + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC"), + row(1, 4, 24, 2L, 24), + row(1, 2, 12, 1L, 12), + row(1, 2, 6, 1L, 6)); + + // Single partition queries with ORDER BY and PER PARTITION LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC PER PARTITION LIMIT 1"), + row(1, 4, 24, 2L, 24)); + + // Single partition queries with ORDER BY and LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC LIMIT 2"), + row(1, 4, 24, 2L, 24), + row(1, 2, 12, 1L, 12)); + + // Multi-partitions queries + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) AND b = 2 GROUP BY a, b, c"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(2, 2, 6, 1L, 6)); + + // Multi-partitions queries without aggregates + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b"), + row(1, 2, 1, 3), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c"), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + // Multi-partitions query with DISTINCT + assertRows(execute("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a"), + row(1, 1L), + row(2, 1L), + row(4, 1L)); + + assertInvalidMessage("Grouping on clustering columns is not allowed for SELECT DISTINCT queries", + "SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b"); + + // Multi-partitions query with DISTINCT and LIMIT + assertRows(execute("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a LIMIT 2"), + row(1, 1L), + row(2, 1L)); + + // Multi-partitions queries with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 1"), + row(1, 2, 6, 1L, 6), + row(2, 2, 6, 1L, 6), + row(4, 8, 24, 1L, 24)); + + assertRows(execute("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 2"), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + // Multi-partitions queries with ORDER BY + assertRows(execute("SELECT a, b, c, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b ORDER BY b DESC, c DESC"), + row(4, 8, 2, 1L, 24), + row(2, 4, 3, 1L, 12), + row(1, 4, 2, 2L, 24), + row(2, 2, 3, 1L, 6), + row(1, 2, 2, 2L, 12)); + + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c ORDER BY b DESC, c DESC"), + row(4, 8, 2, 12), + row(2, 4, 3, 6), + row(1, 4, 2, 12), + row(2, 2, 3, 3), + row(1, 2, 2, 6), + row(1, 2, 1, 3)); + + // Multi-partitions queries with ORDER BY and LIMIT + assertRows(execute("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b ORDER BY b DESC, c DESC LIMIT 3"), + row(4, 8, 2, 12), + row(2, 4, 3, 6), + row(1, 4, 2, 12)); + + // Invalid queries + assertInvalidMessage("Group by is currently only supported on the columns of the PRIMARY KEY, got e", + "SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY a, e"); + + assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY", + "SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY c"); + + assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY", + "SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY a, c, b"); + + assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY", + "SELECT a, b, d, count(b), max(c) FROM %s WHERE a = 1 GROUP BY a, a"); + + assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY", + "SELECT a, b, c, d FROM %s WHERE token(a) = token(1) GROUP BY b, c"); + + assertInvalidMessage("Undefined column name clustering1", + "SELECT a, b as clustering1, max(c) FROM %s WHERE a = 1 GROUP BY a, clustering1"); + + assertInvalidMessage("Undefined column name z", + "SELECT a, b, max(c) FROM %s WHERE a = 1 GROUP BY a, b, z"); + + // Test with composite partition key + createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key ((a, b), c, d))" + compactOption); + + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 1, 1, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 1, 2, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 1, 3, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)"); + + assertInvalidMessage("Group by is not supported on only a part of the partition key", + "SELECT a, b, max(d) FROM %s GROUP BY a"); + + assertRows(execute("SELECT a, b, max(d) FROM %s GROUP BY a, b"), + row(1, 2, 12), + row(1, 1, 12)); + + assertRows(execute("SELECT a, b, max(d) FROM %s WHERE a = 1 AND b = 1 GROUP BY b"), + row(1, 1, 12)); + + // Test with table without clustering key + createTable("CREATE TABLE %s (a int primary key, b int, c int)" + compactOption); + + execute("INSERT INTO %s (a, b, c) VALUES (1, 3, 6)"); + execute("INSERT INTO %s (a, b, c) VALUES (2, 6, 12)"); + execute("INSERT INTO %s (a, b, c) VALUES (3, 12, 24)"); + + assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY", + "SELECT a, max(c) FROM %s WHERE a = 1 GROUP BY a, a"); + } + } + + @Test + public void testGroupByWithoutPagingWithDeletions() throws Throwable + { + for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))" + + compactOption); + + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 9, 18)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 9, 18)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 9, 18)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 3, 12, 24)"); + + execute("DELETE FROM %s WHERE a = 1 AND b = 2 AND c = 1 AND d = 12"); + execute("DELETE FROM %s WHERE a = 1 AND b = 2 AND c = 2 AND d = 9"); + + assertRows(execute("SELECT a, b, c, count(b), max(d) FROM %s GROUP BY a, b, c"), + row(1, 2, 1, 3L, 9), + row(1, 2, 2, 3L, 12), + row(1, 2, 3, 4L, 12)); + } + } + + @Test + public void testGroupByWithRangeNamesQueryWithoutPaging() throws Throwable + { + for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, primary key (a, b, c))" + + compactOption); + + for (int i = 1; i < 5; i++) + for (int j = 1; j < 5; j++) + for (int k = 1; k < 5; k++) + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", i, j, k, i + j); + + // Makes sure that we have some tombstones + execute("DELETE FROM %s WHERE a = 3"); + + // Range queries + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(1, 2, 3, 2L, 3), + row(2, 1, 3, 2L, 3), + row(2, 2, 4, 2L, 4), + row(4, 1, 5, 2L, 5), + row(4, 2, 6, 2L, 6)); + + // Range queries with LIMIT + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a LIMIT 5 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(1, 2, 3, 2L, 3), + row(2, 1, 3, 2L, 3)); + + // Range queries with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + // Range queries with PER PARTITION LIMIT and LIMIT + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 5 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRows(execute("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2 ALLOW FILTERING"), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3)); + } + } + + @Test + public void testGroupByWithStaticColumnsWithoutPaging() throws Throwable + { + createTable("CREATE TABLE %s (a int, b int, c int, s int static, d int, primary key (a, b, c))"); + + // ------------------------------------ + // Test with non static columns empty + // ------------------------------------ + execute("UPDATE %s SET s = 1 WHERE a = 1"); + execute("UPDATE %s SET s = 2 WHERE a = 2"); + execute("UPDATE %s SET s = 3 WHERE a = 4"); + + // Range queries + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + // Range query without aggregates + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a, b"), + row(1, null, 1), + row(2, null, 2), + row(4, null, 3)); + + // Range query with LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b LIMIT 2"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L)); + + // Range queries with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a PER PARTITION LIMIT 2"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + // Range query with DISTINCT + assertRows(execute("SELECT DISTINCT a, s, count(s) FROM %s GROUP BY a"), + row(1, 1, 1L), + row(2, 2, 1L), + row(4, 3, 1L)); + + // Range queries with DISTINCT and LIMIT + assertRows(execute("SELECT DISTINCT a, s, count(s) FROM %s GROUP BY a LIMIT 2"), + row(1, 1, 1L), + row(2, 2, 1L)); + + // Single partition queries + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a"), + row(1, null, 1, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a, b"), + row(1, null, 1, 0L, 1L)); + + // Single partition query without aggregates + assertRows(execute("SELECT a, b, s FROM %s WHERE a = 1 GROUP BY a, b"), + row(1, null, 1)); + + // Single partition query with LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a, b LIMIT 2"), + row(1, null, 1, 0L, 1L)); + + // Single partition query with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 2"), + row(1, null, 1, 0L, 1L)); + + // Single partition query with DISTINCT + assertRows(execute("SELECT DISTINCT a, s, count(s) FROM %s WHERE a = 1 GROUP BY a"), + row(1, 1, 1L)); + + // Multi-partitions queries + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + // Multi-partitions query without aggregates + assertRows(execute("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b"), + row(1, null, 1), + row(2, null, 2), + row(4, null, 3)); + + // Multi-partitions query with LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b LIMIT 2"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L)); + + // Multi-partitions query with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 2"), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + // Multi-partitions queries with DISTINCT + assertRows(execute("SELECT DISTINCT a, s, count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a"), + row(1, 1, 1L), + row(2, 2, 1L), + row(4, 3, 1L)); + + // Multi-partitions with DISTINCT and LIMIT + assertRows(execute("SELECT DISTINCT a, s, count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"), + row(1, 1, 1L), + row(2, 2, 1L)); + + // ------------------------------------ + // Test with some non static columns empty + // ------------------------------------ + execute("UPDATE %s SET s = 3 WHERE a = 3"); + execute("DELETE s FROM %s WHERE a = 4"); + + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 2, 1, 3)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 2, 2, 6)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 3, 2, 12)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 4, 2, 12)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 4, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (2, 2, 3, 3)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (2, 4, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (4, 8, 2, 12)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (5, 8, 2, 12)"); + + // Makes sure that we have some tombstones + execute("DELETE FROM %s WHERE a = 1 AND b = 3 AND c = 2"); + execute("DELETE FROM %s WHERE a = 5"); + + // Range queries + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a"), + row(1, 2, 1, 4L, 4L), + row(2, 2, 2, 2L, 2L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b"), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(2, 4, 2, 1L, 1L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE b = 2 GROUP BY a, b ALLOW FILTERING"), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L)); + + // Range queries without aggregates + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a"), + row(1, 2, 1), + row(2, 2, 2), + row(4, 8, null), + row(3, null, 3)); + + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a, b"), + row(1, 2, 1), + row(1, 4, 1), + row(2, 2, 2), + row(2, 4, 2), + row(4, 8, null), + row(3, null, 3)); + + // Range query with LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a LIMIT 2"), + row(1, 2, 1, 4L, 4L), + row(2, 2, 2, 2L, 2L)); + + // Range query with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b PER PARTITION LIMIT 1"), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + // Range query with PER PARTITION LIMIT and LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 3"), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(4, 8, null, 1L, 0L)); + + // Range queries without aggregates and with LIMIT + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a LIMIT 2"), + row(1, 2, 1), + row(2, 2, 2)); + + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a, b LIMIT 10"), + row(1, 2, 1), + row(1, 4, 1), + row(2, 2, 2), + row(2, 4, 2), + row(4, 8, null), + row(3, null, 3)); + + // Range queries without aggregates and with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a, b PER PARTITION LIMIT 1"), + row(1, 2, 1), + row(2, 2, 2), + row(4, 8, null), + row(3, null, 3)); + + // Range queries without aggregates, with PER PARTITION LIMIT and with LIMIT + assertRows(execute("SELECT a, b, s FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2"), + row(1, 2, 1), + row(2, 2, 2)); + + // Range query with DISTINCT + assertRows(execute("SELECT DISTINCT a, s, count(a), count(s) FROM %s GROUP BY a"), + row(1, 1, 1L, 1L), + row(2, 2, 1L, 1L), + row(4, null, 1L, 0L), + row(3, 3, 1L, 1L)); + + // Range query with DISTINCT and LIMIT + assertRows(execute("SELECT DISTINCT a, s, count(a), count(s) FROM %s GROUP BY a LIMIT 2"), + row(1, 1, 1L, 1L), + row(2, 2, 1L, 1L)); + + // Range query with ORDER BY + assertInvalidMessage("ORDER BY is only supported when the partition key is restricted by an EQ or an IN", + "SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a ORDER BY b DESC, c DESC"); + + // Single partition queries + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a"), + row(1, 2, 1, 4L, 4L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 3 GROUP BY a, b"), + row(3, null, 3, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 AND b = 2 GROUP BY a, b"), + row(2, 2, 2, 1L, 1L)); + + // Single partition queries without aggregates + assertRows(execute("SELECT a, b, s FROM %s WHERE a = 1 GROUP BY a"), + row(1, 2, 1)); + + assertRows(execute("SELECT a, b, s FROM %s WHERE a = 4 GROUP BY a, b"), + row(4, 8, null)); + + // Single partition query with LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b LIMIT 1"), + row(2, 2, 2, 1L, 1L)); + + // Single partition query with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b PER PARTITION LIMIT 1"), + row(2, 2, 2, 1L, 1L)); + + // Single partition queries without aggregates and with LIMIT + assertRows(execute("SELECT a, b, s FROM %s WHERE a = 2 GROUP BY a, b LIMIT 1"), + row(2, 2, 2)); + + assertRows(execute("SELECT a, b, s FROM %s WHERE a = 2 GROUP BY a, b LIMIT 2"), + row(2, 2, 2), + row(2, 4, 2)); + + // Single partition queries with DISTINCT + assertRows(execute("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a = 2 GROUP BY a"), + row(2, 2, 1L, 1L)); + + assertRows(execute("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a = 4 GROUP BY a"), + row(4, null, 1L, 0L)); + + // Single partition query with ORDER BY + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC"), + row(2, 4, 2, 1L, 1L), + row(2, 2, 2, 1L, 1L)); + + // Single partition queries with ORDER BY and LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC LIMIT 1"), + row(2, 4, 2, 1L, 1L)); + + // Single partition queries with ORDER BY and PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC PER PARTITION LIMIT 1"), + row(2, 4, 2, 1L, 1L)); + + // Multi-partitions queries + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a"), + row(1, 2, 1, 4L, 4L), + row(2, 2, 2, 2L, 2L), + row(3, null, 3, 0L, 1L), + row(4, 8, null, 1L, 0L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b"), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(2, 4, 2, 1L, 1L), + row(3, null, 3, 0L, 1L), + row(4, 8, null, 1L, 0L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) AND b = 2 GROUP BY a, b"), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L)); + + // Multi-partitions queries without aggregates + assertRows(execute("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a"), + row(1, 2, 1), + row(2, 2, 2), + row(3, null, 3), + row(4, 8, null)); + + assertRows(execute("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b"), + row(1, 2, 1), + row(1, 4, 1), + row(2, 2, 2), + row(2, 4, 2), + row(3, null, 3), + row(4, 8, null)); + + // Multi-partitions query with LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"), + row(1, 2, 1, 4L, 4L), + row(2, 2, 2, 2L, 2L)); + + // Multi-partitions query with PER PARTITION LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 1"), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(3, null, 3, 0L, 1L), + row(4, 8, null, 1L, 0L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 2"), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(2, 4, 2, 1L, 1L), + row(3, null, 3, 0L, 1L), + row(4, 8, null, 1L, 0L)); + + // Multi-partitions queries with PER PARTITION LIMIT and LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 3"), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(3, null, 3, 0L, 1L)); + + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 4 LIMIT 3"), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L)); + + // Multi-partitions queries without aggregates and with LIMIT + assertRows(execute("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"), + row(1, 2, 1), + row(2, 2, 2)); + + assertRows(execute("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b LIMIT 10"), + row(1, 2, 1), + row(1, 4, 1), + row(2, 2, 2), + row(2, 4, 2), + row(3, null, 3), + row(4, 8, null)); + + // Multi-partitions query with DISTINCT + assertRows(execute("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a"), + row(1, 1, 1L, 1L), + row(2, 2, 1L, 1L), + row(3, 3, 1L, 1L), + row(4, null, 1L, 0L)); + + // Multi-partitions query with DISTINCT and LIMIT + assertRows(execute("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2"), + row(1, 1, 1L, 1L), + row(2, 2, 1L, 1L)); + + // Multi-partitions query with ORDER BY + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b ORDER BY b DESC, c DESC"), + row(4, 8, null, 1L, 0L), + row(1, 4, 1, 2L, 2L), + row(2, 4, 2, 1L, 1L), + row(2, 2, 2, 1L, 1L), + row(1, 2, 1, 2L, 2L)); + + // Multi-partitions queries with ORDER BY and LIMIT + assertRows(execute("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b ORDER BY b DESC, c DESC LIMIT 2"), + row(4, 8, null, 1L, 0L), + row(1, 4, 1, 2L, 2L)); + } + + @Test + public void testGroupByWithPaging() throws Throwable + { + for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))" + + compactOption); + + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 1, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 2, 2, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 3, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 4, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (1, 4, 2, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (2, 2, 3, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (2, 4, 3, 6, 12)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (3, 3, 2, 12, 24)"); + execute("INSERT INTO %s (a, b, c, d, e) VALUES (4, 8, 2, 12, 24)"); + + // Makes sure that we have some tombstones + execute("DELETE FROM %s WHERE a = 1 AND b = 3 AND c = 2 AND d = 12"); + execute("DELETE FROM %s WHERE a = 3"); + + for (int pageSize = 1; pageSize < 10; pageSize++) + { + // Range queries + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a", pageSize), + row(1, 2, 6, 4L, 24), + row(2, 2, 6, 2L, 12), + row(4, 8, 24, 1L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b", pageSize), + row(1, 2, 6, 2L, 12), + row(1, 4, 12, 2L, 24), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s", pageSize), + row(1, 2, 6, 7L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE b = 2 GROUP BY a, b ALLOW FILTERING", + pageSize), + row(1, 2, 6, 2L, 12), + row(2, 2, 6, 1L, 6)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE b = 2 ALLOW FILTERING", + pageSize), + row(1, 2, 6, 3L, 12)); + + // Range queries without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s GROUP BY a, b, c", pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s GROUP BY a, b", pageSize), + row(1, 2, 1, 3), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + // Range query with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b LIMIT 2", + pageSize), + row(1, 2, 6, 2L, 12), + row(1, 4, 12, 2L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s LIMIT 2", + pageSize), + row(1, 2, 6, 7L, 24)); + + // Range queries with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 3", pageSize), + row(1, 2, 6, 2L, 12), + row(1, 4, 12, 2L, 24), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 1", pageSize), + row(1, 2, 6, 2L, 12), + row(2, 2, 6, 1L, 6), + row(4, 8, 24, 1L, 24)); + + // Range query with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2", pageSize), + row(1, 2, 6, 2L, 12), + row(2, 2, 6, 1L, 6)); + + // Range query without aggregates and with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2", pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + // Range queries without aggregates and with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s GROUP BY a, b, c LIMIT 3", pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s GROUP BY a, b LIMIT 3", pageSize), + row(1, 2, 1, 3), + row(1, 4, 2, 6), + row(2, 2, 3, 3)); + + // Range query without aggregates, with PER PARTITION LIMIT and with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2 LIMIT 3", pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(2, 2, 3, 3)); + + // Range query with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s GROUP BY a", pageSize), + row(1, 1L), + row(2, 1L), + row(4, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s", pageSize), + row(1, 3L)); + + // Range query with DISTINCT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s GROUP BY a LIMIT 2", pageSize), + row(1, 1L), + row(2, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s LIMIT 2", pageSize), + row(1, 3L)); + + // Range query with ORDER BY + assertInvalidMessage("ORDER BY is only supported when the partition key is restricted by an EQ or an IN", + "SELECT a, b, c, count(b), max(e) FROM %s GROUP BY a, b ORDER BY b DESC, c DESC"); + + // Single partition queries + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1", pageSize), + row(1, 2, 6, 4L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2 GROUP BY a, b, c", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 AND b = 2", + pageSize), + row(1, 2, 6, 2L, 12)); + + // Single partition queries without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b", pageSize), + row(1, 2, 1, 3), + row(1, 4, 2, 6)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c", pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6)); + + // Single partition query with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s WHERE a = 1 GROUP BY a", + pageSize), + row(1, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s WHERE a = 1 GROUP BY a", + pageSize), + row(1, 1L)); + + // Single partition queries with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 10", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 LIMIT 2", + pageSize), + row(1, 2, 6, 4L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 1", + pageSize), + row(1L, 6)); + + // Single partition query with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12)); + + // Single partition queries without aggregates and with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b LIMIT 2", + pageSize), + row(1, 2, 1, 3), + row(1, 4, 2, 6)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b LIMIT 1", + pageSize), + row(1, 2, 1, 3)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2", + pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6)); + + // Single partition queries with ORDER BY + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC", + pageSize), + row(1, 4, 24, 2L, 24), + row(1, 2, 12, 1L, 12), + row(1, 2, 6, 1L, 6)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 ORDER BY b DESC, c DESC", + pageSize), + row(1, 4, 24, 4L, 24)); + + // Single partition queries with ORDER BY and LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC LIMIT 2", + pageSize), + row(1, 4, 24, 2L, 24), + row(1, 2, 12, 1L, 12)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 ORDER BY b DESC, c DESC LIMIT 2", + pageSize), + row(1, 4, 24, 4L, 24)); + + // Single partition queries with ORDER BY and PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a = 1 GROUP BY a, b, c ORDER BY b DESC, c DESC PER PARTITION LIMIT 2", + pageSize), + row(1, 4, 24, 2L, 24), + row(1, 2, 12, 1L, 12)); + + // Multi-partitions queries + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(1, 4, 12, 2L, 24), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4)", + pageSize), + row(1, 2, 6, 7L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) AND b = 2 GROUP BY a, b, c", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(2, 2, 6, 1L, 6)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) AND b = 2", + pageSize), + row(1, 2, 6, 3L, 12)); + + // Multi-partitions queries with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 2", + pageSize), + row(1, 2, 6, 1L, 6), + row(1, 2, 12, 1L, 12), + row(2, 2, 6, 1L, 6), + row(2, 4, 12, 1L, 12), + row(4, 8, 24, 1L, 24)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, e, count(b), max(e) FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 1", + pageSize), + row(1, 2, 6, 1L, 6), + row(2, 2, 6, 1L, 6), + row(4, 8, 24, 1L, 24)); + + // Multi-partitions queries without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b", + pageSize), + row(1, 2, 1, 3), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, c, d FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c", + pageSize), + row(1, 2, 1, 3), + row(1, 2, 2, 6), + row(1, 4, 2, 6), + row(2, 2, 3, 3), + row(2, 4, 3, 6), + row(4, 8, 2, 12)); + + // Multi-partitions queries with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a", + pageSize), + row(1, 1L), + row(2, 1L), + row(4, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4)", + pageSize), + row(1, 3L)); + + // Multi-partitions query with DISTINCT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) GROUP BY a LIMIT 2", + pageSize), + row(1, 1L), + row(2, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, count(a)FROM %s WHERE a IN (1, 2, 4) LIMIT 2", + pageSize), + row(1, 3L)); + } + } + } + + @Test + public void testGroupByWithRangeNamesQueryWithPaging() throws Throwable + { + for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" }) + { + createTable("CREATE TABLE %s (a int, b int, c int, d int, primary key (a, b, c))" + + compactOption); + + for (int i = 1; i < 5; i++) + for (int j = 1; j < 5; j++) + for (int k = 1; k < 5; k++) + execute("INSERT INTO %s (a, b, c, d) VALUES (?, ?, ?, ?)", i, j, k, i + j); + + // Makes sure that we have some tombstones + execute("DELETE FROM %s WHERE a = 3"); + + for (int pageSize = 1; pageSize < 2; pageSize++) + { + // Range queries + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(1, 2, 3, 2L, 3), + row(2, 1, 3, 2L, 3), + row(2, 2, 4, 2L, 4), + row(4, 1, 5, 2L, 5), + row(4, 2, 6, 2L, 6)); + + // Range queries with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a LIMIT 5 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b LIMIT 3 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(1, 2, 3, 2L, 3), + row(2, 1, 3, 2L, 3)); + + // Range queries with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + // Range queries with PER PARTITION LIMIT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b = 1 and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 5 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3), + row(4, 1, 5, 2L, 5)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, d, count(b), max(d) FROM %s WHERE b IN (1, 2) and c IN (1, 2) GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2 ALLOW FILTERING", pageSize), + row(1, 1, 2, 2L, 2), + row(2, 1, 3, 2L, 3)); + } + } + } + + @Test + public void testGroupByWithStaticColumnsWithPaging() throws Throwable + { + createTable("CREATE TABLE %s (a int, b int, c int, s int static, d int, primary key (a, b, c))"); + + // ------------------------------------ + // Test with non static columns empty + // ------------------------------------ + execute("UPDATE %s SET s = 1 WHERE a = 1"); + execute("UPDATE %s SET s = 2 WHERE a = 2"); + execute("UPDATE %s SET s = 3 WHERE a = 4"); + + for (int pageSize = 1; pageSize < 10; pageSize++) + { + // Range queries + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a", pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b", pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s", pageSize), + row(1, null, 1, 0L, 3L)); + + // Range query without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s GROUP BY a, b", pageSize), + row(1, null, 1), + row(2, null, 2), + row(4, null, 3)); + + // Range query with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b LIMIT 2", + pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s LIMIT 2", pageSize), + row(1, null, 1, 0L, 3L)); + + // Range query with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a PER PARTITION LIMIT 2", pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + // Range query with PER PARTITION LIMIT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a PER PARTITION LIMIT 2 LIMIT 2", pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L)); + + // Range queries with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s GROUP BY a", pageSize), + row(1, 1, 1L), + row(2, 2, 1L), + row(4, 3, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s ", pageSize), + row(1, 1, 3L)); + + // Range queries with DISTINCT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s GROUP BY a LIMIT 2", pageSize), + row(1, 1, 1L), + row(2, 2, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s LIMIT 2", pageSize), + row(1, 1, 3L)); + + // Single partition queries + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a", + pageSize), + row(1, null, 1, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a, b", + pageSize), + row(1, null, 1, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1", pageSize), + row(1, null, 1, 0L, 1L)); + + // Single partition query without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a = 1 GROUP BY a, b", pageSize), + row(1, null, 1)); + + // Single partition queries with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a, b LIMIT 2", + pageSize), + row(1, null, 1, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 LIMIT 2", + pageSize), + row(1, null, 1, 0L, 1L)); + + + // Single partition queries with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s WHERE a = 1 GROUP BY a", + pageSize), + row(1, 1, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s WHERE a = 1", pageSize), + row(1, 1, 1L)); + + // Multi-partitions queries + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a", + pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b", + pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4)", + pageSize), + row(1, null, 1, 0L, 3L)); + + // Multi-partitions query without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b", + pageSize), + row(1, null, 1), + row(2, null, 2), + row(4, null, 3)); + + // Multi-partitions query with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b LIMIT 2", + pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) LIMIT 2", + pageSize), + row(1, null, 1, 0L, 3L)); + + // Multi-partitions query with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a PER PARTITION LIMIT 2", + pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L), + row(4, null, 3, 0L, 1L)); + + // Multi-partitions query with PER PARTITION LIMIT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a PER PARTITION LIMIT 2 LIMIT 2", + pageSize), + row(1, null, 1, 0L, 1L), + row(2, null, 2, 0L, 1L)); + + // Multi-partitions queries with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a", + pageSize), + row(1, 1, 1L), + row(2, 2, 1L), + row(4, 3, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s WHERE a IN (1, 2, 3, 4)", + pageSize), + row(1, 1, 3L)); + + // Multi-partitions queries with DISTINCT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2", + pageSize), + row(1, 1, 1L), + row(2, 2, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(s) FROM %s WHERE a IN (1, 2, 3, 4) LIMIT 2", + pageSize), + row(1, 1, 3L)); + } + + // ------------------------------------ + // Test with non static columns + // ------------------------------------ + execute("UPDATE %s SET s = 3 WHERE a = 3"); + execute("DELETE s FROM %s WHERE a = 4"); + + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 2, 1, 3)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 2, 2, 6)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 3, 2, 12)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 4, 2, 12)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (1, 4, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (2, 2, 3, 3)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (2, 4, 3, 6)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (4, 8, 2, 12)"); + execute("INSERT INTO %s (a, b, c, d) VALUES (5, 8, 2, 12)"); + + // Makes sure that we have some tombstones + execute("DELETE FROM %s WHERE a = 1 AND b = 3 AND c = 2"); + execute("DELETE FROM %s WHERE a = 5"); + + for (int pageSize = 1; pageSize < 10; pageSize++) + { + // Range queries + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a", pageSize), + row(1, 2, 1, 4L, 4L), + row(2, 2, 2, 2L, 2L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b", pageSize), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(2, 4, 2, 1L, 1L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s", pageSize), + row(1, 2, 1, 7L, 7L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE b = 2 GROUP BY a, b ALLOW FILTERING", + pageSize), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE b = 2 ALLOW FILTERING", + pageSize), + row(1, 2, 1, 3L, 3L)); + + // Range queries without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s GROUP BY a", pageSize), + row(1, 2, 1), + row(2, 2, 2), + row(4, 8, null), + row(3, null, 3)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s GROUP BY a, b", pageSize), + row(1, 2, 1), + row(1, 4, 1), + row(2, 2, 2), + row(2, 4, 2), + row(4, 8, null), + row(3, null, 3)); + + // Range query with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a LIMIT 2", + pageSize), + row(1, 2, 1, 4L, 4L), + row(2, 2, 2, 2L, 2L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s LIMIT 2", pageSize), + row(1, 2, 1, 7L, 7L)); + + // Range queries without aggregates and with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s GROUP BY a LIMIT 2", pageSize), + row(1, 2, 1), + row(2, 2, 2)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s GROUP BY a, b LIMIT 10", pageSize), + row(1, 2, 1), + row(1, 4, 1), + row(2, 2, 2), + row(2, 4, 2), + row(4, 8, null), + row(3, null, 3)); + + // Range queries with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b PER PARTITION LIMIT 2", pageSize), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(2, 4, 2, 1L, 1L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b PER PARTITION LIMIT 1", pageSize), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(4, 8, null, 1L, 0L), + row(3, null, 3, 0L, 1L)); + + // Range queries with PER PARTITION LIMIT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b PER PARTITION LIMIT 2 LIMIT 3", pageSize), + row(1, 2, 1, 2L, 2L), + row(1, 4, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 3", pageSize), + row(1, 2, 1, 2L, 2L), + row(2, 2, 2, 1L, 1L), + row(4, 8, null, 1L, 0L)); + + // Range query without aggregates and with PER PARTITION LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s GROUP BY a, b PER PARTITION LIMIT 1", pageSize), + row(1, 2, 1), + row(2, 2, 2), + row(4, 8, null), + row(3, null, 3)); + + // Range queries with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s GROUP BY a", pageSize), + row(1, 1, 1L, 1L), + row(2, 2, 1L, 1L), + row(4, null, 1L, 0L), + row(3, 3, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s", pageSize), + row(1, 1, 4L, 3L)); + + // Range queries with DISTINCT and LIMIT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s GROUP BY a LIMIT 2", + pageSize), + row(1, 1, 1L, 1L), + row(2, 2, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s LIMIT 2", pageSize), + row(1, 1, 4L, 3L)); + + // Single partition queries + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 1 GROUP BY a", + pageSize), + row(1, 2, 1, 4L, 4L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 3 GROUP BY a, b", + pageSize), + row(3, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 3", + pageSize), + row(3, null, 3, 0L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 AND b = 2 GROUP BY a, b", + pageSize), + row(2, 2, 2, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 AND b = 2", + pageSize), + row(2, 2, 2, 1L, 1L)); + + // Single partition queries without aggregates + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a = 1 GROUP BY a", pageSize), + row(1, 2, 1)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a = 4 GROUP BY a, b", pageSize), + row(4, 8, null)); + + // Single partition queries with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b LIMIT 1", + pageSize), + row(2, 2, 2, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 LIMIT 1", + pageSize), + row(2, 2, 2, 2L, 2L)); + + // Single partition queries without aggregates and with LIMIT + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a = 2 GROUP BY a, b LIMIT 1", pageSize), + row(2, 2, 2)); + + assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a = 2 GROUP BY a, b LIMIT 2", pageSize), + row(2, 2, 2), + row(2, 4, 2)); + + // Single partition queries with DISTINCT + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a = 2 GROUP BY a", + pageSize), + row(2, 2, 1L, 1L)); + + assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a = 4 GROUP BY a", + pageSize), +
<TRUNCATED> --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
