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

Wednesday, 9 January 2019

    [
https://issues.jboss.org/browse/TEIID-5589?page=com.atlassian.jira.plugin...
] 

Christoph John commented on TEIID-5589:
---------------------------------------

Hello Ramesh,
if have rebuild an example. So here this navigation path fails for example

http://localhost:8080/odata4/test/test/Profile(38)/fkProfileToAccount

CREATE FOREIGN TABLE Account (
	idProfile long NOT NULL AUTO_INCREMENT OPTIONS (ANNOTATION '', NAMEINSOURCE
'`idProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),
	uuidUser string(36) NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE
'`uuidUser`', NATIVE_TYPE 'CHAR'),
	CONSTRAINT "PRIMARY" PRIMARY KEY(uuidUser),
	CONSTRAINT idProfile_UNIQUE UNIQUE(idProfile),
	CONSTRAINT uuidUser_UNIQUE UNIQUE(uuidUser)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Account`', UPDATABLE TRUE);

CREATE FOREIGN TABLE Avatar (
	fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE
'`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),
	AvatarImg blob(65535) OPTIONS (ANNOTATION '', NAMEINSOURCE '`AvatarImg`',
NATIVE_TYPE 'BLOB'),
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
	CONSTRAINT fkProfile_UNIQUE UNIQUE(fkProfile),
	CONSTRAINT fkAvatarToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Avatar`', UPDATABLE TRUE);

CREATE FOREIGN TABLE BodyWeight (
	idBodyWeight long NOT NULL AUTO_INCREMENT OPTIONS (ANNOTATION 'We need a surrogate
key here as Teiid requires a primary key on each table. fkProfile is not unique here as we
can will have multiple weight measurements per person. Moreover, combining it with
WeightMeasurementDateTime makes the index to compute intensive.', NAMEINSOURCE
'`idBodyWeight`', NATIVE_TYPE 'BIGINT UNSIGNED'),
	fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE
'`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),
	WeightMeasurementDateTime timestamp NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE
'`WeightMeasurementDateTime`', NATIVE_TYPE 'DATETIME'),
	Weight float NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE '`Weight`',
NATIVE_TYPE 'FLOAT UNSIGNED'),
	CONSTRAINT "PRIMARY" PRIMARY KEY(idBodyWeight),
	CONSTRAINT idBodyWeight_UNIQUE UNIQUE(idBodyWeight),
	CONSTRAINT fkBodyWeightToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`BodyWeight`', UPDATABLE TRUE);

CREATE FOREIGN TABLE ConfigOptions (
	fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE
'`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),
	MUnitWeight string(2) DEFAULT 'kg' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`MUnitWeight`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'ENUM'),
	MUnitLength string(2) DEFAULT 'cm' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`MUnitLength`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'ENUM'),
	MUnitEnergy string(4) DEFAULT 'kcal' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`MUnitEnergy`', CHAR_OCTET_LENGTH 16, NATIVE_TYPE 'ENUM'),
	MUnitLiquids string(2) DEFAULT 'ml' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`MUnitLiquids`', CHAR_OCTET_LENGTH 8, NATIVE_TYPE 'ENUM'),
	MUnitTime string(3) DEFAULT '24h' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`MUnitTime`', CHAR_OCTET_LENGTH 12, NATIVE_TYPE 'ENUM'),
	EnableDrinkReminder boolean DEFAULT '0' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`EnableDrinkReminder`', NATIVE_TYPE 'BIT'),
	PreferredContryCodeForSearch string(2) DEFAULT 'de' OPTIONS (ANNOTATION
'', NAMEINSOURCE '`PreferredContryCodeForSearch`', CHAR_OCTET_LENGTH 8,
NATIVE_TYPE 'VARCHAR'),
	EnableInternationalSearch boolean DEFAULT '1' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`EnableInternationalSearch`', NATIVE_TYPE 'BIT'),
	NumberOfActiveMeals long DEFAULT '4' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`NumberOfActiveMeals`', NATIVE_TYPE 'INT UNSIGNED'),
	Meal1Name string(45) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Meal1Name`',
CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
	Meal1StartTime time DEFAULT '00:00:00' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`Meal1StartTime`', NATIVE_TYPE 'TIME'),
	Meal2Name string(45) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Meal2Name`',
CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
	Meal2StartTime time DEFAULT '00:00:00' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`Meal2StartTime`', NATIVE_TYPE 'TIME'),
	Meal3Name string(45) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Meal3Name`',
CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
	Meal3StartTime time DEFAULT '00:00:00' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`Meal3StartTime`', NATIVE_TYPE 'TIME'),
	Meal4Name string(45) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Meal4Name`',
CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
	Meal4StartTime time DEFAULT '00:00:00' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`Meal4StartTime`', NATIVE_TYPE 'TIME'),
	Meal5Name string(45) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Meal5Name`',
CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
	Meal5StartTime time DEFAULT '00:00:00' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`Meal5StartTime`', NATIVE_TYPE 'TIME'),
	Meal6Name string(45) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Meal6Name`',
CHAR_OCTET_LENGTH 180, NATIVE_TYPE 'VARCHAR'),
	Meal6StartTime time DEFAULT '00:00:00' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`Meal6StartTime`', NATIVE_TYPE 'TIME'),
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
	CONSTRAINT fkProfile_UNIQUE UNIQUE(fkProfile),
	CONSTRAINT fkConfigOptionsToAccount FOREIGN KEY(fkProfile) REFERENCES Account
(idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`ConfigOptions`', UPDATABLE
TRUE);

CREATE FOREIGN TABLE Profile (
	fkProfile long NOT NULL OPTIONS (ANNOTATION '', NAMEINSOURCE
'`fkProfile`', NATIVE_TYPE 'BIGINT UNSIGNED'),
	BodyHeight float DEFAULT '0' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`BodyHeight`', NATIVE_TYPE 'FLOAT UNSIGNED'),
	GoalWeight float DEFAULT '0.000' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`GoalWeight`', NATIVE_TYPE 'FLOAT UNSIGNED'),
	Gender string(1) DEFAULT 'm' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`Gender`', CHAR_OCTET_LENGTH 4, NATIVE_TYPE 'ENUM'),
	BirthDate date DEFAULT '1980-01-01' OPTIONS (ANNOTATION '', NAMEINSOURCE
'`BirthDate`', NATIVE_TYPE 'DATE'),
	ActivityLevel string(17) DEFAULT 'strActive' OPTIONS (ANNOTATION '',
NAMEINSOURCE '`ActivityLevel`', CHAR_OCTET_LENGTH 68, NATIVE_TYPE
'ENUM'),
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProfile),
	CONSTRAINT fkProfile_UNIQUE UNIQUE(fkProfile),
	CONSTRAINT fkProfileToAccount FOREIGN KEY(fkProfile) REFERENCES Account (idProfile)
) OPTIONS (ANNOTATION '', NAMEINSOURCE '`Profile`', UPDATABLE TRUE);

...
 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=fkPro...
 http://localhost:8080/odata4/vdb/my_nutri_diary/Profile(38)/fkProfileInPr...
 <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)

2025

2024

2023

2022

2021

2020

2019

2018

2017

2016

2015

2014

2013

2012

2011

2010

2009