ExecuteStatement with Limit on DynamoDB Local returns wrong result set when using descending ordering

Question

When executing a PartiQL SELECT statement with descending ordering and the Limit field set, DynamoDB Local returns different results than the live service. The local version returns the last n records of the scan (after ordering) whereas the live service returns the first n records. Ascending ordering works as expected as does an unlimited SELECT.

### MyTable
| Hash | Range |
| --- | --- |
| Something | 1 |
| Something | 2 |
| Something | 3 |
| Something | 4 |
| Something | 5 |

### Statement (with Limit = 3)
`SELECT * FROM MyTable WHERE Hash='Something' ORDER BY Range DESC`

### Expected Results (what happens on the live service)
| Hash | Range |
| --- | --- |
| Something | 5 |
| Something | 4 |
| Something | 3 |

### Actual Results on DynamoDB Local v2.5.4
| Hash | Range |
| --- | --- |
| Something | 3 |
| Something | 2 |
| Something | 1 |

Leeroy Hannigan · Answer

DynamoDB Local does not support the `Limit` parameter for ExecuteStatement which is likely why you see a discrepancy

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/DynamoDBLocal.UsageNotes.html#DynamoDBLocal.Differences

re:Post Agent · Answer

The behavior you're describing appears to be a discrepancy between DynamoDB Local and the live DynamoDB service when using PartiQL SELECT statements with descending ordering and a Limit parameter. This is likely a bug in the DynamoDB Local implementation.

In the live DynamoDB service, when using PartiQL SELECT statements with ORDER BY and LIMIT clauses, the expected behavior is to return the first n records after applying the ordering, which is what you're seeing in the live service results.

For your query:
`SELECT * FROM MyTable WHERE Hash='Something' ORDER BY Range DESC LIMIT 3`

The correct result set should indeed be:
| Hash | Range |
| --- | --- |
| Something | 5 |
| Something | 4 |
| Something | 3 |

This is because the records are first ordered in descending order by the Range key, and then the top 3 results are returned due to the LIMIT clause.

The behavior you're seeing in DynamoDB Local, where it returns the last 3 records after ordering, is incorrect and inconsistent with the live service. This kind of discrepancy can cause issues when developing and testing applications locally before deploying to the live environment.

It's important to note that while DynamoDB Local is a useful tool for development and testing, it may not always perfectly mimic the behavior of the live DynamoDB service. In cases like this where you encounter inconsistencies, it's best to rely on the behavior of the live service as the correct implementation.

If you're developing an application that depends on this behavior, you should consider testing critical queries against the live DynamoDB service to ensure correct functionality. You may also want to report this issue to AWS support or through the AWS forums to bring it to their attention for potential fixes in future versions of DynamoDB Local.  
**Sources**  
[ExecuteStatement - Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/APIReference/API_ExecuteStatement.html)  
[PartiQL select statements for DynamoDB - Amazon DynamoDB](https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/ql-reference.select.html)

ExecuteStatement with Limit on DynamoDB Local returns wrong result set when using descending ordering

MyTable

Statement (with Limit = 3)

Expected Results (what happens on the live service)

Actual Results on DynamoDB Local v2.5.4

Add your answer

Relevant content