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

Question

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.

If you want anyone to be able to help you, you might want to say what the "weird error" actually was. — Victor, Jul 18 '23 at 15:21
It's already described in the question: `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.` — ffleandro, Jul 18 '23 at 15:23
Does the query work with the AND condition but without the ORDER BY? — jarmod, Jul 18 '23 at 18:40
And try `timestamp BETWEEN 1689552000000 AND 1689724799999`. — jarmod, Jul 18 '23 at 18:43
@jarmod that was perfect. BETWEEN works like a charm, and to answer your question, it worked without the ORDER BY, it was the last example in my original question. — ffleandro, Jul 18 '23 at 19:06
@jarmod Do you want to answer this question so that I can mark your answer as Correct? — ffleandro, Jul 18 '23 at 19:07
Out of interest, can you retry your query as follows: `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`. Note that I've surrounded the device ID value with single quotes instead of double quotes. — jarmod, Jul 19 '23 at 19:33

jarmod · Accepted Answer · 2023-07-18T19:56:35.430

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.

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

1 Answers1