[CASSANDRA-11223] Queries with LIMIT filtering on clustering columns can return less rows than expected

• Type: Bug
• Status: Resolved
• Priority: Normal
• Resolution: Fixed
• Fix Version/s: 2.2.11, 3.0.15, 3.11.1, 4.0-alpha1, 4.0
• Component/s: Legacy/Local Write-Read Paths
• Bug Category: Correctness - API / Semantic Implementation
• Severity: Normal
• Since Version: 3.0.0
• Assignee: Benjamin Lerer
• Reporter: Benjamin Lerer

Authors

Benjamin Lerer

Reviewers

Stefania Alborghetti

• Created: 24/Feb/16 14:49
• Updated: 15/May/20 08:02
• Resolved: 02/Aug/17 02:27

Description

A query like SELECT * FROM %s WHERE b = 1 LIMIT 2 ALLOW FILTERING can return less row than expected if the table has some static columns and some of the partition have no rows matching b = 1.

The problem can be reproduced with the following unit test:

    public void testFilteringOnClusteringColumnsWithLimitAndStaticColumns() throws Throwable
    {
        createTable("CREATE TABLE %s (a int, b int, s int static, c int, primary key (a, b))");

        for (int i = 0; i < 3; i++)
        {
            execute("INSERT INTO %s (a, s) VALUES (?, ?)", i, i);
                for (int j = 0; j < 3; j++)
                    if (!(i == 0 && j == 1))
                        execute("INSERT INTO %s (a, b, c) VALUES (?, ?, ?)", i, j, i + j);
        }

        assertRows(execute("SELECT * FROM %s"),
                   row(1, 0, 1, 1),
                   row(1, 1, 1, 2),
                   row(1, 2, 1, 3),
                   row(0, 0, 0, 0),
                   row(0, 2, 0, 2),
                   row(2, 0, 2, 2),
                   row(2, 1, 2, 3),
                   row(2, 2, 2, 4));

        assertRows(execute("SELECT * FROM %s WHERE b = 1 ALLOW FILTERING"),
                   row(1, 1, 1, 2),
                   row(2, 1, 2, 3));

        assertRows(execute("SELECT * FROM %s WHERE b = 1 LIMIT 2 ALLOW FILTERING"),
                   row(1, 1, 1, 2),
                   row(2, 1, 2, 3)); // <-------- FAIL It returns only one row because the static row of partition 0 is counted and filtered out in SELECT statement
    }