Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

DataStore Error having two relationships with the same model #494

Closed
Lorenzohidalgo opened this issue Apr 2, 2021 · 7 comments
Closed

DataStore Error having two relationships with the same model #494

Lorenzohidalgo opened this issue Apr 2, 2021 · 7 comments
Labels
Android Issues specific to the Android Platform bug Something is not working; the issue has reproducible steps and has been reproduced datastore Issues related to the DataStore Category requires-android-fix This issue is the result of an underlying Amplify Android issue that needs to be fixed.

Comments

@Lorenzohidalgo
Copy link
Contributor

Describe the bug
The autogenerated code for a data schema does not support having multiple relationships with the same data model.
Error: android.database.sqlite.SQLiteException throws an exception for duplicated column names.

Thrown Exception DataStoreException (DataStoreException(message: Error in saving the model: Transfers[id=4fd38eb3-ed48-4452-870e-ae3d5f748116], recoverySuggestion: See attached exception for details., underlyingException: android.database.sqlite.SQLiteException: ambiguous column name: Users.id (code 1 SQLITE_ERROR): , while compiling: SELECT `Transfers`.`id` AS `Transfers_id`, `Transfers`.`isPublic` AS `Transfers_isPublic`, `Transfers`.`likesUserIDs` AS `Transfers_likesUserIDs`, `Transfers`.`message` AS `Transfers_message`, `Transfers`.`transferDate` AS `Transfers_transferDate`, `Transfers`.`transfersFromUserId` AS `Transfers_transfersFromUserId`, `Transfers`.`transfersToUserId` AS `Transfers_transfersToUserId`, `Users`.`id` AS `Users_id`, `Users`.`avatarKey` AS `Users_avatarKey`, `Users`.`cognito_uid` AS `Users_cognito_uid`, `Users`.`description` AS `Users_description`, `Users`.`email` AS `Users_email`, `Users`.`joinDate` AS `Users_joinDate`, `Users`.`lastLogIn` AS `Users_lastLogIn`, `Users`.`name` AS `Users_name`, `Users`.`phoneNumber` AS `Users_phoneNumber`, `Users`.`id` AS `Users_id`, `Users`.`avatarKey` AS `Users_avatarKey`, `Users`.`cognito_uid` AS `Users_cognito_uid`, `Users`.`description` AS `Users_description`, `Users`.`email` AS `Users_email`, `Users`.`joinDate` AS `Users_joinDate`, `Users`.`lastLogIn` AS `Users_lastLogIn`, `Users`.`name` AS `Users_name`, `Users`.`phoneNumber` AS `Users_phoneNumber` FROM `Transfers` INNER JOIN `Users` ON `Transfers`.`transfersFromUserId`=`Users`.`id` INNER JOIN `Users` ON `Transfers`.`transfersToUserId`=`Users`.`id` WHERE (`Transfers`.`id` = ? AND 1 = 1);))

To Reproduce
Steps to reproduce the behavior:

  1. Generate a new data model with two relationships to the same model.
  2. Deploy changes and try to save a new item.
  3. See error

Expected behavior
The autogenerated code should differ the relationships using its name for the sql querys.

Screenshots
image

Platform
Amplify Flutter current supports iOS and Android. This issue is reproducible in (check all that apply):
[X] Android
[] iOS

Output of flutter doctor -v 
[√] Flutter (Channel stable, 2.0.2, on Microsoft Windows [Versión 10.0.19042.867], locale es-ES)
  • Flutter version 2.0.2 at C:\DEVELOPMENT\SRC\flutter
  • Framework revision 8962f6dc68 (3 weeks ago), 2021-03-11 13:22:20 -0800
  • Engine revision 5d8bf811b3
  • Dart version 2.12.1

[√] Android toolchain - develop for Android devices (Android SDK version 30.0.3)
  • Android SDK at C:\Users\Lorenzo HG\AppData\Local\Android\sdk
  • Platform android-30, build-tools 30.0.3
  • Java binary at: C:\Program Files\Android\Android Studio\jre\bin\java
  • Java version OpenJDK Runtime Environment (build 1.8.0_242-release-1644-b01)
  • All Android licenses accepted.

[√] Chrome - develop for the web
  • Chrome at C:\Program Files (x86)\Google\Chrome\Application\chrome.exe

[√] Android Studio (version 4.1.0)
  • Android Studio at C:\Program Files\Android\Android Studio
  • Flutter plugin can be installed from:
     https://plugins.jetbrains.com/plugin/9212-flutter
  • Dart plugin can be installed from:
     https://plugins.jetbrains.com/plugin/6351-dart
  • Java version OpenJDK Runtime Environment (build 1.8.0_242-release-1644-b01)

[√] VS Code, 64-bit edition (version 1.54.3)
  • VS Code at C:\Program Files\Microsoft VS Code
  • Flutter extension version 3.21.0

[√] Connected device (3 available)
  • sdk gphone x86 (mobile) • emulator-5554 • android-x86    • Android 11 (API 30) (emulator)
  • Chrome (web)            • chrome        • web-javascript • Google Chrome 89.0.4389.114
  • Edge (web)              • edge          • web-javascript • Microsoft Edge 89.0.774.57

• No issues found!

Smartphone (please complete the following information):

  • Device: Pixel 4 Simulator
  • OS: Android API 30

Additional context

Error Logs E/amplify:flutter:datastore(20026): Caused by: android.database.sqlite.SQLiteException: ambiguous column name: Users.id (code 1 SQLITE_ERROR): , while compiling: SELECT `Transfers`.`id` AS `Transfers_id`, `Transfers`.`isPublic` AS `Transfers_isPublic`, `Transfers`.`likesUserIDs` AS `Transfers_likesUserIDs`, `Transfers`.`message` AS `Transfers_message`, `Transfers`.`transferDate` AS `Transfers_transferDate`, `Transfers`.`transfersFromUserId` AS `Transfers_transfersFromUserId`, `Transfers`.`transfersToUserId` AS `Transfers_transfersToUserId`, `Users`.`id` AS `Users_id`, `Users`.`avatarKey` AS `Users_avatarKey`, `Users`.`cognito_uid` AS `Users_cognito_uid`, `Users`.`description` AS `Users_description`, `Users`.`email` AS `Users_email`, `Users`.`joinDate` AS `Users_joinDate`, `Users`.`lastLogIn` AS `Users_lastLogIn`, `Users`.`name` AS `Users_name`, `Users`.`phoneNumber` AS `Users_phoneNumber`, `Users`.`id` AS `Users_id`, `Users`.`avatarKey` AS `Users_avatarKey`, `Users`.`cognito_uid` AS `Users_cognito_uid`, `Users`.`description` AS `Users_description`, `Users`.`email` AS `Users_email`, `Users`.`joinDate` AS `Users_joinDate`, `Users`.`lastLogIn` AS `Users_lastLogIn`, `Users`.`name` AS `Users_name`, `Users`.`phoneNumber` AS `Users_phoneNumber` FROM `Transfers` INNER JOIN `Users` ON `Transfers`.`transfersFromUserId`=`Users`.`id` INNER JOIN `Users` ON `Transfers`.`transfersToUserId`=`Users`.`id` WHERE (`Transfers`.`id` = ? AND 1 = 1); E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1045) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:652) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:590) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteProgram.(SQLiteProgram.java:61) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteQuery.(SQLiteQuery.java:37) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:46) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1545) E/amplify:flutter:datastore(20026): at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1484) E/amplify:flutter:datastore(20026): at com.amplifyframework.datastore.storage.sqlite.SQLiteStorageAdapter.getQueryAllCursor(SQLiteStorageAdapter.java:912) E/amplify:flutter:datastore(20026): at com.amplifyframework.datastore.storage.sqlite.SQLiteStorageAdapter.modelExists(SQLiteStorageAdapter.java:843) E/amplify:flutter:datastore(20026): at com.amplifyframework.datastore.storage.sqlite.SQLiteStorageAdapter.lambda$save$3$SQLiteStorageAdapter(SQLiteStorageAdapter.java:274) E/amplify:flutter:datastore(20026): ... 6 more
@infernal008
Copy link

Hello, I was browsing around and have come across this filing here. I think we may have a similar use case and thus have encountered the same problem.

You can find my filing here: DataStore SQLiteException: Ambiguous Column Name

It appears to currently be a limitation with query generation. What I've done is to change the model of my data - which I am fortunate enough that I would still be able then in terms of my development progress. Though, it has been tagged for a more long term fix.

At least, another use case can be noted from this.

@Lorenzohidalgo
Copy link
Contributor Author

Thanks @infernal008 it seems like we encountered the same issue. I ended up doing the same as you, changed the relationships for the model identifiers and ended up querying them in different steps.

@b-cancel
Copy link

This is a bug report I just filed that includes a couple of bugs I found and the workarounds to each
after reading your bug report I think it could help
#822

@Jordan-Nelson Jordan-Nelson added Android Issues specific to the Android Platform datastore Issues related to the DataStore Category requires-android-fix This issue is the result of an underlying Amplify Android issue that needs to be fixed. labels Mar 8, 2022
@Jordan-Nelson
Copy link
Member

@Lorenzohidalgo it looks like the underlying issue in Amplify-Android has been resolved, and would have been available since Amplify-flutter 0.3.2. It is possible there is some work required to consume this fix in Amplify-Flutter though. Have you attempted this with Amplify-flutter 0.3.2 or later?

@Jordan-Nelson Jordan-Nelson added bug Something is not working; the issue has reproducible steps and has been reproduced pending-community-response Pending response from the issue opener or other community members labels Mar 8, 2022
@Lorenzohidalgo
Copy link
Contributor Author

@Jordan-Nelson I stopped using DataStore for my application. I'm not able to test it with the newer versions.

@Jordan-Nelson
Copy link
Member

Jordan-Nelson commented Mar 9, 2022 •
edited
Loading

Okay. No worries. We can attempt to reproduce this with the latest version of amplify-flutter.

I am going to label this as to-be-reproduced and pending-triage so that it gets in our list of issues to reproduce.

@Jordan-Nelson Jordan-Nelson added pending-triage This issue is in the backlog of issues to triage to-be-reproduced Issues that have not been reproduced yet, but have reproduction steps provided and removed bug Something is not working; the issue has reproducible steps and has been reproduced labels Mar 9, 2022
@Jordan-Nelson Jordan-Nelson removed the pending-community-response Pending response from the issue opener or other community members label Mar 9, 2022
@HuiSF HuiSF mentioned this issue Mar 10, 2022
Closed
@abdallahshaban557 abdallahshaban557 added bug Something is not working; the issue has reproducible steps and has been reproduced and removed to-be-reproduced Issues that have not been reproduced yet, but have reproduction steps provided pending-triage This issue is in the backlog of issues to triage labels Apr 26, 2022
@HuiSF
Copy link
Member

HuiSF commented Jul 14, 2022

As the fix has been released with previous amplify-flutter updates to integrate the latest version of amplify-android, I'm going to optimistically close this issue. Please feel free the follow up or reopen if anything. Thanks.

@HuiSF HuiSF closed this as completed Jul 14, 2022
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
Android Issues specific to the Android Platform bug Something is not working; the issue has reproducible steps and has been reproduced datastore Issues related to the DataStore Category requires-android-fix This issue is the result of an underlying Amplify Android issue that needs to be fixed.
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
6 participants
@HuiSF @b-cancel @abdallahshaban557 @Jordan-Nelson @Lorenzohidalgo @infernal008