LIMIT offset, rowcount or LIMIT rowcount OFFSET offset won't give the expected result, because it is not only applied on the shard nodes, but also on the head node.
Thus, when e.g. applying LIMIT 100, 5 on a system with 10 shards, 10x5 rows will be collected at the head node and reapplying LIMIT 100,5 will return nothing. It would make more sense to not reapply LIMIT on the head node, if there is an OFFSET given.
Yet in fact, OFFSET for sharded databases doesn't make much sense anyway and I vote for not supporting it at all, e.g. by return an error if a LIMIT clause with OFFSET is used in a query.
LIMIT offset, rowcount
orLIMIT rowcount OFFSET offset
won't give the expected result, because it is not only applied on the shard nodes, but also on the head node.Thus, when e.g. applying
LIMIT 100, 5
on a system with 10 shards, 10x5 rows will be collected at the head node and reapplyingLIMIT 100,5
will return nothing. It would make more sense to not reapply LIMIT on the head node, if there is an OFFSET given.Yet in fact, OFFSET for sharded databases doesn't make much sense anyway and I vote for not supporting it at all, e.g. by return an error if a LIMIT clause with OFFSET is used in a query.