[teiid-issues] [JBoss JIRA] (TEIID-5589) NavigationProperty not working with Teiid odata, results in TEIID16053

[Ramesh Reddy (Jira)]

    [ https://issues.jboss.org/browse/TEIID-5589?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13680109#comment-13680109 ] 

Ramesh Reddy edited comment on TEIID-5589 at 1/9/19 3:08 PM:
-------------------------------------------------------------

Can you post the DDL between your Account and Profile Table? I think either the association between them is incorrect, or reverse navigation is code in the Teiid is wrong. According to the metadata, the association from Profile and Account should be on "idProfile" property, but it selected a PK property "uuidUser"

If in DDL you have like on Profile table

CONSTRAINT FOREIGN KEY fkProfileInProfile (fkProfile) References Account (IdProfile)  --> then is may be wrong as Account's IdProfile is not a Primary Key

whereas if you have this on Account table

CONSTRAINT FOREIGN KEY fkProfileInProfile (IdProfile) References Profile (fkProfile)  

then reverse navigation on Teiid side is the culprit IMO. 

Also, "Profile_fkProfileInProfile" defined is also deriving similar navigation between two entities, that seems to be at odds with above.


was (Author: rareddy):
Can you post the DDL between your Account and Profile Table? I think either the association between them is incorrect, or reverse navigation is code in the Teiid is wrong. According to the metadata, the association from Profile and Account should be on "idProfile" property, but it selected a PK property "uuidUser"

If in DDL you have like on Profile table

CONSTRAINT FOREIGN KEY fkProfileInProfile (IdProfile) References Account (IdProfile)  --> then is may be wrong as Account's IdProfile is not a Primary Key

whereas if you have this on Account table

CONSTRAINT FOREIGN KEY fkProfileInProfile (IdProfile) References Profile (IdProfile)  

then reverse navigation on Teiid side is the culprit IMO. 

Also, "Profile_fkProfileInProfile" defined is also deriving similar navigation between two entities, that seems to be at odds with above.

> NavigationProperty not working with Teiid odata, results in  TEIID16053 
> ------------------------------------------------------------------------
>
>                 Key: TEIID-5589
>                 URL: https://issues.jboss.org/browse/TEIID-5589
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 11.2.1
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Major
>         Attachments: AccountRecord.xml, metadata.xml
>
>
> Teiid throws an error in case it tries to follow an odata Navigation property. I tried this by using $expand and also by directly following a path. Two examples are shown below. My $metaddata.xml file is attached, and also a single record from the Account table in which the relevant comparison seems to happen. the uuidUser property you can find here.
> I do not understand the error message. What is not clear to me is the assignment which is mentioned there g1.uuidUser = g0.fkProfile 
> Just for your awareness, uuidUser and fkProfile are different columns of different types that should not be compared in anyway. Maybe this is a hint what is going wrong. I would the relevant NavigationProperty expect to be
> <NavigationProperty Name="fkProfileInProfile" Type="my_nutri_diary.Account" Nullable="false">
> <ReferentialConstraint Property="fkProfile" ReferencedProperty="idProfile"/>
> Here are two example queries which fail with the same error
> http://localhost:8080/odata4/vdb/my_nutri_diary/Profile(38)?$expand=fkProfileInProfile
> http://localhost:8080/odata4/vdb/my_nutri_diary/Profile(38)/fkProfileInProfile
> <error xmlns="http://docs.oasis-open.org/odata/ns/metadata">
> <code>TEIID31172</code>
> <message>
> TEIID31172 Could not resolve expressions being compared to a common type excluding character conversions: g1.uuidUser = g0.fkProfile
> </message>
> </error>
> ----------------------
> There might also exist a bug in Teiid Designer, when changing the private key and reimporting the database, the source in Teiid Designer looks as expected. However, a sync of the vdb seems not to work properly. For the workaround described in this ticket, I had to create a new vdb in order to get things to work correctely.



--
This message was sent by Atlassian Jira
(v7.12.1#712002)