cassandra-trunk/test/unit/org/apache/cassandra/cql3/validation/operations/SelectGroupByTest.java
/*
 * 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.LocalDate;

import java.time.LocalDateTime;
import java.time.ZoneOffset;
import java.time.format.DateTimeFormatter;
import java.util.Date;
import java.util.UUID;

import org.apache.cassandra.cql3.CQLTester;
import org.apache.cassandra.serializers.SimpleDateSerializer;
import org.apache.cassandra.serializers.TimeSerializer;
import org.apache.cassandra.serializers.TimestampSerializer;
import org.apache.cassandra.utils.TimeUUID;

public class SelectGroupByTest extends CQLTester
{
    @Test
    public void testGroupByWithoutPaging() throws Throwable
    {
        createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))");

        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 queries with wildcard
        assertRows(execute("SELECT * FROM %s GROUP BY a, b, c"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(1, 4, 2, 6, 12),
                   row(2, 2, 3, 3, 6),
                   row(2, 4, 3, 6, 12),
                   row(4, 8, 2, 12, 24));

        assertRows(execute("SELECT * FROM %s GROUP BY a, b"),
                   row(1, 2, 1, 3, 6),
                   row(1, 4, 2, 6, 12),
                   row(2, 2, 3, 3, 6),
                   row(2, 4, 3, 6, 12),
                   row(4, 8, 2, 12, 24));

        // 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 with wildcard and with LIMIT
        assertRows(execute("SELECT * FROM %s GROUP BY a, b, c LIMIT 3"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(1, 4, 2, 6, 12));

        assertRows(execute("SELECT * FROM %s GROUP BY a, b LIMIT 3"),
                   row(1, 2, 1, 3, 6),
                   row(1, 4, 2, 6, 12),
                   row(2, 2, 3, 3, 6));

        // 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 with wildcard and with PER PARTITION LIMIT
        assertRows(execute("SELECT * FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(2, 2, 3, 3, 6),
                   row(2, 4, 3, 6, 12),
                   row(4, 8, 2, 12, 24));

        assertRows(execute("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1"),
                   row(1, 2, 1, 3, 6),
                   row(2, 2, 3, 3, 6),
                   row(4, 8, 2, 12, 24));

        // 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 queries with wildcard, with PER PARTITION LIMIT and LIMIT
        assertRows(execute("SELECT * FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2 LIMIT 3"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(2, 2, 3, 3, 6));

        // 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 wildcard
        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(1, 4, 2, 6, 12));

        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b"),
                   row(1, 2, 1, 3, 6),
                   row(1, 4, 2, 6, 12));

        // 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 with wildcard and with LIMIT
        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12));

        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b LIMIT 1"),
                   row(1, 2, 1, 3, 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 wildcard and with PER PARTITION LIMIT
        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12));

        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 1"),
                   row(1, 2, 1, 3, 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 with wildcard
        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(1, 4, 2, 6, 12),
                   row(2, 2, 3, 3, 6),
                   row(2, 4, 3, 6, 12),
                   row(4, 8, 2, 12, 24));

        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b"),
                   row(1, 2, 1, 3, 6),
                   row(1, 4, 2, 6, 12),
                   row(2, 2, 3, 3, 6),
                   row(2, 4, 3, 6, 12),
                   row(4, 8, 2, 12, 24));

        // 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 with wildcard and PER PARTITION LIMIT
        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c PER PARTITION LIMIT 2"),
                   row(1, 2, 1, 3, 6),
                   row(1, 2, 2, 6, 12),
                   row(2, 2, 3, 3, 6),
                   row(2, 4, 3, 6, 12),
                   row(4, 8, 2, 12, 24));

        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b PER PARTITION LIMIT 1"),
                   row(1, 2, 1, 3, 6),
                   row(2, 2, 3, 3, 6),
                   row(4, 8, 2, 12, 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));

        // Multi-partitions with wildcard, ORDER BY and LIMIT
        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c ORDER BY b DESC, c DESC LIMIT 3"),
                   row(4, 8, 2, 12, 24),
                   row(2, 4, 3, 6, 12),
                   row(1, 4, 2, 12, 24));

        // 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))");

        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)");

        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
    {
        createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))");

        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
    {
        createTable("CREATE TABLE %s (a int, b int, c int, d int, primary key (a, b, c))");

        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 wildcard
        assertRows(execute("SELECT * FROM %s GROUP BY a, b"),
                   row(1, null, null, 1, null),
                   row(2, null, null, 2, null),
                   row(4, null, null, 3, null ));

        // 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 wildcard
        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a, b"),
                   row(1, null, null, 1, null));

        // 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 wildcard
        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b"),
                   row(1, null, null, 1, null),
                   row(2, null, null, 2, null),
                   row(4, null, null, 3, 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, 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 queries with wildcard
        assertRows(execute("SELECT * FROM %s GROUP BY a"),
                   row(1, 2, 1, 1, 3),
                   row(2, 2, 3, 2, 3),
                   row(4, 8, 2, null, 12),
                   row(3, null, null, 3, null));

        assertRows(execute("SELECT * FROM %s GROUP BY a, b"),
                   row(1, 2, 1, 1, 3),
                   row(1, 4, 2, 1, 12),
                   row(2, 2, 3, 2, 3),
                   row(2, 4, 3, 2, 6),
                   row(4, 8, 2, null, 12),
                   row(3, null, null, 3, null));

        // 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 with wildcard and with LIMIT
        assertRows(execute("SELECT * FROM %s GROUP BY a LIMIT 2"),
                   row(1, 2, 1, 1, 3),
                   row(2, 2, 3, 2, 3));

        assertRows(execute("SELECT * FROM %s GROUP BY a, b LIMIT 10"),
                   row(1, 2, 1, 1, 3),
                   row(1, 4, 2, 1, 12),
                   row(2, 2, 3, 2, 3),
                   row(2, 4, 3, 2, 6),
                   row(4, 8, 2, null, 12),
                   row(3, null, null, 3, null));

        // 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 with wildcard and with PER PARTITION LIMIT
        assertRows(execute("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1"),
                   row(1, 2, 1, 1, 3),
                   row(2, 2, 3, 2, 3),
                   row(4, 8, 2, null, 12),
                   row(3, null, null, 3, null));

        // 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 queries with wildcard, PER PARTITION LIMIT and LIMIT
        assertRows(execute("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2"),
                   row(1, 2, 1, 1, 3),
                   row(2, 2, 3, 2, 3));

        // 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 queries with wildcard
        assertRows(execute("SELECT * FROM %s WHERE a = 1 GROUP BY a"),
                   row(1, 2, 1, 1, 3));

        assertRows(execute("SELECT * FROM %s WHERE a = 4 GROUP BY a, b"),
                   row(4, 8, 2, null, 12));

        // 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 queries with wildcard
        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a"),
                   row(1, 2, 1, 1, 3),
                   row(2, 2, 3, 2, 3),
                   row(3, null, null, 3, null),
                   row(4, 8, 2, null, 12));

        assertRows(execute("SELECT * FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b"),
                   row(1, 2, 1, 1, 3),
                   row(1, 4, 2, 1, 12),
                   row(2, 2, 3, 2, 3),
                   row(2, 4, 3, 2, 6),
                   row(3, null, null, 3, null),
                   row(4, 8, 2, null, 12));

        // 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
    {
        createTable("CREATE TABLE %s (a int, b int, c int, d int, e int, primary key (a, b, c, d))");

        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 queries with wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b, c", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(1, 2, 2, 6, 12),
                          row(1, 4, 2, 6, 12),
                          row(2, 2, 3, 3, 6),
                          row(2, 4, 3, 6, 12),
                          row(4, 8, 2, 12, 24));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(1, 4, 2, 6, 12),
                          row(2, 2, 3, 3, 6),
                          row(2, 4, 3, 6, 12),
                          row(4, 8, 2, 12, 24));

            // 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 queries with wildcard and with LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b, c LIMIT 3", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(1, 2, 2, 6, 12),
                          row(1, 4, 2, 6, 12));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b LIMIT 3", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(1, 4, 2, 6, 12),
                          row(2, 2, 3, 3, 6));

            // Range queries with wildcard and with PER PARTITION LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(1, 2, 2, 6, 12),
                          row(2, 2, 3, 3, 6),
                          row(2, 4, 3, 6, 12),
                          row(4, 8, 2, 12, 24));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(2, 2, 3, 3, 6),
                          row(4, 8, 2, 12, 24));

            // Range queries with wildcard, with PER PARTITION LIMIT and LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b, c PER PARTITION LIMIT 2 LIMIT 3", pageSize),
                          row(1, 2, 1, 3, 6),
                          row(1, 2, 2, 6, 12),
                          row(2, 2, 3, 3, 6));

            // 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 queries with wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c", pageSize),
                       row(1, 2, 1, 3, 6),
                       row(1, 2, 2, 6, 12),
                       row(1, 4, 2, 6, 12));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b", pageSize),
                       row(1, 2, 1, 3, 6),
                       row(1, 4, 2, 6, 12));

            // 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 wildcard and with LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c LIMIT 2", pageSize),
                       row(1, 2, 1, 3, 6),
                       row(1, 2, 2, 6, 12));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b LIMIT 1", pageSize),
                       row(1, 2, 1, 3, 6));

            // Single partition queries with wildcard and with PER PARTITION LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b, c PER PARTITION LIMIT 2", pageSize),
                       row(1, 2, 1, 3, 6),
                       row(1, 2, 2, 6, 12));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b PER PARTITION LIMIT 1", pageSize),
                       row(1, 2, 1, 3, 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 with wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b, c", pageSize),
                       row(1, 2, 1, 3, 6),
                       row(1, 2, 2, 6, 12),
                       row(1, 4, 2, 6, 12),
                       row(2, 2, 3, 3, 6),
                       row(2, 4, 3, 6, 12),
                       row(4, 8, 2, 12, 24));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a IN (1, 2, 4) GROUP BY a, b", pageSize),
                       row(1, 2, 1, 3, 6),
                       row(1, 4, 2, 6, 12),
                       row(2, 2, 3, 3, 6),
                       row(2, 4, 3, 6, 12),
                       row(4, 8, 2, 12, 24));

            // 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
    {
        createTable("CREATE TABLE %s (a int, b int, c int, d int, primary key (a, b, c))");

        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 wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b", pageSize),
                       row(1, null, null, 1, null),
                       row(2, null, null, 2, null),
                       row(4, null, null, 3, null ));

            // 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 query with wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a, b", pageSize),
                       row(1, null, null, 1, null));

            // 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 queries with wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(2, 2, 3, 2, 3),
                       row(4, 8, 2, null, 12),
                       row(3, null, null, 3, null));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(1, 4, 2, 1, 12),
                       row(2, 2, 3, 2, 3),
                       row(2, 4, 3, 2, 6),
                       row(4, 8, 2, null, 12),
                       row(3, null, null, 3, null));

            // 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 wildcard and with LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a LIMIT 2", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(2, 2, 3, 2, 3));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b LIMIT 10", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(1, 4, 2, 1, 12),
                       row(2, 2, 3, 2, 3),
                       row(2, 4, 3, 2, 6),
                       row(4, 8, 2, null, 12),
                       row(3, null, null, 3, null));

            // 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 wildcard and PER PARTITION LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(2, 2, 3, 2, 3),
                       row(4, 8, 2, null, 12),
                       row(3, null, null, 3, null));

            // 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 queries with wildcard, PER PARTITION LIMIT and LIMIT
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s GROUP BY a, b PER PARTITION LIMIT 1 LIMIT 2", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(2, 2, 3, 2, 3));

            // 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 wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 1 GROUP BY a", pageSize),
                       row(1, 2, 1, 1, 3));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a = 4 GROUP BY a, b", pageSize),
                       row(4, 8, 2, null, 12));

            // 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),
                          row(4, null, 1L, 0L));

            // Single partition queries with ORDER BY
            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 GROUP BY a, b ORDER BY b DESC, c DESC",
                                               pageSize),
                          row(2, 4, 2, 1L, 1L),
                          row(2, 2, 2, 1L, 1L));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 ORDER BY b DESC, c DESC",
                                               pageSize),
                          row(2, 4, 2, 2L, 2L));

            // Single partition queries with ORDER BY and LIMIT
            assertRowsNet(executeNetWithPaging("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",
                                               pageSize),
                          row(2, 4, 2, 1L, 1L));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a = 2 ORDER BY b DESC, c DESC LIMIT 2",
                                               pageSize),
                          row(2, 4, 2, 2L, 2L));

            // 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, 2, 1, 4L, 4L),
                          row(2, 2, 2, 2L, 2L),
                          row(3, null, 3, 0L, 1L),
                          row(4, 8, null, 1L, 0L));

            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, 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));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4)",
                                               pageSize),
                          row(1, 2, 1, 7L, 7L));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) AND b = 2 GROUP BY a, b",
                                               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 a IN (1, 2, 3, 4) AND b = 2",
                                               pageSize),
                          row(1, 2, 1, 3L, 3L));

            // Multi-partitions queries without aggregates
            assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a", pageSize),
                          row(1, 2, 1),
                          row(2, 2, 2),
                          row(3, null, 3),
                          row(4, 8, null));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b",
                                               pageSize),
                          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 queries with wildcard
            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(2, 2, 3, 2, 3),
                       row(3, null, null, 3, null),
                       row(4, 8, 2, null, 12));

            assertRowsNet(executeNetWithPaging("SELECT * FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b", pageSize),
                       row(1, 2, 1, 1, 3),
                       row(1, 4, 2, 1, 12),
                       row(2, 2, 3, 2, 3),
                       row(2, 4, 3, 2, 6),
                       row(3, null, null, 3, null),
                       row(4, 8, 2, null, 12));

            // Multi-partitions queries with LIMIT
            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) 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 WHERE a IN (1, 2, 3, 4) LIMIT 2",
                                               pageSize),
                          row(1, 2, 1, 7L, 7L));

            // Multi-partitions queries without aggregates and with LIMIT
            assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a LIMIT 2",
                                               pageSize),
                          row(1, 2, 1),
                          row(2, 2, 2));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b LIMIT 10",
                                               pageSize),
                          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 queries 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, b PER PARTITION LIMIT 1",
                                               pageSize),
                          row(1, 2, 1, 2L, 2L),
                          row(2, 2, 2, 1L, 1L),
                          row(3, null, 3, 0L, 1L),
                          row(4, 8, null, 1L, 0L));

            assertRowsNet(executeNetWithPaging("SELECT a, b, s, count(b), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a, b PER PARTITION LIMIT 3",
                                               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(3, null, 3, 0L, 1L),
                          row(4, 8, null, 1L, 0L));

            // Multi-partitions queries 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, b PER PARTITION LIMIT 1 LIMIT 3",
                                               pageSize),
                          row(1, 2, 1, 2L, 2L),
                          row(2, 2, 2, 1L, 1L),
                          row(3, 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 PER PARTITION LIMIT 3 LIMIT 10",
                                               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(3, null, 3, 0L, 1L),
                          row(4, 8, null, 1L, 0L));

            // Multi-partitions queries with DISTINCT
            assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a IN (1, 2, 3, 4) GROUP BY a",
                                               pageSize),
                          row(1, 1, 1L, 1L),
                          row(2, 2, 1L, 1L),
                          row(3, 3, 1L, 1L),
                          row(4, null, 1L, 0L));

            assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a IN (1, 2, 3, 4)",
                                               pageSize),
                          row(1, 1, 4L, 3L));

            // Multi-partitions query with DISTINCT and LIMIT
            assertRowsNet(executeNetWithPaging("SELECT DISTINCT a, s, count(a), count(s) FROM %s WHERE a IN (1, 2, 3, 4) 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 WHERE a IN (1, 2, 3, 4) LIMIT 2",
                                               pageSize),
                          row(1, 1, 4L, 3L));
        }
    }

    @Test
    public void testGroupByTimeRangesWithTimestamTypeAndWithoutPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, time timestamp, v int, primary key (pk, time))" + compactOption);

            assertInvalidMessage("Group by currently only support groups of columns following their declared order in the PRIMARY KEY",
                                 "SELECT pk, floor(time, 2h, '2016-09-01'), v FROM %s GROUP BY floor(time, 2h, '2016-09-01')");

            assertInvalidMessage("Only monotonic functions are supported in the GROUP BY clause. Got: system.floor : (timestamp, duration(constant), timestamp) -> timestamp",
                                 "SELECT pk, floor(time, 2h, time), v FROM %s GROUP BY pk, floor(time, 2h, time)");

            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:10:00 UTC', 1)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:12:00 UTC', 2)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:14:00 UTC', 3)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:15:00 UTC', 4)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:21:00 UTC', 5)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:22:00 UTC', 6)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:26:00 UTC', 7)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:26:20 UTC', 8)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-09-27 16:26:20 UTC', 10)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-09-27 16:30:00 UTC', 11)");

            // Test prepared statement
            assertRows(execute("SELECT pk, floor(time, 5m, ?), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m, ?)",
                               toTimestamp("2016-09-27 00:00:00 UTC"),
                               toTimestamp("2016-09-27 00:00:00 UTC")),
                       row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                       row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                       row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                       row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                       row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L),
                       row(2, toTimestamp("2016-09-27 16:30:00 UTC"), 11, 11, 1L));

            for (String startingTime : new String[]{"", ", '2016-09-27 UTC'"})
            {
                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ")"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L),
                           row(2, toTimestamp("2016-09-27 16:30:00 UTC"), 11, 11, 1L));

                // Checks with duration lower than precisions
                assertInvalidMessage("The floor cannot be computed for the 10us duration as precision is below 1 millisecond",
                                     "SELECT pk, floor(time, 10us" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 10us" + startingTime + ")");

                // Checks with a negative duration
                assertInvalidMessage("Negative durations are not supported by the floor function",
                                     "SELECT pk, floor(time, 10us" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, -5m" + startingTime + ")");

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") LIMIT 2"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L));

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") PER PARTITION LIMIT 1"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L));

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC"),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L));

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC LIMIT 2"),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L));
            }

            // Checks with start time is greater than the timestamp
            assertInvalidMessage("The floor function starting time is greater than the provided time",
                                 "SELECT pk, floor(time, 5m, '2016-10-27'), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m, '2016-10-27')");
        }
    }

    @Test
    public void testGroupByTimeRangesWithTimeUUIDAndWithoutPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, time timeuuid, v int, primary key (pk, time))" + compactOption);

            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:10:00"), 1);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:12:00"), 2);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:14:00"), 3);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:15:00"), 4);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:21:00"), 5);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:22:00"), 6);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:26:00"), 7);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:26:20"), 8);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 2, toTimeUUID("2016-09-27 16:26:00"), 10);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 2, toTimeUUID("2016-09-27 16:30:00"), 11);

            for (String startingTime : new String[]{"", ", '2016-09-27 UTC'"})
            {
                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ")"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L),
                           row(2, toTimestamp("2016-09-27 16:30:00 UTC"), 11, 11, 1L));

                assertRows(execute("SELECT pk, floor(to_timestamp(time), 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(to_timestamp(time), 5m" + startingTime + ")"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L),
                           row(2, toTimestamp("2016-09-27 16:30:00 UTC"), 11, 11, 1L));

                // Checks with duration lower than precisions
                assertInvalidMessage("The floor cannot be computed for the 10us duration as precision is below 1 millisecond",
                                     "SELECT pk, floor(time, 10us" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 10us" + startingTime + ")");

                // Checks with a negative duration
                assertInvalidMessage("Negative durations are not supported by the floor function",
                                     "SELECT pk, floor(time, 10us" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, -5m" + startingTime + ")");

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") LIMIT 2"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L));

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") PER PARTITION LIMIT 1"),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                           row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L));

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC"),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                           row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                           row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L));

                assertRows(execute("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC LIMIT 2"),
                           row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                           row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L));
            }

            // Checks with start time is greater than the timestamp
            assertInvalidMessage("The floor function starting time is greater than the provided time",
                                 "SELECT pk, floor(time, 5m, '2016-10-27'), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m, '2016-10-27')");
        }
    }

    @Test
    public void testGroupByTimeRangesWithDateTypeAndWithoutPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, time date, v int, primary key (pk, time))" + compactOption);

            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27', 1)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-28', 2)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-29', 3)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-30', 4)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-10-01', 5)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-10-04', 6)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-10-20', 7)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-11-27', 8)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-11-01', 10)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-11-02', 11)");

            for (String startingTime : new String[]{"", ", '2016-06-01'"})
            {
                assertRows(execute("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo" + startingTime + ")"),
                           row(1, toDate("2016-09-01"), 1, 4, 4L),
                           row(1, toDate("2016-10-01"), 5, 7, 3L),
                           row(1, toDate("2016-11-01"), 8, 8, 1L),
                           row(2, toDate("2016-11-01"), 10, 11, 2L));

                // Checks with duration lower than precisions
                assertInvalidMessage("The floor on date values cannot be computed for the 1h duration as precision is below 1 day",
                                     "SELECT pk, floor(time, 1h" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1h" + startingTime + ")");

                // Checks with a negative duration
                assertInvalidMessage("Negative durations are not supported by the floor function",
                                     "SELECT pk, floor(time, -1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, -1mo" + startingTime + ")");

                assertRows(execute("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo" + startingTime + ") LIMIT 2"),
                           row(1, toDate("2016-09-01"), 1, 4, 4L),
                           row(1, toDate("2016-10-01"), 5, 7, 3L));

                assertRows(execute("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo" + startingTime + ") PER PARTITION LIMIT 1"),
                           row(1, toDate("2016-09-01"), 1, 4, 4L),
                           row(2, toDate("2016-11-01"), 10, 11, 2L));

                assertRows(execute("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 1mo" + startingTime + ") ORDER BY time DESC"),
                           row(1, toDate("2016-11-01"), 8, 8, 1L),
                           row(1, toDate("2016-10-01"), 5, 7, 3L),
                           row(1, toDate("2016-09-01"), 1, 4, 4L));

                assertRows(execute("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 1mo" + startingTime + ") ORDER BY time DESC LIMIT 2"),
                           row(1, toDate("2016-11-01"), 8, 8, 1L),
                           row(1, toDate("2016-10-01"), 5, 7, 3L));
            }

            // Checks with start time is greater than the timestamp
            assertInvalidMessage("The floor function starting time is greater than the provided time",
                                 "SELECT pk, floor(time, 1mo, '2017-01-01'), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo, '2017-01-01')");
        }
    }

    @Test
    public void testGroupByTimeRangesWithTimeTypeAndWithoutPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, date date, time time, v int, primary key (pk, date, time))" + compactOption);

            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:10:00', 1)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:12:00', 2)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:14:00', 3)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:15:00', 4)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:21:00', 5)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:22:00', 6)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:26:00', 7)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:26:20', 8)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-28', '16:26:20', 9)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-28', '16:26:30', 10)");

            assertInvalidMessage("Functions are only supported on the last element of the GROUP BY clause",
                                 "SELECT pk, floor(date, 1w), time, min(v), max(v), count(v) FROM %s GROUP BY pk, floor(date, 1w), time");

            assertRows(execute("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s GROUP BY pk, date, floor(time, 5m)"),
                       row(1, toDate("2016-09-27"), toTime("16:10:00"), 1, 3, 3L),
                       row(1, toDate("2016-09-27"), toTime("16:15:00"), 4, 4, 1L),
                       row(1, toDate("2016-09-27"), toTime("16:20:00"), 5, 6, 2L),
                       row(1, toDate("2016-09-27"), toTime("16:25:00"), 7, 8, 2L),
                       row(1, toDate("2016-09-28"), toTime("16:25:00"), 9, 10, 2L));

            // Checks with duration greater than dayprecisions
            assertInvalidMessage("For time values, the floor can only be computed for durations smaller that a day",
                                 "SELECT pk, date, floor(time, 10d), min(v), max(v), count(v) FROM %s GROUP BY pk, date, floor(time, 10d)");

            // Checks with a negative duration
            assertInvalidMessage("Negative durations are not supported by the floor function",
                                 "SELECT pk, date, floor(time, -10m), min(v), max(v), count(v) FROM %s GROUP BY pk, date, floor(time, -10m)");

            assertRows(execute("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s GROUP BY pk, date, floor(time, 5m) LIMIT 2"),
                       row(1, toDate("2016-09-27"), toTime("16:10:00"), 1, 3, 3L),
                       row(1, toDate("2016-09-27"), toTime("16:15:00"), 4, 4, 1L));

            assertRows(execute("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, date, floor(time, 5m) ORDER BY date DESC, time DESC"),
                       row(1, toDate("2016-09-28"), toTime("16:25:00"), 9, 10, 2L),
                       row(1, toDate("2016-09-27"), toTime("16:25:00"), 7, 8, 2L),
                       row(1, toDate("2016-09-27"), toTime("16:20:00"), 5, 6, 2L),
                       row(1, toDate("2016-09-27"), toTime("16:15:00"), 4, 4, 1L),
                       row(1, toDate("2016-09-27"), toTime("16:10:00"), 1, 3, 3L));

            assertRows(execute("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, date, floor(time, 5m) ORDER BY date DESC, time DESC LIMIT 2"),
                       row(1, toDate("2016-09-28"), toTime("16:25:00"), 9, 10, 2L),
                       row(1, toDate("2016-09-27"), toTime("16:25:00"), 7, 8, 2L));
        }
    }

    @Test
    public void testGroupByTimeRangesWithTimestampTypeAndPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, time timestamp, v int, primary key (pk, time))"
                    + compactOption);

            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:10:00 UTC', 1)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:12:00 UTC', 2)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:14:00 UTC', 3)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:15:00 UTC', 4)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:21:00 UTC', 5)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:22:00 UTC', 6)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:26:00 UTC', 7)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27 16:26:20 UTC', 8)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-09-27 16:26:20 UTC', 10)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-09-27 16:30:00 UTC', 11)");

            for (int pageSize = 1; pageSize < 10; pageSize++)
            {
                for (String startingTime : new String[]{"", ", '2016-09-27 UTC'"})
                {
                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ")", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                                  row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                                  row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                                  row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                                  row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L),
                                  row(2, toTimestamp("2016-09-27 16:30:00 UTC"), 11, 11, 1L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") LIMIT 2", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                                  row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") PER PARTITION LIMIT 1", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                                  row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                                  row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                                  row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC LIMIT 2", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                                  row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L));
                }
            }
        }
    }

    @Test
    public void testGroupByTimeRangesWithTimeUUIDAndPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, time timeuuid, v int, primary key (pk, time))" + compactOption);

            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:10:00"), 1);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:12:00"), 2);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:14:00"), 3);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:15:00"), 4);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:21:00"), 5);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:22:00"), 6);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:26:00"), 7);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 1, toTimeUUID("2016-09-27 16:26:20"), 8);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 2, toTimeUUID("2016-09-27 16:26:00"), 10);
            execute("INSERT INTO %s (pk, time, v) VALUES (?, ?, ?)", 2, toTimeUUID("2016-09-27 16:30:00"), 11);

            for (int pageSize = 1; pageSize < 10; pageSize++)
            {
                for (String startingTime : new String[]{"", ", '2016-09-27 UTC'"})
                {
                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ")", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                                  row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                                  row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                                  row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                                  row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L),
                                  row(2, toTimestamp("2016-09-27 16:30:00 UTC"), 11, 11, 1L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") LIMIT 2", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                                  row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 5m" + startingTime + ") PER PARTITION LIMIT 1", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L),
                                  row(2, toTimestamp("2016-09-27 16:25:00 UTC"), 10, 10, 1L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                                  row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L),
                                  row(1, toTimestamp("2016-09-27 16:15:00 UTC"), 4, 4, 1L),
                                  row(1, toTimestamp("2016-09-27 16:10:00 UTC"), 1, 3, 3L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 5m" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 5m" + startingTime + ") ORDER BY time DESC LIMIT 2", pageSize),
                                  row(1, toTimestamp("2016-09-27 16:25:00 UTC"), 7, 8, 2L),
                                  row(1, toTimestamp("2016-09-27 16:20:00 UTC"), 5, 6, 2L));
                }
            }
        }
    }

    @Test
    public void testGroupByTimeRangesWithDateTypeAndPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, time date, v int, primary key (pk, time))" + compactOption);

            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-27', 1)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-28', 2)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-29', 3)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-09-30', 4)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-10-01', 5)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-10-04', 6)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-10-20', 7)");
            execute("INSERT INTO %s (pk, time, v) VALUES (1, '2016-11-27', 8)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-11-01', 10)");
            execute("INSERT INTO %s (pk, time, v) VALUES (2, '2016-11-02', 11)");

            for (int pageSize = 1; pageSize < 10; pageSize++)
            {
                for (String startingTime : new String[]{"", ", '2016-06-01'"})
                {
                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo" + startingTime + ")", pageSize),
                                  row(1, toLocalDate("2016-09-01"), 1, 4, 4L),
                                  row(1, toLocalDate("2016-10-01"), 5, 7, 3L),
                                  row(1, toLocalDate("2016-11-01"), 8, 8, 1L),
                                  row(2, toLocalDate("2016-11-01"), 10, 11, 2L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo" + startingTime + ") LIMIT 2", pageSize),
                                  row(1, toLocalDate("2016-09-01"), 1, 4, 4L),
                                  row(1, toLocalDate("2016-10-01"), 5, 7, 3L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s GROUP BY pk, floor(time, 1mo" + startingTime + ") PER PARTITION LIMIT 1", pageSize),
                                  row(1, toLocalDate("2016-09-01"), 1, 4, 4L),
                                  row(2, toLocalDate("2016-11-01"), 10, 11, 2L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 1mo" + startingTime + ") ORDER BY time DESC", pageSize),
                                  row(1, toLocalDate("2016-11-01"), 8, 8, 1L),
                                  row(1, toLocalDate("2016-10-01"), 5, 7, 3L),
                                  row(1, toLocalDate("2016-09-01"), 1, 4, 4L));

                    assertRowsNet(executeNetWithPaging("SELECT pk, floor(time, 1mo" + startingTime + "), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, floor(time, 1mo" + startingTime + ") ORDER BY time DESC LIMIT 2", pageSize),
                                  row(1, toLocalDate("2016-11-01"), 8, 8, 1L),
                                  row(1, toLocalDate("2016-10-01"), 5, 7, 3L));
                }
            }
        }
    }

    @Test
    public void testGroupByTimeRangesWithTimeTypeAndPaging() throws Throwable
    {
        for (String compactOption : new String[] { "", " WITH COMPACT STORAGE" })
        {
            createTable("CREATE TABLE %s (pk int, date date, time time, v int, primary key (pk, date, time))" + compactOption);

            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:10:00', 1)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:12:00', 2)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:14:00', 3)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:15:00', 4)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:21:00', 5)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:22:00', 6)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:26:00', 7)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-27', '16:26:20', 8)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-28', '16:26:20', 9)");
            execute("INSERT INTO %s (pk, date, time, v) VALUES (1, '2016-09-28', '16:26:30', 10)");

            for (int pageSize = 1; pageSize < 10; pageSize++)
            {
                assertRowsNet(executeNetWithPaging("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s GROUP BY pk, date, floor(time, 5m)", pageSize),
                              row(1, toLocalDate("2016-09-27"), toTime("16:10:00"), 1, 3, 3L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:15:00"), 4, 4, 1L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:20:00"), 5, 6, 2L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:25:00"), 7, 8, 2L),
                              row(1, toLocalDate("2016-09-28"), toTime("16:25:00"), 9, 10, 2L));

                assertRowsNet(executeNetWithPaging("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s GROUP BY pk, date, floor(time, 5m) LIMIT 2", pageSize),
                              row(1, toLocalDate("2016-09-27"), toTime("16:10:00"), 1, 3, 3L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:15:00"), 4, 4, 1L));

                assertRowsNet(executeNetWithPaging("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, date, floor(time, 5m) ORDER BY date DESC, time DESC", pageSize),
                              row(1, toLocalDate("2016-09-28"), toTime("16:25:00"), 9, 10, 2L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:25:00"), 7, 8, 2L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:20:00"), 5, 6, 2L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:15:00"), 4, 4, 1L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:10:00"), 1, 3, 3L));

                assertRowsNet(executeNetWithPaging("SELECT pk, date, floor(time, 5m), min(v), max(v), count(v) FROM %s WHERE pk = 1 GROUP BY pk, date, floor(time, 5m) ORDER BY date DESC, time DESC LIMIT 2", pageSize),
                              row(1, toLocalDate("2016-09-28"), toTime("16:25:00"), 9, 10, 2L),
                              row(1, toLocalDate("2016-09-27"), toTime("16:25:00"), 7, 8, 2L));
            }
        }
    }

    private static UUID toTimeUUID(String string)
    {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        LocalDateTime dateTime = LocalDateTime.parse(string, formatter);
        long timeInMillis = dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();
        return TimeUUID.Generator.atUnixMillis(timeInMillis).asUUID();
    }

    private static Date toTimestamp(String timestampAsString)
    {
        return new Date(TimestampSerializer.dateStringToTimestamp(timestampAsString));
    }

    private static int toDate(String dateAsString)
    {
        return SimpleDateSerializer.dateStringToDays(dateAsString);
    }

    private static LocalDate toLocalDate(String dateAsString)
    {
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd");
        LocalDateTime dateTime = java.time.LocalDate.parse(dateAsString, formatter).atStartOfDay();
        long timeInMillis = dateTime.toInstant(ZoneOffset.UTC).toEpochMilli();

        return LocalDate.fromMillisSinceEpoch(timeInMillis);
    }

    private static long toTime(String timeAsString)
    {
        return TimeSerializer.timeStringToLong(timeAsString);
    }
}
