Describe the problem

In v19.2.1 the pagination on an ordered query continues from top after 1024 results whenever a LIMIT is set.

So, a query with OFFSET 1024 will return all results starting from the 1025th till the end (as expected), but as soon as I introduce a LIMIT of any size, the first returned result will be the first as if I had no offset. Sorting DESC makes things worse...

The problem does not appear right away, but takes a minute or so to manifest for new data.

To Reproduce

1. Upgrade 3-node CockroachDB cluster (Kubernetes) to v19.2.1.
2. Create a complex table with more than 1024 values as follows:

db.ExecContext(ctx, "CREATE TABLE test (i INT)")
for i := 0; i < 1026; i++ {
  db.ExecContext(ctx, "INSERT INTO test (i) values ($1)", i)
}

3. Wait. The problem only appears after about a minute or so.

4. Run following queries:

root@cockroachdb-public:26257/db> SELECT * FROM test ORDER BY i DESC OFFSET 1020;
  i  
+---+
  5  
  4  
  3  
  2  
  1  
  0  
(6 rows)

Time: 9.5643ms

root@cockroachdb-public:26257/db> SELECT * FROM test ORDER BY i DESC OFFSET 1020 LIMIT 10;
   i    
+------+
     5  
     4  
     3  
     2  
  1025  
  1024  
(6 rows)

Time: 4.090952ms

root@cockroachdb-public:26257/db> SELECT * FROM test ORDER BY i ASC OFFSET 1020;

   i    
+------+
  1020  
  1021  
  1022  
  1023  
  1024  
  1025  
(6 rows)

Time: 4.24624ms

root@cockroachdb-public:26257/db> SELECT * FROM test ORDER BY i ASC OFFSET 1020 LIMIT 10;

   i    
+------+
  1020  
  1021  
  1022  
  1023  
     0  
     1  
(6 rows)

Time: 4.370304ms

5. Expect different output

Note: Downgrading to v19.2.0 makes the problem go away.

Expected behavior

I would expect ordered result sets to not jump to some other values...

Environment:

• CockroachDB version v19.2.1
• Client app: (any)

Additional context

This is the "secure" 3-node CockroachDB cluster on Kubernetes.