Closed airon-tark closed 1 year ago
Hi @airon-tark thanks for opening the issue.
From where do these fields come from?
The DataStore implementation follows only provided schema to generate SQL commands querying local SQLite database. It won't use random names to present table like what the exception message showed. Therefore, mostly likely, your dev App has wrong schema information.
lib/models/ModelProvider.dart
is correct and reflect the model schema you pasted above.lib/models/
and run amplify codegen models
to regenerate modelsflutter clean
to clean the project buildflutter run
to reinstall the dev App@HuiSF
Thanks for the answer
"The DataStore implementation follows only provided schema to generate SQL commands querying local SQLite database...."
Seems this is not the full truth. It follows, but make some other changes in the field names. As you can see from my schema:
Wristband
has PaymentMethod
that has User
Wristband
has User
So the wristband has two joint tables of model User
. So it called the first one User
and another one User2
E/amplify:flutter:datastore(13115): Caused by: DataStoreException{message=Invalid SQL statement:
SELECT `User`.`id` AS `User_id`,
`User`.`authUserID` AS `User_authUserID`,
`User`.`createdAt` AS `User_createdAt`,
`User`.`email` AS `User_email`,
`User`.`emailConfirmed` AS `User_emailConfirmed`,
`User`.`linkedPaymentMethodId` AS `User_linkedPaymentMethodId`,
`User`.`updatedAt` AS `User_updatedAt`,
`User`.`userAccountInfoId` AS `User_userAccountInfoId`,
`User`.`userAutoTopUpConfigId` AS `User_userAutoTopUpConfigId`,
`User2`.`id` AS `User_id2`,
`User2`.`authUser2ID` AS `User_authUserID2`,
`User2`.`createdAt` AS `User_createdAt2`,
`User2`.`email` AS `User_email2`,
`User2`.`emailConfirmed` AS `User_emailConfirmed2`,
`User2`.`linkedPaymentMethodId` AS `User_linkedPaymentMethodId2`,
`User2`.`updatedAt` AS `User_updatedAt2`,
`User2`.`userAccountInfoId` AS `User_userAccountInfoId2`,
`User2`.`userAutoTopUpConfigId` AS `User_userAutoTopUpConfigId2`,
`Wristband`.`id` AS `Wristband_id`,
`Wristband`.`createdAt` AS `Wristband_createdAt`,
`Wristband`.`emailToSendReceipt` AS `Wristband_emailToSendReceipt`,
`Wristband`.`eventID` AS `Wristband_eventID`,
`Wristband`.`paymentMethodID` AS `Wristband_paymentMethodID`,
`Wristband`.`pinCode` AS `Wristband_pinCode`,
`Wristband`.`spendingLimit` AS `Wristband_spendingLimit`,
`Wristband`.`topUpAmount` AS `Wristband_topUpAmount`,
`Wristband`.`updatedAt` AS `Wristband_updatedAt`,
`Wristband`.`uuid` AS `Wristband_uuid`,
`Wristband`.`wristbandNumber` AS `Wristband_wristbandNumber`,
`Wristband`.`wristbandOwnerEmail` AS `Wristband_wristbandOwnerEmail`,
`Wristband`.`wristbandOwnerName` AS `Wristband_wristbandOwnerName`,
`Wristband`.`wristbandOwnerPhoneNumber` AS `Wristband_wristbandOwnerPhoneNumber`,
`Wristband`.`wristbandUserID` AS `Wristband_wristbandUserID`,
`Wristband`.`eventWristbandsId` AS `Wristband_eventWristbandsId`,
`Wristband`.`paymentMethodWristbandsId` AS `Wristband_paymentMethodWristbandsId`,
`Wristband`.`userWristbandsId` AS `Wristband_userWristbandsId`,
`Event`.`id` AS `Event_id`, `Event`.`address` AS `Event_address`,
`Event`.`category` AS `Event_category`,
`Event`.`city` AS `Event_city`,
`Event`.`country` AS `Event_country`,
`Event`.`createdAt` AS `Event_createdAt`,
`Event`.`description` AS `Event_description`,
`Event`.`finishDate` AS `Event_finishDate`,
`Event`.`imageUrl` AS `Event_imageUrl`,
`Event`.`latitude` AS `Event_latitude`,
`Event`.`longitude` AS `Event_longitude`,
`Event`.`name` AS `Event_name`,
`Event`.`startDate` AS `Event_startDate`,
`Event`.`updatedAt` AS `Event_updatedAt`,
`PaymentMethod`.`id` AS `PaymentMethod_id`,
`PaymentMethod`.`cardExpiryDate` AS `PaymentMethod_cardExpiryDate`,
`PaymentMethod`.`cardHolderName` AS `PaymentMethod_cardHolderName`,
`PaymentMethod`.`cardNumber` AS `PaymentMethod_cardNumber`,
`PaymentMethod`.`cardType` AS `PaymentMethod_cardType`,
`PaymentMethod`.`createdAt` AS `PaymentMethod_createdAt`,
`PaymentMethod`.`type` AS `PaymentMethod_type`,
`PaymentMethod`.`updatedAt` AS `PaymentMethod_updatedAt`,
`PaymentMethod`.`userID` AS `PaymentMethod_userID`,
`PaymentMethod`.`userPaymentMethodsId` AS `PaymentMethod_userPaymentMethodsId` FROM `Wristband`
LEFT JOIN `Event` ON `Wristband`.`eventWristbandsId`=`Event`.`id`
LEFT JOIN `PaymentMethod` ON `Wristband`.`paymentMethodWristbandsId`=`PaymentMethod`.`id`
LEFT JOIN `User` ON `PaymentMethod`.`userPaymentMethodsId`=`User`.`id`
LEFT JOIN `User` AS `User2` ON `Wristband`.`userWristbandsId`=`User2`.`id`
WHERE wristbandUserID = ?;,
These lines are important
LEFT JOIN `User` ON `PaymentMethod`.`userPaymentMethodsId`=`User`.`id`
LEFT JOIN `User` AS `User2` ON `Wristband`.`userWristbandsId`=`User2`.`id`
Our team found these lines in the amplify-android code (here)
String columnName = column.getQuotedColumnName().replace(column.getTableName(), tableAlias);
We can see it is replacing the name of the column name with the alias table name. But it making a mistake and replacing the word in the middle of the next column name as well.
it trying to do
User.UserColumn -> User2.UserColumn
but doing instead
User.UserColumn -> User2.User2Column
and after that SQL can't find the column named User2Column
.
The problem is in this commit when fixing this issue
How to fix: replace only the first occurrence of table name, not all
Thanks for the details @airon-tark ! I'll get in touch with amplify-android maintainers to fix the underlying issue.
Hi @airon-tark 👋 , we are working on reproducing the issue, could you please run below command and send us the report amplify diagnose --send-report
Hi @AnilMaktala, I having the same problem, has any news about this problem?
Another instance of the issue has been reported by amplify-flutter consumers. (linked to this issue)
The issue is at this line in below generated SQL command, where empresaLicensaEmpresa2Id
should still be empresaLicensaEmpresaId
due to what this comment pointed out.
`Empresa2`.`empresaLicensaEmpresa2Id` AS `Empresa_empresaLicensaEmpresaId2`,
This is consistently reproducible with the following model relationship pattern, when querying ModelA
this issue surfaces.
ModelA {
modelB: @belongsTo ModelB
modelC: @belongsTo ModelC
}
ModelB {
modelD: @belongsTo ModelD
}
ModelC {
modelD: @belongsTo ModelD
}
ModelD {
}
The complete sample of generated SQL command that breaks.
SELECT
`Usuario`.`id` AS `Usuario_id`,
`Usuario`.`admin` AS `Usuario_admin`,
`Usuario`.`ativo` AS `Usuario_ativo`,
`Usuario`.`createdAt` AS `Usuario_createdAt`,
`Usuario`.`email` AS `Usuario_email`,
`Usuario`.`login` AS `Usuario_login`,
`Usuario`.`nome` AS `Usuario_nome`,
`Usuario`.`permissao_cliente` AS `Usuario_permissao_cliente`,
`Usuario`.`permissao_colheita` AS `Usuario_permissao_colheita`,
`Usuario`.`permissao_cultura` AS `Usuario_permissao_cultura`,
`Usuario`.`permissao_estoque` AS `Usuario_permissao_estoque`,
`Usuario`.`permissao_exclusao` AS `Usuario_permissao_exclusao`,
`Usuario`.`permissao_insumo` AS `Usuario_permissao_insumo`,
`Usuario`.`permissao_logo` AS `Usuario_permissao_logo`,
`Usuario`.`permissao_manejo` AS `Usuario_permissao_manejo`,
`Usuario`.`permissao_ocorrencia` AS `Usuario_permissao_ocorrencia`,
`Usuario`.`permissao_pedido` AS `Usuario_permissao_pedido`,
`Usuario`.`permissao_perda` AS `Usuario_permissao_perda`,
`Usuario`.`permissao_precificacao` AS `Usuario_permissao_precificacao`,
`Usuario`.`permissao_propriedade` AS `Usuario_permissao_propriedade`,
`Usuario`.`permissao_relatorio` AS `Usuario_permissao_relatorio`,
`Usuario`.`permissao_safra` AS `Usuario_permissao_safra`,
`Usuario`.`permissao_servico` AS `Usuario_permissao_servico`,
`Usuario`.`permissao_usuario` AS `Usuario_permissao_usuario`,
`Usuario`.`responsavel_atualizacao_id` AS `Usuario_responsavel_atualizacao_id`,
`Usuario`.`responsavel_cadastro_id` AS `Usuario_responsavel_cadastro_id`,
`Usuario`.`telefon` AS `Usuario_telefon`,
`Usuario`.`updatedAt` AS `Usuario_updatedAt`,
`Usuario`.`empresaID` AS `Usuario_empresaID`,
`Propriedade`.`id` AS `Propriedade_id`,
`Propriedade`.`cpf_cnpj` AS `Propriedade_cpf_cnpj`,
`Propriedade`.`createdAt` AS `Propriedade_createdAt`,
`Propriedade`.`nome` AS `Propriedade_nome`,
`Propriedade`.`propriedadeEnderecoPropriedadeId` AS `Propriedade_propriedadeEnderecoPropriedadeId`,
`Propriedade`.`responsavel_atualizacao_id` AS `Propriedade_responsavel_atualizacao_id`,
`Propriedade`.`responsavel_cadastro_id` AS `Propriedade_responsavel_cadastro_id`,
`Propriedade`.`updatedAt` AS `Propriedade_updatedAt`,
`Propriedade`.`empresaID` AS `Propriedade_empresaID`,
`Empresa`.`id` AS `Empresa_id`,
`Empresa`.`cpf_cnpj` AS `Empresa_cpf_cnpj`,
`Empresa`.`createdAt` AS `Empresa_createdAt`,
`Empresa`.`email` AS `Empresa_email`,
`Empresa`.`empresaLicensaEmpresaId` AS `Empresa_empresaLicensaEmpresaId`,
`Empresa`.`nome` AS `Empresa_nome`,
`Empresa`.`nome_fantasia` AS `Empresa_nome_fantasia`,
`Empresa`.`telefone` AS `Empresa_telefone`,
`Empresa`.`updatedAt` AS `Empresa_updatedAt`,
`Empresa2`.`id` AS `Empresa_id2`,
`Empresa2`.`cpf_cnpj` AS `Empresa_cpf_cnpj2`,
`Empresa2`.`createdAt` AS `Empresa_createdAt2`,
`Empresa2`.`email` AS `Empresa_email2`,
`Empresa2`.`empresaLicensaEmpresa2Id` AS `Empresa_empresaLicensaEmpresaId2`,
`Empresa2`.`nome` AS `Empresa_nome2`,
`Empresa2`.`nome_fantasia` AS `Empresa_nome_fantasia2`,
`Empresa2`.`telefone` AS `Empresa_telefone2`,
`Empresa2`.`updatedAt` AS `Empresa_updatedAt2`,
`PropriedadeUsuario`.`id` AS `PropriedadeUsuario_id`,
`PropriedadeUsuario`.`createdAt` AS `PropriedadeUsuario_createdAt`,
`PropriedadeUsuario`.`responsavel_atualizacao_id` AS `PropriedadeUsuario_responsavel_atualizacao_id`,
`PropriedadeUsuario`.`responsavel_cadastro_id` AS `PropriedadeUsuario_responsavel_cadastro_id`,
`PropriedadeUsuario`.`updatedAt` AS `PropriedadeUsuario_updatedAt`,
`PropriedadeUsuario`.`propriedadeID` AS `PropriedadeUsuario_propriedadeID`,
`PropriedadeUsuario`.`usuarioID` AS `PropriedadeUsuario_usuarioID`
FROM
`PropriedadeUsuario`
LEFT JOIN `Propriedade` ON `PropriedadeUsuario`.`propriedadeID` = `Propriedade`.`id`
LEFT JOIN `Empresa` ON `Propriedade`.`empresaID` = `Empresa`.`id`
LEFT JOIN `Usuario` ON `PropriedadeUsuario`.`usuarioID` = `Usuario`.`id`
LEFT JOIN `Empresa` AS `Empresa2` ON `Usuario`.`empresaID` = `Empresa2`.`id`
WHERE
`PropriedadeUsuario`.`usuarioID` = ?;
A fix for the issue has been released in Amplify Android 1.38.1 and 2.3.0.
Comments on closed issues are hard for our team to see. If you need more assistance, please either tag a team member or open a new issue that references this one. If you wish to keep having a conversation with other community members under this issue feel free to do so.
Description
Having an SQL error on Flutter using DataStore shows fields with the added digit "2" at the end. like "User2.authUser2ID",
User2
.email
ASUser_email2
,User_updatedAt2
,User2
and so on.My model doesn't have any field with "2" in the end. I checked how it looks on Amplify Studio and in the Dynamo DB table. There are no such fields like "email2", "updateAt2" etc. The app code doesn't have these fields (neither the code nor the generated files). From where do these fields come from?
Categories
Steps to Reproduce
Screenshots
No response
Platforms
Android Device/Emulator API Level
API 31
Environment
Dependencies
Device
OS
Mac
Deployment Method
Amplify CLI
CLI Version
9.2.1
Additional Context
No response
Amplify Config
GraphQL schema