sql: incorrect number of rows when we have an offset over a limit

[ridwanmsharif]

Out limit doesn't seem to be right when we have an offset above it.
Consider the following table:

  col
+----+
   1
   2
   3
   4
   5
   6
   7
   8
   9
  10
(10 rows)

And the query:

SELECT * FROM (SELECT * FROM tab ORDER BY col LIMIT 5) ORDER BY a OFFSET 3

We expect to see (which is what postgres returns)

  4
  5
(2 rows)

But instead we get:

  4
  5
  6
  7
  8
(5 rows)

[jordanlewis]

Thanks. This looks like a bug in distsql physical planning.

Explain plans for your scenario:

root@127.0.0.1:51341/defaultdb> explain SELECT * FROM (SELECT * FROM tab ORDER BY a LIMIT 5) ORDER BY a OFFSET 3;
    tree    | field  | description
+-----------+--------+-------------+
  limit     |        |
   │        | offset | 3
   └── scan |        |
            | table  | tab@primary
            | spans  | ALL
            | limit  | 5
(6 rows)

Time: 541µs

root@127.0.0.1:51341/defaultdb> explain(distsql) SELECT * FROM (SELECT * FROM tab ORDER BY a LIMIT 5) ORDER BY a OFFSET 3;
  automatic |                                                                                                                                                                url
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
    true    | https://cockroachdb.github.io/distsqlplan/decode.html#eJyMjzFLBDEUhHt_xTL1E3cRm1S2B-LJaScpcpt3R2A3CXlvQTny3-WSQiyEK-ebZGbeBTF5fnUrC8wnJlhCLmlmkVSuqD_Y-S-YkRBi3vSKLWFOhWEu0KALw-DDHRc-sPNcHkYQPKsLS4vNJayufD-rO4Lwnl0UM9yD8BLWoMPTsD-dhHV4hK2EtOlvi6g7M8xU6fYlB5acovCfEf8lj9US2J-5XytpKzO_lTS3mi737V8DnkW7O3Wxi92qtt79BAAA___H2G4Y
(1 row)

Time: 9.758ms

Note that the physical plan combines the offset and limit in a bogus way.