CODEWITHSUNDEEP
amazon-web-servicesamazon-dynamodbpartiql
ffleandro
ffleandro

Reputation: 4041

DynamoDB: can't use multiple AND operators combined with ORDER BY on sort key

I believe I've found a bug in DynamoDB using PartiQL.

Given this table:

  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"
    },
  ]

I can't use the following PartiQL SELECT Statement:

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

Throws the following error:

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.

I believe this is a Bug because the following PartiQL SELECT Statements work:

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

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

So by putting multiple AND operators in the Sort key and using ORDER BY, we get the above error.

Upvotes: 0

Views: 173

Answers (1)

jarmod
jarmod

Reputation: 78842

Your original query was:

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

I can't immediately explain why the range condition doesn't work, perhaps it's a PartiQL bug, but the following works:

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

Note that BETWEEN X AND Y is inclusive of both X and Y values.

Upvotes: 1

Related Questions