Secondary index with two sort key fields is unpopulated

[jayKayEss]

Before opening, please confirm:

• [X] I have installed the latest version of the Amplify CLI (see above), and confirmed that the issue still persists.
• [X] I have searched for duplicate or closed issues.
• [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.
• [X] I have removed any sensitive information from my code snippets and submission.

How did you install the Amplify CLI?

npm

If applicable, what version of Node.js are you using?

16.3.0

Amplify CLI Version

8.3.1

What operating system are you using?

Mac

Did you make any manual changes to the cloud resources managed by Amplify? Please describe the changes made.

No

Amplify Categories

api

Amplify Commands

push

Describe the bug

Given a GraphQL schema like this:

type Invoice {
  id: ID!
  buyer_paidol_id: ID!
    @index(
      name: "byBuyerPaidol"
      queryField: "invoicesByBuyerPaidol"
      sortKeyFields: ["date"]
    )
    @index(
      name: "byBuyerPaidolIdAndSupplierPaidolIdAndUserInvoiceId"
      queryField: "invoicesByBuyerPaidolIdAndSupplierPaidolIdAndUserInvoiceId"
      sortKeyFields: ["user_invoice_id", "supplier_paidol_id"]
    )
  supplier_paidol_id: ID!
  user_invoice_id: String!
}

The first index, byBuyerPaidol is populated and returns expected results in GQL. However, the second index, byBuyerPaidolIdAndSupplierPaidolIdAndUserInvoiceId is not populated:

Switching the order of sortKeyFields has no effect.

Expected behavior

The index should be populated.

Reproduction steps

1. Edit schema
2. Push

GraphQL schema(s)

type Invoice
  @model(subscriptions: { level: off })
  @auth(rules: [{ allow: private, operations: [read] }]) {
  id: ID!
  id_base58: String
  id_base58_short: String
  payment_file_queue_id: ID
    @index(
      name: "byPaymentFileQueue"
      queryField: "invoicesByPaymentFileQueue"
      sortKeyFields: ["createdAt"]
    )
  payment_file: PaymentFileQueue
    @hasOne(fields: ["payment_file_queue_id"])
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  buyer_paidol_id: ID!
    @index(
      name: "byBuyerPaidol"
      queryField: "invoicesByBuyerPaidol"
      sortKeyFields: ["date"]
    )
    @index(
      name: "byBuyerPaidolIdAndSupplierPaidolIdAndUserInvoiceId"
      queryField: "invoicesByBuyerPaidolIdAndSupplierPaidolIdAndUserInvoiceId"
      sortKeyFields: ["user_invoice_id", "supplier_paidol_id"]
    )
  paidol_vendor_id: ID!
  paidol_vendor: PaidolVendor!
    @hasOne(fields: ["paidol_vendor_id"])
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  supplier_paidol_id: ID!
  supplier_paidol: Paidol!
    @hasOne(fields: ["supplier_paidol_id"])
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  user_invoice_id: String!
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  date: AWSDateTime!
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  balance_due: Currency!
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  createdAt: AWSDateTime!
  transaction_id: String
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  priority_payment_id: String
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  priority_payment_status: String
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  priority_payment_type: String
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  priority_payment_date: AWSDateTime
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  priority_payment_auth_message: String
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  priority_response: String
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
  status: PaymentFileQueueStatusEnum!
    @auth(
      rules: [
        {
          allow: groups
          groupsField: "buyer_paidol_id"
          operations: [create, read, update, delete]
        }
      ]
    )
}

Log output

# Put your logs below this line

Additional information

No response

[jayKayEss]

Hello, it looks like this index eventually did populate, either because I waited for that to happen, or because I inserted new rows.

Can you please let me know:

• When do DDB indexes get populated? Is there a scheduled job that run periodically?
• Should creating a new index on an existing table automatically backfill the index for existing rows?

The docs are pretty quiet on these points.

[josefaidt]

Hey @jayKayEss :wave: thanks for raising this! I'm taking a further look at this one 🙂

[technoknol]

We're facing the same issue and it's really annoying. Hoping to get it resolved soon.

[josefaidt]

Hey @jayKayEss :wave: thanks for raising this! I was able to reproduce this behavior using the following schema and steps:

1. create an amplify project with amplify init -y
2. create a GraphQL API with amplify add api > accept defaults
3. populate the schema with:

   enum Priority {
     LOW
     MEDIUM
     HIGH
   }
   
   enum Completed {
     YES
     NO
   }
   
   type Todo @model @auth(rules: [{ allow: public }]) {
     id: ID!
     name: String!
     priority: Priority!
       @index(
         name: "byPriorityAndompleted"
         queryField: "todosByPriorityAndCompleted"
         sortKeyFields: ["completed"]
       )
     completed: Completed @default(value: "NO")
     createdAt: AWSDateTime!
   }
   

4. push with amplify push -y
5. create a few records
6. add the following index to the Todo model

   type Todo @model @auth(rules: [{ allow: public }]) {
     id: ID!
     name: String!
     priority: Priority!
       @index(
         name: "byPriorityAndompleted"
         queryField: "todosByPriorityAndCompleted"
         sortKeyFields: ["completed"]
       )
       @index( 	# add this index
         name: "byPriorityAndCompletedAndCreatedAt"
         queryField: "todosByPriorityAndCompletedAndCreatedAt"
         sortKeyFields: ["completed", "createdAt"]
       )
     completed: Completed @default(value: "NO")
     createdAt: AWSDateTime!
   }
   

7. push with amplify push -y
8. observe second index is empty

After updating a single record, we see that record is now available from the new index, however this is not the case for records we have not updated or created since pushing the new index. The document count also shows 0 after updating the record

Marking as a bug 🙂

[kevinxu3]

Hi running into the same issue. Any updates on this?

[ObinnaAka]

This may not be relevant, but in the legacy documentation it states that secondary indexes with more than 1 sort key would need to be backfilled with a composite key of the two sort fields.

https://docs.amplify.aws/javascript/tools/cli-legacy/key-directive/#evolving-apis-with-key

I wonder if this behavior carried over into V2 without anyone noticing. It would explain why existing objects aren't indexed but new objects are.