problem on oData query with FK on the same table

[mirco73]

Hi,

we're facing some problems with Teiid 9.1.4 on a particular oData query

We have a virtual table like this:

EmployeesEntity:

EmployeeID (int)

FirstName (varchar(50))

LastName (varchar(50))

Delegate (int)

DeputyDelegate (int)

TimeSheetDelegate (int)

with the following keys

Primary key

PK_EmployeeID -> EmployeesEntity.EmployeeID

Foreign key

delegates -> EmployeesEntity.Delegate ref_to: PK_EmployeeID

Foreign key

deputyDelegates -> EmployeesEntity.DeputyDelegate ref_to: PK_EmployeeID

Foreign key

timeSheetDelegates -> EmployeesEntity.TimeSheetDelegate ref_to: PK_EmployeeID

that is, a delegate, a deputy delegate or a time sheet delegate is an employee

When we execute an oData query the generated SQL query in Teiid has always the same JOIN ON clause

ex:

1- http://casteiid01.casalelug.ch:8080/odata4/NORTHWIND.1/ViewNorthwind/EmployeesEntity(8)/delegates

2- http://casteiid01.casalelug.ch:8080/odata4/NORTHWIND.1/ViewNorthwind/EmployeesEntity(8)/deputyDelegates

3- http://casteiid01.casalelug.ch:8080/odata4/NORTHWIND.1/ViewNorthwind/EmployeesEntity(8)/timeSheetDelegates

the correponding SQL query, as reported on the logs, are always:

SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.Delegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

while the correct ones should be:

1- SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.Delegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

2- SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.DeputyDelegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

3- SELECT g6.EmployeeID, g6.LastName, g6.FirstName, g6.Delegate, g6.DeputyDelegate, g6.TimeSheetDelegate FROM ViewNorthwind.EmployeesEntity AS g5 INNER JOIN ViewNorthwind.EmployeesEntity AS g6 ON g6.EmployeeID = g5.TimeSheetDelegate WHERE g5.EmployeeID = 8 ORDER BY g6.EmployeeID

thanks,

Mirco

[shawkins]

This seems similar to [TEIID-4754] Wrong result with bidirectional relationship - JBoss Issue Tracker

I'll double check the logic to see if there is something overlooked for a self relationship.

[shawkins]

There is indeed a problem with how we are determining which foreign key to use.  Logged as [TEIID-4852] Wrong OData results with multiple self relationships - JBoss Issue Tracker