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

[saqib-github-work]

Before opening, please confirm:

• I have searched for duplicate or closed issues and discussions.

Language and Async Model

Kotlin - Coroutines

Amplify Categories

DataStore

Gradle script dependencies

// 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

// 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     <no-tag>                                                     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.<init>(SQLiteProgram.java:62)
                                                                                                    	at android.database.sqlite.SQLiteQuery.<init>(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

// 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]

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

[yuhengshs]

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.