odata4 multiple foreign keys issue

[mirco73]

Hi,

after installing version 9.1.3 with the fix of TEIID-4298 the reverse navigation seems to work fine.

We've facing another problem in the oData navigation in case of two foreign keys between two tables:

Example:

EmployeeMasterEntity (PK EmployeeID, FK Departments)

OrganizationalUnitEntity (PK OrganizationalUnitID, FK Managers)

With following data (case 1):

EmployeeMasterEntity

EmployeeID                  Department

1                                  10000001 

2                                  10000002 

3                                  10000003 

OrganizationalUnitEntity

OrganizationaUnitID      UnitManager

10000001                     1

10000002                     2

10000003                     3

the following queries work fine:

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(1)/Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(2)/Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000001')/Managers?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000002')/Managers?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/Managers?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000001')/EmployeeMasterEntity_Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000002')/EmployeeMasterEntity_Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/EmployeeMasterEntity_Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(1)/OrganizationalUnitEntity_Managers?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(2)/OrganizationalUnitEntity_Managers?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/OrganizationalUnitEntity_Managers?$format=json

With following data (Case 2):

EmployeeMasterEntity

EmployeeID                       Department

1                                       10000001 

2                                       10000002 

3                                       10000001 

OrganizationalUnitEntity

OrganizationaUnitID           UnitManager

10000001                          1

10000002                          2

10000003                          2

the following queries don't work, since they return no values (blank page):

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/Departments?$format=json

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/Managers?$format=json

It seems that the query makes a sort of inner join between the two foreign keys. In case 1 EmployeeID 3 references to Department 10000003 and

Departement 10000003 references to EmployeeID 3

In case 2 EmployeeID 3 references to Department 10000001 but Department 10000001 references to EmployeeID 2

The query

http://localhost:8080/odata4/TestOdata.1/InterlemView/EmployeeMasterEntity(3)/Departments?$format=json

should return Department 10000003, while query

http://localhost:8080/odata4/TestOdata.1/InterlemView/OrganizationalUnitEntity('10000003')/Managers?$format=json

Should return Employee 2

Thanks,

Mirco

[shawkins]

I'll have a look to see if I can recreate what you are seeing.

[shawkins]

The issue is that we're building the join using the wrong foreign key.  Can you log an issue for this?

[shawkins]

Logged and worked this as [TEIID-4754] Wrong result with bidirectional relationship - JBoss Issue Tracker