Query operators ge, le and between don't work as expected when querying with composite sort keys in a GSI

Open KalvinWei opened this issue 4 months ago • 6 comments

Before opening, please confirm:

[x] I have searched for duplicate or closed issues and discussions.
[x] I have read the guide for submitting bug reports.
[x] I have done my best to include a minimal, self-contained set of instructions for consistently reproducing the issue.

JavaScript Framework

React, Next.js

Amplify APIs

GraphQL API

Amplify Version

Amplify Categories

api

Backend

Amplify Gen 2

Environment information

# Put output below this line



  System:
    OS: macOS 15.5
    CPU: (11) x64 Apple M3 Pro
    Memory: 32.45 MB / 18.00 GB
    Shell: 5.9 - /bin/zsh
  Binaries:
    Node: 22.14.0 - ~/.nvm/versions/node/v22.14.0/bin/node
    npm: 10.9.2 - ~/.nvm/versions/node/v22.14.0/bin/npm
  Browsers:
    Chrome: 138.0.7204.101
    Safari: 18.5
  npmPackages:
    %name%:  0.1.0 
    @ampproject/toolbox-optimizer:  undefined ()
    @aws-amplify/backend: ^1.16.1 => 1.16.1 
    @aws-amplify/backend-cli: ^1.8.0 => 1.8.0 
    @aws-amplify/ui-react: ^6.5.5 => 6.5.5 
    @aws-amplify/ui-react-internal:  undefined ()
    @aws-amplify/ui-react-server:  undefined ()
    @babel/core:  undefined ()
    @babel/runtime:  7.22.5 
    @edge-runtime/cookies:  5.0.0 
    @edge-runtime/ponyfill:  3.0.0 
    @edge-runtime/primitives:  5.0.0 
    @hapi/accept:  undefined ()
    @mswjs/interceptors:  undefined ()
    @napi-rs/triples:  undefined ()
    @next/font:  undefined ()
    @opentelemetry/api:  undefined ()
    @types/node: ^20 => 20.17.0 
    @types/react: ^18 => 18.3.12 
    @types/react-dom: ^18 => 18.3.1 
    @vercel/nft:  undefined ()
    @vercel/og:  0.6.2 
    acorn:  undefined ()
    amphtml-validator:  undefined ()
    anser:  undefined ()
    arg:  undefined ()
    assert:  undefined ()
    async-retry:  undefined ()
    async-sema:  undefined ()
    aws-amplify: ^6.15.3 => 6.15.3 
    aws-amplify/adapter-core:  undefined ()
    aws-amplify/adapter-core/internals:  undefined ()
    aws-amplify/analytics:  undefined ()
    aws-amplify/analytics/kinesis:  undefined ()
    aws-amplify/analytics/kinesis-firehose:  undefined ()
    aws-amplify/analytics/personalize:  undefined ()
    aws-amplify/analytics/pinpoint:  undefined ()
    aws-amplify/api:  undefined ()
    aws-amplify/api/internals:  undefined ()
    aws-amplify/api/server:  undefined ()
    aws-amplify/auth:  undefined ()
    aws-amplify/auth/cognito:  undefined ()
    aws-amplify/auth/cognito/server:  undefined ()
    aws-amplify/auth/enable-oauth-listener:  undefined ()
    aws-amplify/auth/server:  undefined ()
    aws-amplify/data:  undefined ()
    aws-amplify/data/server:  undefined ()
    aws-amplify/datastore:  undefined ()
    aws-amplify/in-app-messaging:  undefined ()
    aws-amplify/in-app-messaging/pinpoint:  undefined ()
    aws-amplify/push-notifications:  undefined ()
    aws-amplify/push-notifications/pinpoint:  undefined ()
    aws-amplify/storage:  undefined ()
    aws-amplify/storage/s3:  undefined ()
    aws-amplify/storage/s3/server:  undefined ()
    aws-amplify/storage/server:  undefined ()
    aws-amplify/utils:  undefined ()
    aws-cdk: ^2 => 2.163.1 
    aws-cdk-lib: ^2 => 2.204.0 
    babel-packages:  undefined ()
    browserify-zlib:  undefined ()
    browserslist:  undefined ()
    buffer:  undefined ()
    bytes:  undefined ()
    ci-info:  undefined ()
    cli-select:  undefined ()
    client-only:  0.0.1 
    commander:  undefined ()
    comment-json:  undefined ()
    compression:  undefined ()
    conf:  undefined ()
    constants-browserify:  undefined ()
    constructs: ^10.3.0 => 10.4.2 
    content-disposition:  undefined ()
    content-type:  undefined ()
    cookie:  undefined ()
    cross-spawn:  undefined ()
    crypto-browserify:  undefined ()
    css.escape:  undefined ()
    data-uri-to-buffer:  undefined ()
    debug:  undefined ()
    devalue:  undefined ()
    domain-browser:  undefined ()
    edge-runtime:  undefined ()
    esbuild: ^0.23.1 => 0.23.1 (0.25.6)
    events:  undefined ()
    find-cache-dir:  undefined ()
    find-up:  undefined ()
    fresh:  undefined ()
    get-orientation:  undefined ()
    glob:  undefined ()
    gzip-size:  undefined ()
    http-proxy:  undefined ()
    http-proxy-agent:  undefined ()
    https-browserify:  undefined ()
    https-proxy-agent:  undefined ()
    icss-utils:  undefined ()
    ignore-loader:  undefined ()
    image-size:  undefined ()
    is-animated:  undefined ()
    is-docker:  undefined ()
    is-wsl:  undefined ()
    jest-worker:  undefined ()
    json5:  undefined ()
    jsonwebtoken:  undefined ()
    loader-runner:  undefined ()
    loader-utils:  undefined ()
    lodash.curry:  undefined ()
    lru-cache:  undefined ()
    mini-css-extract-plugin:  undefined ()
    nanoid:  undefined ()
    native-url:  undefined ()
    neo-async:  undefined ()
    next: 14.2.10 => 14.2.10 
    node-fetch:  undefined ()
    node-html-parser:  undefined ()
    ora:  undefined ()
    os-browserify:  undefined ()
    p-limit:  undefined ()
    path-browserify:  undefined ()
    picomatch:  undefined ()
    platform:  undefined ()
    postcss-flexbugs-fixes:  undefined ()
    postcss-modules-extract-imports:  undefined ()
    postcss-modules-local-by-default:  undefined ()
    postcss-modules-scope:  undefined ()
    postcss-modules-values:  undefined ()
    postcss-preset-env:  undefined ()
    postcss-safe-parser:  undefined ()
    postcss-scss:  undefined ()
    postcss-value-parser:  undefined ()
    process:  undefined ()
    punycode:  undefined ()
    querystring-es3:  undefined ()
    raw-body:  undefined ()
    react: ^18 => 18.3.1 
    react-builtin:  undefined ()
    react-dom: ^18 => 18.3.1 
    react-dom-builtin:  undefined ()
    react-dom-experimental-builtin:  undefined ()
    react-experimental-builtin:  undefined ()
    react-is:  18.2.0 
    react-refresh:  0.12.0 
    react-server-dom-turbopack-builtin:  undefined ()
    react-server-dom-turbopack-experimental-builtin:  undefined ()
    react-server-dom-webpack-builtin:  undefined ()
    react-server-dom-webpack-experimental-builtin:  undefined ()
    regenerator-runtime:  0.13.4 
    sass-loader:  undefined ()
    scheduler-builtin:  undefined ()
    scheduler-experimental-builtin:  undefined ()
    schema-utils:  undefined ()
    semver:  undefined ()
    send:  undefined ()
    server-only:  0.0.1 
    setimmediate:  undefined ()
    shell-quote:  undefined ()
    source-map:  undefined ()
    source-map08:  undefined ()
    stacktrace-parser:  undefined ()
    stream-browserify:  undefined ()
    stream-http:  undefined ()
    string-hash:  undefined ()
    string_decoder:  undefined ()
    strip-ansi:  undefined ()
    superstruct:  undefined ()
    tar:  undefined ()
    terser:  undefined ()
    text-table:  undefined ()
    timers-browserify:  undefined ()
    tsx: ^4.19.0 => 4.19.4 
    tty-browserify:  undefined ()
    typescript: ^5.6.2 => 5.6.3 (4.4.4, 4.9.5)
    ua-parser-js:  undefined ()
    unistore:  undefined ()
    util:  undefined ()
    vm-browserify:  undefined ()
    watchpack:  undefined ()
    web-vitals:  undefined ()
    webpack:  undefined ()
    webpack-sources:  undefined ()
    ws:  undefined ()
    zod:  undefined ()
  npmGlobalPackages:
    @aws-amplify/cli: 12.14.3
    aws-amplify: 6.14.4
    corepack: 0.31.0
    npm: 10.9.2

Describe the bug

To reproduce the issue:

add Image model to your data module

    // amplify/data/resource.ts
    const schema = a.schema({
  Image: a
    .model({
      imageID: a.string().required(),
      subEventName: a.string().required(),
      eventID: a.string().required(),
      orderKey: a.integer(), // how to order images
    })
    .identifier(["imageID"])
    .secondaryIndexes((index) => [
      index("eventID").sortKeys(["subEventName", "orderKey"]),
    ])
    .authorization((allow) => [allow.publicApiKey()]),
});

Spin up your Amplify Gen2 sandbox and add below sample data into the DynamoDb table | imageID | eventID | subEventName | orderKey | typename | subEventName#orderKey | |---------|---------|-------------|----------|-----------|----------------------| | img_001 | event_001 | ceremony | 1 | Image | ceremony#1 | | img_002 | event_001 | reception | 5 | Image | reception#5 | | img_005 | event_001 | ceremony | 10 | Image | ceremony#10 | | img_003 | event_002 | ceremony | 3 | Image | ceremony#3 | | img_004 | event_002 | party | 8 | Image | party#8 | | image_006 | event_003 | ceremony | 7 | Image | ceremony#7 |
Use the query method generated on the JS client for the GSI index

// app/page.tsx
const { data: images } =
    await client.models.Image.listImageByEventIDAndSubEventNameAndOrderKey({
      eventID: "event_001",
      subEventNameOrderKey: {
        between: [
          { subEventName: "ceremony", orderKey: 0 },
          { subEventName: "ceremony", orderKey: 10 },
        ],
      },
    });

Issues observed

between operator in VSCode has a type definition that only accepts a single item in the array so typescript type check fails and IDE prompts type error.

# When hovering on "between" in VSCode, popup shows:
(property) between?: [{
    subEventName: string;
    orderKey: number;
}] | undefined

Though the query returns expected result:

[
  {
    imageID: 'img_001',
    subEventName: 'ceremony',
    eventID: 'event_001',
    orderKey: 1,
    createdAt: '2025-07-14T00:56:19.085033+00:00',
    updatedAt: '2025-07-14T00:56:19.085395+00:00'
  },
  {
    imageID: 'img_005',
    subEventName: 'ceremony',
    eventID: 'event_001',
    orderKey: 10,
    createdAt: '2025-07-14T00:56:19.085404+00:00',
    updatedAt: '2025-07-14T00:56:19.085404+00:00'
  }
]
```.

2. When try to query with `between` with a different range:
```typescript
 await client.models.Image.listImageByEventIDAndSubEventNameAndOrderKey({
      eventID: "event_001",
      subEventNameOrderKey: {
        between: [
          { subEventName: "ceremony", orderKey: 1 },
          { subEventName: "ceremony", orderKey: 5 },
        ],
      },
    });

I got wrong resut since orderKey is greater than 5.

[
  {
    imageID: 'img_001',
    subEventName: 'ceremony',
    eventID: 'event_001',
    orderKey: 1,
    createdAt: '2025-07-14T00:56:19.085033+00:00',
    updatedAt: '2025-07-14T00:56:19.085395+00:00'
  },
  {
    imageID: 'img_005',
    subEventName: 'ceremony',
    eventID: 'event_001',
    orderKey: 10,
    createdAt: '2025-07-14T00:56:19.085404+00:00',
    updatedAt: '2025-07-14T00:56:19.085404+00:00'
  }
]

Further I tested with ge and le as a combination to query the same, it returns null.

 const { data: images } =
    await client.models.Image.listImageByEventIDAndSubEventNameAndOrderKey({
      eventID: "event_001",
      subEventNameOrderKey: {
        ge: {
          subEventName: "ceremony",
          orderKey: 1,
        },
        le: {
          subEventName: "ceremony",
          orderKey: 20,
        },
      },
    });

When try to query only with ge or le, the result shows the client uses the operators by comparing the subEventName#orderKey attributes that are automatically generated by Amplify when calling client.models.Image.create method in a lexicographical ordering, not comparing each of the sort keys.

Can you help resolve the issue or suggest a workaround before it's amended?

Expected behavior

between operator should pass typescript type check.
Sort keys should be compared by the type they're defined. String with lexicographical ordering and number with numerical order.

Reproduction steps

See the description section

Code Snippet

See the description section

Log output

aws-exports.js

Manual configuration

Additional configuration

No response

Mobile Device

No response

Mobile Operating System

No response

Mobile Browser

No response

Mobile Browser Version

No response

Additional information and screenshots

No response

Jul 15 '25 02:07 KalvinWei