DynamoDB PartiQL问题：排序键多AND条件结合ORDER BY触发报错

表结构定义 #

name      = "my-table"
hash_key  = "device_id"
range_key = "message_id"

attributes = [
  { name = "device_id", type = "S" },
  { name = "message_id", type = "S" },
  { name = "timestamp", type = "N" },
]

local_secondary_indexes = [
  {
    name            = "device-id-timestamp-local-index"
    hash_key        = "device_id"
    range_key       = "timestamp"
    projection_type = "ALL"
  },
]

问题场景 #

执行包含排序键多范围条件+ORDER BY的PartiQL查询时触发错误：

SELECT * FROM "my-table"."device-id-timestamp-local-index" WHERE device_id = "91f66b0e-1565-431b-aa4a-5db301af9510" AND "timestamp" >= 1689552000000 AND "timestamp" <= 1689724799999 ORDER BY "timestamp" DESC

报错信息 #

An error occurred (ValidationException) when calling the ExecuteStatement operation: Must have at least one non-optional hash key condition in WHERE clause when using ORDER BY clause.

对比验证 #

以下两种场景的查询均可正常执行：

1. 排序键单范围条件+ORDER BY：

SELECT * FROM "my-table"."device-id-timestamp-local-index" WHERE device_id = "91f66b0e-1565-431b-aa4a-5db301af9510" AND "timestamp" >= 1689552000000 ORDER BY "timestamp" DESC

2. 排序键多范围条件但无ORDER BY：

SELECT * FROM "my-table"."device-id-timestamp-local-index" WHERE device_id = "91f66b0e-1565-431b-aa4a-5db301af9510" AND "timestamp" >= 1689552000000 AND "timestamp" <= 1689724799999

结论 #

当针对DynamoDB本地二级索引使用PartiQL查询，同时满足以下两个条件时会触发ValidationException错误：

• 排序键（timestamp）同时使用>=和<=的多范围条件
• 查询包含ORDER BY子句

内容的提问来源于stack exchange，提问作者ffleandro