DynamoDB's PartiQL reads some comparisons backwards

I've been working through a list of places where Dynoxide answers differently from DynamoDB. The method is dull and it works: send the same request to both, and where they disagree, find out who's right. Nearly always it's DynamoDB, because DynamoDB is the thing I'm copying.

This week I hit one where I don't think it is.

Two ways of writing the same condition

Take a table with a partition key pk and a numeric sort key sk, holding five items under pk = 'a' with sort keys 1 to 5. Ask PartiQL for the ones above 3:

SELECT sk FROM "sk-order-test" WHERE pk = 'a' AND sk > 3

You get 4 and 5. Now write the same condition the other way round, with the number first:

SELECT sk FROM "sk-order-test" WHERE pk = 'a' AND 3 < sk

You get 1 and 2.

3 < sk and sk > 3 mean the same thing, and DynamoDB returns opposite halves of the partition for them. There's no error and no warning. It runs, and it hands back the wrong items.

Here's the full set I ran, through the plain AWS CLI. Each item also has an attribute called copy holding the same number as its sort key, which turns out to matter.

The last three rows are the interesting ones. The same comparison on copy, which isn't a key, is right. The same comparison on the sort key is right too when there's no partition key in the statement, which makes it a scan.

What I think is happening

It only goes wrong when DynamoDB can use the comparison as a key condition - partition key given, sort key compared. My guess is that when it lifts the comparison out of the WHERE clause to build the query, it takes the operator as written and puts the sort key on the left, without flipping the operator to match. So 3 < sk becomes sk < 3.

That's a guess from the outside. What I can say is that it fits everything I threw at it:

• It happens with a string sort key as well as a number.
• It happens through a global secondary index, on the index's own sort key.
• It happens with a ? parameter in place of the literal.
• It doesn't happen under NOT or OR, or when another condition in the statement already uses the sort key. In those cases the comparison is a filter, and filters get it right.
• = and <> are fine, which you'd expect, since they read the same in both directions.

begins_with does a version of the same thing. begins_with('bcd', sk) ought to mean "the sort key is a prefix of bcd". With the partition key given, DynamoDB returns the items whose sort key starts with bcd, exactly as begins_with(sk, 'bcd') does.

BETWEEN fits too, once you see it. 'bcd' BETWEEN sk AND 'z' returned the items at or after bcd, which is sk BETWEEN 'bcd' AND 'z' - the sort key swapped into the first position again.

Two controls make me more confident it's the PartiQL layer and nothing deeper. The plain Query API accepts :v < sk as a key condition and returns the right items. And the PartiQL reference implementation, given the same five items, returns 4 and 5 for both sk > 3 and 3 < sk.

I also ran the same value-first comparisons through two other AWS services on 13 November: Athena reading an S3 Table, and Redshift Serverless, including a SUPER column read with PartiQL navigation (3 < doc.n). Neither returned a wrong row.

Is it a bug?

I think so, but I'll put the case against first. The AWS docs only ever show the attribute on the left. Nothing I can find says a literal-first comparison is supported, so you could argue I'm off the documented path.

The trouble with that is what DynamoDB does with it. If the form isn't supported, the statement should be refused, the way plenty of other PartiQL is. It isn't refused. It runs and returns rows that don't match the condition. And it gives the right answer for the identical expression on a non-key attribute, so the parser clearly understands it.

I couldn't find a report of it anywhere, which surprised me. It might be that nobody writes their comparisons this way round. I don't, normally - I found it because I was testing operand order on purpose.

Who it affects

Not many people, I suspect. Only ExecuteStatement SELECTs are affected. UPDATE and DELETE need the whole key and evaluate the rest of the WHERE clause correctly, and the batch and transaction forms refuse a SELECT that doesn't name the whole key.

To hit it you need exactly one ordering comparison on the sort key, written value-first, beside the partition key. Add a second condition on the sort key and the comparison becomes a filter, which gets it right. Hand-written SQL rarely looks like that. Generated SQL might - a query builder that writes range checks as low <= col, or code ported from another dialect.

Reproducing it

A handful of commands, a table that costs next to nothing on-demand, and it's gone again in a minute.

T=sk-order-test
R=eu-west-2

aws dynamodb create-table --region $R --table-name $T \
  --attribute-definitions AttributeName=pk,AttributeType=S AttributeName=sk,AttributeType=N \
  --key-schema AttributeName=pk,KeyType=HASH AttributeName=sk,KeyType=RANGE \
  --billing-mode PAY_PER_REQUEST
aws dynamodb wait table-exists --region $R --table-name $T

for n in 1 2 3 4 5; do
  aws dynamodb put-item --region $R --table-name $T \
    --item "{\"pk\":{\"S\":\"a\"},\"sk\":{\"N\":\"$n\"},\"copy\":{\"N\":\"$n\"}}"
done

q() {
  printf '%-28s => ' "$1"
  aws dynamodb execute-statement --region $R --consistent-read \
    --statement "SELECT sk FROM \"$T\" WHERE $1" \
    --query 'Items[].sk.N' --output text
}

q "pk = 'a' AND sk > 3"
q "pk = 'a' AND 3 < sk"
q "pk = 'a' AND 3 > sk"
q "pk = 'a' AND 3 >= sk"
q "pk = 'a' AND 3 <= sk"
q "pk = 'a' AND 3 < copy"
q "pk = 'a' AND 3 > copy"
q "3 > sk"

aws dynamodb delete-table --region $R --table-name $T

I got the same answers in eu-west-2 and us-east-1 on 12 November 2026, and again on the 13th.

What Dynoxide does

Dynoxide returns the rows the statement asks for, and I'm leaving it that way.

It wasn't an obvious call. Dynoxide exists so that a test passing locally tells you something about production, and here it can't. If your code says 3 < sk, Dynoxide gives you 4 and 5, your tests pass, and production quietly gives you 1 and 2.

But copying it means writing a defect into my own engine on purpose, and then taking it out again the day AWS fixes theirs. So this is one place where the two differ, and I'd sooner tell you about it than copy it.

If you write PartiQL against DynamoDB, the practical advice is short: put the attribute on the left.

I've reported it to AWS. If you know more about why it happens, or you've seen it before, I'd like to hear.