search

aws-amplify / amplify-android

The fastest and easiest way to use AWS from your Android app.
https://docs.amplify.aws/lib/q/platform/android/
Apache License 2.0
245 stars 115 forks source link

DataStore observeQuery api throwing Exception while listening for data updates using relational Data models in android #2756

Open saqib-github-work opened 5 months ago

saqib-github-work commented 5 months ago

Before opening, please confirm:

Language and Async Model

Kotlin - Coroutines

Amplify Categories

DataStore

Gradle script dependencies

```groovy // Put output below this line amplify version : 2.14.11 ```

Environment information

``` # Put output below this line Kotlin version = 1.9.0 Gradle version = 8.3.1 ```

Please include any relevant guides or documentation you're referencing

https://docs.amplify.aws/android/build-a-backend/more-features/datastore/relational-models/

Describe the bug

I have relational DataModels and creating a subscriptions in the code using observeQuery API providing QueryPredicate to listen to the data updates.

I am experimenting with the DataModels (Post and Comments ) provided in the official documentations.

Exception thrown

DataStoreException{message=Error in querying the model., cause=DataStoreException{message=Invalid SQL statement: SELECT Comment.id AS Comment_id, Comment.content AS Comment_content, Comment.createdAt AS Comment_createdAt, Comment.updatedAt AS Comment_updatedAt, Comment.postID AS Comment_postID, Post.id AS Post_id, Post.content AS Post_content, Post.createdAt AS Post_createdAt, Post.rating AS Post_rating, Post.status AS Post_status, Post.title AS Post_title, Post.updatedAt AS Post_updatedAt FROM Comment INNER JOIN Post ON Comment.postID=Post.id WHERE Comment.postID = ? ORDER BY ;, cause=android.database.sqlite.SQLiteException: near ";": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT Comment.id AS Comment_id, Comment.content AS Comment_content, Comment.createdAt AS Comment_createdAt, Comment.updatedAt AS Comment_updatedAt, Comment.postID AS Comment_postID, Post.id AS Post_id, Post.content AS Post_content, Post.createdAt AS Post_createdAt, Post.rating AS Post_rating, Post.status AS Post_status, Post.title AS Post_title, Post.updatedAt AS Post_updatedAt FROM Comment INNER JOIN Post ON Comment.postID=Post.id WHERE Comment.postID = ? ORDER BY ;, recoverySuggestion=There is a possibility that there is a bug if this error persists. Please take a look at

Reproduction steps (if applicable)

No response

Code Snippet

// Put your code below this line.
val options = ObserveQueryOptions(Comment.POST.eq(postId), emptyList())
        viewModelScope.launch {
            Amplify.DataStore.observeQuery(
                Comment::class,
                options
            ).onStart { Log.i("aws-amplify-poc", "comments fetching starts")  }
                .catch { Log.i("aws-amplify-poc", "comments fetching failed", it)  }
                .onCompletion { Log.i("aws-amplify-poc", "comments fetching completes")  }
                .collect {
                    Log.i("aws-amplify-poc", "comments fetched for postId =$postId, comments = $it")
                }
        }

Log output

```Kotlin // Put your logs below this line 2024-04-05 17:51:50.305 4158-4346 SQLiteLog com...e.amplify.android_amplify_poc E (1) near ";": syntax error in "SELECT `Comment`.`id` AS `Comment_id`, `Comment`.`content` AS `Comment_content`, `Comment`.`createdAt` AS `Comment_createdAt`, `Comment`.`updatedAt` AS `Comment_updatedAt`, `Comment` 2024-04-05 17:51:50.306 4158-4346 amplify:aws-datastore com...e.amplify.android_amplify_poc V SQLCommandProcessor rawQuery in 0 ms inTransaction: false SQL: SELECT `LastSyncMetadata`.`id` AS `LastSyncMetadata_id`, `LastSyncMetadata`.`lastSyncTime` AS `LastSyncMetadata_lastSyncTime`, `LastSyncMetadata`.`lastSyncType` AS `LastSyncMetadata_lastSyncType`, `LastSyncMetadata`.`modelClassName` AS `LastSyncMetadata_modelClassName` FROM `LastSyncMetadata` WHERE modelClassName = ?; 2024-04-05 17:51:50.306 4158-4346 amplify:aws-datastore com...e.amplify.android_amplify_poc D Querying item for: LastSyncMetadata 2024-04-05 17:51:50.309 4158-4346 amplify:aws-datastore com...e.amplify.android_amplify_poc V Attempt to convert value "0be84069-51cd-3da8-af00-f79328cf4efc" from field id of type ID in model LastSyncMetadata 2024-04-05 17:51:50.309 4158-4346 amplify:aws-datastore com...e.amplify.android_amplify_poc V Attempt to convert value "1712332298585" from field lastSyncTime of type AWSTimestamp in model LastSyncMetadata 2024-04-05 17:51:50.309 4158-4346 amplify:aws-datastore com...e.amplify.android_amplify_poc V Attempt to convert value "DELTA" from field lastSyncType of type String in model LastSyncMetadata 2024-04-05 17:51:50.310 4158-4346 amplify:aws-datastore com...e.amplify.android_amplify_poc V Attempt to convert value "Comment" from field modelClassName of type String in model LastSyncMetadata 2024-04-05 17:51:50.312 4158-4158 WindowOnBackDispatcher com...e.amplify.android_amplify_poc W OnBackInvokedCallback is not enabled for the application. Set 'android:enableOnBackInvokedCallback="true"' in the application manifest. 2024-04-05 17:51:50.326 769-769 StatusBarIconController com.android.systemui D ignoring old pipeline callback because the new wifi icon is enabled 1970-01-01 01:00:00.000 0-0 I --------- beginning of crash 2024-04-05 17:51:50.348 4158-4234 EGL_emulation com...e.amplify.android_amplify_poc D app_time_stats: avg=2130.02ms min=1.68ms max=12339.00ms count=6 2024-04-05 17:51:50.388 4158-4158 AndroidRuntime com...e.amplify.android_amplify_poc E FATAL EXCEPTION: main Process: com.example.amplify.android_amplify_poc, PID: 4158 DataStoreException{message=Error in querying the model., cause=DataStoreException{message=Invalid SQL statement: SELECT `Comment`.`id` AS `Comment_id`, `Comment`.`content` AS `Comment_content`, `Comment`.`createdAt` AS `Comment_createdAt`, `Comment`.`updatedAt` AS `Comment_updatedAt`, `Comment`.`postID` AS `Comment_postID`, `Post`.`id` AS `Post_id`, `Post`.`content` AS `Post_content`, `Post`.`createdAt` AS `Post_createdAt`, `Post`.`rating` AS `Post_rating`, `Post`.`status` AS `Post_status`, `Post`.`title` AS `Post_title`, `Post`.`updatedAt` AS `Post_updatedAt` FROM `Comment` INNER JOIN `Post` ON `Comment`.`postID`=`Post`.`id` WHERE `Comment`.`postID` = ? ORDER BY ;, cause=android.database.sqlite.SQLiteException: near ";": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT `Comment`.`id` AS `Comment_id`, `Comment`.`content` AS `Comment_content`, `Comment`.`createdAt` AS `Comment_createdAt`, `Comment`.`updatedAt` AS `Comment_updatedAt`, `Comment`.`postID` AS `Comment_postID`, `Post`.`id` AS `Post_id`, `Post`.`content` AS `Post_content`, `Post`.`createdAt` AS `Post_createdAt`, `Post`.`rating` AS `Post_rating`, `Post`.`status` AS `Post_status`, `Post`.`title` AS `Post_title`, `Post`.`updatedAt` AS `Post_updatedAt` FROM `Comment` INNER JOIN `Post` ON `Comment`.`postID`=`Post`.`id` WHERE `Comment`.`postID` = ? ORDER BY ;, recoverySuggestion=There is a possibility that there is a bug if this error persists. Please take a look at https://github.com/aws-amplify/amplify-android/issues to see if there are any existing issues that match your scenario, and file an issue with the details of the bug if there isn't.}, recoverySuggestion=See attached exception for details.} at com.amplifyframework.datastore.storage.sqlite.SqlQueryProcessor.queryOfflineData(SqlQueryProcessor.java:83) at com.amplifyframework.datastore.storage.sqlite.ObserveQueryExecutor.queryLocalData(ObserveQueryExecutor.java:185) at com.amplifyframework.datastore.storage.sqlite.ObserveQueryExecutor.lambda$observeQuery$4$com-amplifyframework-datastore-storage-sqlite-ObserveQueryExecutor(ObserveQueryExecutor.java:153) at com.amplifyframework.datastore.storage.sqlite.ObserveQueryExecutor$$ExternalSyntheticLambda2.run(D8$$SyntheticClass:0) at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:487) at java.util.concurrent.FutureTask.run(FutureTask.java:264) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:644) at java.lang.Thread.run(Thread.java:1012) Suppressed: kotlinx.coroutines.internal.DiagnosticCoroutineContextException: [StandaloneCoroutine{Cancelling}@1ed22d4, Dispatchers.Main.immediate] 2024-04-05 17:51:50.389 4158-4158 AndroidRuntime com...e.amplify.android_amplify_poc E Caused by: DataStoreException{message=Invalid SQL statement: SELECT `Comment`.`id` AS `Comment_id`, `Comment`.`content` AS `Comment_content`, `Comment`.`createdAt` AS `Comment_createdAt`, `Comment`.`updatedAt` AS `Comment_updatedAt`, `Comment`.`postID` AS `Comment_postID`, `Post`.`id` AS `Post_id`, `Post`.`content` AS `Post_content`, `Post`.`createdAt` AS `Post_createdAt`, `Post`.`rating` AS `Post_rating`, `Post`.`status` AS `Post_status`, `Post`.`title` AS `Post_title`, `Post`.`updatedAt` AS `Post_updatedAt` FROM `Comment` INNER JOIN `Post` ON `Comment`.`postID`=`Post`.`id` WHERE `Comment`.`postID` = ? ORDER BY ;, cause=android.database.sqlite.SQLiteException: near ";": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT `Comment`.`id` AS `Comment_id`, `Comment`.`content` AS `Comment_content`, `Comment`.`createdAt` AS `Comment_createdAt`, `Comment`.`updatedAt` AS `Comment_updatedAt`, `Comment`.`postID` AS `Comment_postID`, `Post`.`id` AS `Post_id`, `Post`.`content` AS `Post_content`, `Post`.`createdAt` AS `Post_createdAt`, `Post`.`rating` AS `Post_rating`, `Post`.`status` AS `Post_status`, `Post`.`title` AS `Post_title`, `Post`.`updatedAt` AS `Post_updatedAt` FROM `Comment` INNER JOIN `Post` ON `Comment`.`postID`=`Post`.`id` WHERE `Comment`.`postID` = ? ORDER BY ;, recoverySuggestion=There is a possibility that there is a bug if this error persists. Please take a look at https://github.com/aws-amplify/amplify-android/issues to see if there are any existing issues that match your scenario, and file an issue with the details of the bug if there isn't.} at com.amplifyframework.datastore.storage.sqlite.SQLCommandProcessor.dataStoreException(SQLCommandProcessor.java:85) at com.amplifyframework.datastore.storage.sqlite.SQLCommandProcessor.rawQuery(SQLCommandProcessor.java:53) at com.amplifyframework.datastore.storage.sqlite.SqlQueryProcessor.queryOfflineData(SqlQueryProcessor.java:65) ... 8 more Caused by: android.database.sqlite.SQLiteException: near ";": syntax error (code 1 SQLITE_ERROR): , while compiling: SELECT `Comment`.`id` AS `Comment_id`, `Comment`.`content` AS `Comment_content`, `Comment`.`createdAt` AS `Comment_createdAt`, `Comment`.`updatedAt` AS `Comment_updatedAt`, `Comment`.`postID` AS `Comment_postID`, `Post`.`id` AS `Post_id`, `Post`.`content` AS `Post_content`, `Post`.`createdAt` AS `Post_createdAt`, `Post`.`rating` AS `Post_rating`, `Post`.`status` AS `Post_status`, `Post`.`title` AS `Post_title`, `Post`.`updatedAt` AS `Post_updatedAt` FROM `Comment` INNER JOIN `Post` ON `Comment`.`postID`=`Post`.`id` WHERE `Comment`.`postID` = ? ORDER BY ; at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1069) at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:673) at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590) at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:62) at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46) at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1714) at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1653) at com.amplifyframework.datastore.storage.sqlite.SQLCommandProcessor.rawQuery(SQLCommandProcessor.java:48) ... 9 more ```

amplifyconfiguration.json

No response

GraphQL Schema

```graphql // Put your schema below this line type Post @model @auth(rules: [{allow: public}]) { id: ID! title: String! status: PostStatus! rating: Int content: String! comments: [Comment] @hasMany(indexName: "byPost", fields: ["id"]) } enum PostStatus { ACTIVE INACTIVE } type Comment @model @auth(rules: [{allow: public}]) { id: ID! postID: ID! @index(name: "byPost") post: Post! @belongsTo(fields: ["postID"]) content: String! } ```

Additional information and screenshots

No response

joon-won commented 5 months ago

Hi @saqib-github-work, our team members will investigate the issue. We appreciate your patience.

yuhengshs commented 5 months ago

Hi @saqib-github-work ,

Thank you for reporting this issue.

I noticed that you were passing emptyList() to ObserveQueryOptions. If your intention was not to sort the output, then you should simply pass null value for that parameter. Otherwise, please specify whether you want the list to be in ASCENDING or DESCENDING order.

In this context, passing an empty list to ObserveQueryOptions led the statement generator to assume that an order was specified when, in fact, there was nothing passed. Therefore, you see a blank after ORDER BY, which invalidates the entire SQL statement.

Please let us know if this addresses your concerns.