[CASSANDRA-13302] last row of previous page == first row of next page while querying data using SASI index

• Type: Bug
• Status: Resolved
• Priority: Normal
• Resolution: Fixed
• Fix Version/s: 3.11.7, 4.0-alpha1, 4.0

Environment

Tested with C* 3.9 and 3.10.

• Severity: Normal
• Assignee: Alex Petrov
• Reporter: Andy Tolbert

Authors

Alex Petrov

Reviewers

Andres de la Peña

• Created: 06/Mar/17 16:03
• Updated: 01/Aug/21 11:18
• Resolved: 12/May/17 07:53

Description

Apologies if this is a duplicate (couldn't track down an existing bug).

Similarly to CASSANDRA-11208, it appears it is possible to retrieve duplicate rows when paging using a SASI index as documented in JAVA-1413, the following test demonstrates that data is repeated while querying using a SASI index:

public class TestPagingBug
{
	public static void main(String[] args)
	{
		Cluster.Builder builder = Cluster.builder();
		Cluster c = builder.addContactPoints("192.168.98.190").build();		
		Session s = c.connect();
		
		s.execute("CREATE KEYSPACE IF NOT EXISTS test WITH replication = { 'class' : 'SimpleStrategy', 'replication_factor' : 3 }");
		s.execute("CREATE TABLE IF NOT EXISTS test.test_table_sec(sec BIGINT PRIMARY KEY, id INT)");
                //create secondary index on ID column, used for select statement
                String index = "CREATE CUSTOM INDEX test_table_sec_idx ON test.test_table_sec (id) USING 'org.apache.cassandra.index.sasi.SASIIndex' "
                + "WITH OPTIONS = { 'mode': 'PREFIX' }";
                s.execute(index);
		
		PreparedStatement insert = s.prepare("INSERT INTO test.test_table_sec (id, sec) VALUES (1, ?)");		
		for (int i = 0; i < 1000; i++)
			s.execute(insert.bind((long) i));
		
		PreparedStatement select = s.prepare("SELECT sec FROM test.test_table_sec WHERE id = 1");
		
		long lastSec = -1;		
		for (Row row : s.execute(select.bind().setFetchSize(300)))
		{
			long sec = row.getLong("sec");
			if (sec == lastSec)
				System.out.println(String.format("Duplicated id %d", sec));
			
			lastSec = sec;
		}
		System.exit(0);
	}
}

The program outputs the following:

Duplicated id 23
Duplicated id 192
Duplicated id 684

Note that the simple primary key is required to reproduce this.

Issue Links

• breaks: CASSANDRA-13332 wrong number of rows in cqlsh
• duplicates: CASSANDRA-13379 SASI index returns duplicate rows
• is related to: CASSANDRA-11208 Paging is broken for IN queries