search

aws-amplify / amplify-category-api

The AWS Amplify CLI is a toolchain for simplifying serverless web and mobile development. This plugin provides functionality for the API category, allowing for the creation and management of GraphQL and REST based backends for your amplify project.
https://docs.amplify.aws/
Apache License 2.0
89 stars 77 forks source link

Using Search query on a float field returning unexpected results. #866

Open jeff-accountably opened 2 years ago

jeff-accountably commented 2 years ago

Description

Here is my example query code running in javascript on the client side.

return useGraphQLQuery(
    {
      filter: {
        transactionTotalAmount: { eq: 29 },
      },
      sort: {
        direction: "desc",
        field: "transactionDate",
      },
      limit: 10,
    },
    searchTransactions,
    "searchTransactions"
  );

My schema has the field as a float, and the model object is tagged with @Searchable.

In my database I have a number of transactions that are 29.98, or 29.37, etc. They all get returned from this query, which is unexpected, since only 29 should be returned.

If I change the filter to be transactionTotalAmount: { eq: 29.98 } nothing gets returned, even though that should be an exact match with the transaction that is 29.98.

Categories

Steps to Reproduce

No response

Screenshots

No response

Platforms

Android Device/Emulator API Level

No response

Environment

ReactJS

Dependencies

N/A

Device

N/A

OS

N/A

CLI Version

8.0.3

Additional Context

No response

Jordan-Nelson commented 2 years ago

Hi @jeff-accountably - It looks like you are using Amplify JS with react native. Please let me know if that is not the case. Otherwise I will transfer this to the Amplify JS repo.

jeff-accountably commented 2 years ago

I am using Amplify JS with React JS, not react native.

majirosstefan commented 2 years ago

I am experiencing similar @searchable related error https://github.com/aws-amplify/amplify-category-api/issues/1038 (maybe it's something with encoding ??)

ncarvajalc commented 2 years ago

Description

The problem could be reproduced and another error appeared in the process, in which AWSDate data would not be sortable or appear in the search results when using the searchTransactions query when using a date with a timezone. This happened using a model with the @searchable directive.

Steps to reproduce errors

  1. Create the following Schema 
    type Transaction @model @searchable {
id: ID!
name: String!
transactionDate: AWSDate
transactionTotalAmount: Float
}
  2. Upload it to the cloud
  3. Run the following mutation: 
    mutation createIncorrect {
createTransaction(input: {name: "Lorem ipsum dolor sit amet",
      transactionDate: "2010-09-28Z",
      transactionTotalAmount: 123}) {
name
transactionDate
transactionTotalAmount
id
createdAt
updatedAt
}
}

In your Amazon Open Source service you should see the following index being created: Pasted image 20220928180237

Notice that the transactionDate is being parsed as a text and that the transactionTotalAmount is being parsed as a long.

  1. Try running the following query: 
    query filterAndSort {
searchTransactions(filter: {transactionTotalAmount: {eq: 123}},
sort: {direction: desc, field: transactionDate}, limit: 10) {
total
items {
  id
  transactionDate
  createdAt
  transactionTotalAmount
}
}
}

    You should see the following result:

    {
"data": {
"searchTransactions": {
  "total": null,
  "items": []
}
}
}

    If you change the sort field to name like this:

    query filterAndSort {
searchTransactions(filter: {transactionTotalAmount: {eq: 123}},
sort: {direction: desc, field: name}, limit: 10) {
total
items {
  id
  transactionDate
  createdAt
  transactionTotalAmount
}
}
}

    You should now see the transaction you had created earlier.

  2. Create 1 more transaction with transactionTotalAmount as the same integer part as the one created previously (123) but with a decimal part (.15 in this case): 
    mutation create {
createTransaction(input: {name: "Lorem ipsum dolor sit amet",
      transactionDate: "2010-09-28Z",
      transactionTotalAmount: 123.15}) {
name
transactionDate
transactionTotalAmount
id
createdAt
updatedAt
}
}

    Run the previous query:

    query filterAndSort {
searchTransactions(filter: {transactionTotalAmount: {eq: 123}},
sort: {direction: desc, field: name}, limit: 10) {
total
items {
  id
  transactionDate
  createdAt
  transactionTotalAmount
}
}
}

    And you should see both the result for the first transaction and the second one, even though the second one doesn't have a {transactionTotalAmount: {eq: 123} (equal to 23).

How to solve it

Clone your current environment and be sure that the first element you insert has a transactionDate in the format YYYY-MM-DD. If you insert the Z at the end it will be parsed as text and won't let you sort by date, even if allowed by the AWSDate Scalar. Also be sure to insert a float not ending in .0 (such as 123.15) as the transactionTotalAmount. I tried to do it with 123.0 and it didn't work.

It should work with the following query:

mutation createCorrect {
  createTransaction(input: {name: "Lorem ipsum dolor sit amet",
          transactionDate: "2010-09-28",
          transactionTotalAmount: 123.15}) {
    name
    transactionDate
    transactionTotalAmount
    id
    createdAt
    updatedAt
  }
}

You should see your new index as the one below: Pasted image 20220928182543

Notice how transactionDate is now parsed as a date and transactionTotalAmount as a float.

The following query should work as expected now:

query filterAndSort {
  searchTransactions(filter: {transactionTotalAmount: {eq: 123.15}},
  sort: {direction: desc, field: transactionDate}, limit: 10) {
    total
    items {
      id
      transactionDate
      createdAt
      transactionTotalAmount
    }
  }
}

Also you should be able to insert other values without the number being a float or the not ending in .0 restriction.

Persistent errors

If an AWSDate is present you can create a transaction with a transactionDate with a timezone such as explained in the AppSync documentation (e.g "1970-01-01Z" or "1970-01-01+05:30"). Whenever this happens a new transaction gets created but the searchTransactions query won't show it up in the results (the other transactions without timezones show up). You can still visualize them by listing all of the transactions with the listTransactions query.

A solution to this is using AWSDateTime or String instead of AWSDate.

Possible causes of the problem

I think it has to tho with the way Amazon OpenSearch Service parses the data. The index is created in the OpenSearch instance based on the first data registry that is inserted (I think this is the case as an index doesn't exist before the data is inserted). Also, according to the OpenSearch documentation, there is no timezone offset in the date data type in OpenSearch, so there might be conflicts when trying to get the transactionDate with a timezone.

AaronZyLee commented 1 year ago

Thanks for the details in issue report and contribution! The current behavior for the data type in elastic search is depending on the first inserted data following the dynamic mapping rules here. This is not ideal in some cases as you point out for float and date type with timezone. While the PR fixes the float type, we are currently working on a more generic solution for all the data type dynamic mapping. Still the PR really helps a lot for us to get a clear picture of this issue! I will update with the solution once the team approves the proposal.

ncarvajalc commented 1 year ago

Thanks @AaronZyLee. Also, my solution can be extended to other data types as well, it just needs to add conditions in the Python lambda function to map to the correct type. I would be glad to contribute to this fix as I have time to work on it. I am currently working with the Amplify Team via the MLHFellowship this fall, so reach out to me if you need anything.