Limit Operator

[Michael Alexeev]

Hi All,

It looks like PlanAssembler::handleLimitOperator sets Limit/Offset
parameter index (if there is one) only for a coordinator node and not
for a distributed node. For the latter only the limit expression is
getting set. Is it an oversight?

The LimitPlanNode::getLimitAndOffsetByReference relies on limit/offset
parameter index to be set (not equal to -1) to check limit/offset
values to be positive.
if (getLimitParamIdx() != -1) {
limit = ValuePeeker::peekInteger(params[getLimitParamIdx()]);
if (limit < 0) {
throw SQLException(SQLException::data_exception_invalid_parameter,
"Negative parameter to LIMIT");
}

Thanks,
Mike

[Paul Martel]

This is on purpose. Assume that the query result has a well-defined order.
If I want rows 21 through 30 from a multi-partition table, those rows could
theoretically happen to be rows 21 through 30 from some partition
-- maybe a partition that happens to contain all of the early rows -- or it may be rows 1 through 10 from some partition
(all earlier rows coming from other partitions).
The only safe assumption is that no individual partition has to produce more than its first "limit + offset" total rows, so long as it produces those rows in the correct order.

[mike alexeev]

Paul, understood. But then there is an inconsistency in the limit node/executor behavior in case of negative limit/offset values. LimitPlanNode::getLimitAndOffsetByReference will throw if it's on the coordinator node and quietly set limit/offset to negative values for the distributed node. The executor code still needs to be prepared to handle negative values. Why not always check for negative values within LimitPlanNode::getLimitAndOffsetByReference regardless whether limit/offset parameter index is set or not? 

 

Mike