[teiid-issues] [JBoss JIRA] (TEIID-5763) GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files

Sun Jun 23 10:26:00 EDT 2019

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

Christoph John edited comment on TEIID-5763 at 6/23/19 10:25 AM:
-----------------------------------------------------------------

Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error. 

TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context

It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:

CREATE FOREIGN TABLE UserDefinedProducts_SRC (
	fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
	fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
	CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
	CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
	CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');

The view is:

CREATE VIEW UserDefinedProducts (
	fkProduct long NOT NULL AUTO_INCREMENT,
	fkProfile long NOT NULL,
...
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
	CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
	CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
	CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
	udp.fkProduct as fkProduct,
	udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;

And here is the relevant input trigger:

CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
	FOR EACH ROW 
	BEGIN ATOMIC
   		DECLARE long vIdProduct;

		INSERT INTO 
			Products(fkDatabaseKey)
		VALUES
			(0);

		vIdProduct = cast(generated_key('idProduct') as long);
		key.fkProduct = vIdProduct;
		-- key.fkProfile = new.fkProfile;

		-- create a new record for the quickly added product
		INSERT INTO 
			UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity, 
									serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g, 
									sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier, 
									salt_100g, salt_modifier, fiber_100g)
		VALUES
			(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
			 new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
			 new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
			 new.salt_modifier, new.fiber_100g);
	END;

Do you have an idea what is going wrong?

Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct?

Update: Interestingly, I found out that the procedure is working if I do not set the key explicitly. Hence, I simply removed the line 

key.fkProduct = vIdProduct;

Would be great if you could explain me what is happening here with the error message and why I in the one case have to set the key. explicitly, and why it does not work in the other case. Still the topic has to much trial and error characteristic for me with my current understanding. Thanks for your help.

was (Author: cjohn001):
Hello Steven,
I need to come back on you regarding the generated key topic. I tried now to use the generated key in the trigger function of a different table and always end up with the following error. 

TEIID31080 my_nutri_diary.UserDefinedProducts validation error: TEIID31119 Symbol key.fkProduct is specified with an unknown group context

It does not matter if I take the shortcut generated key, or the explicit cast function. The difference on the current table to the other one is, that the table has a composite primary key. I was wondering if this might be the reason for the issue. I tried to set the second key explicitely on the "key.". Unfortunately, the error remains. The source table definition is as below:

CREATE FOREIGN TABLE UserDefinedProducts_SRC (
	fkProduct long NOT NULL OPTIONS(NAMEINSOURCE '"fkProduct"', NATIVE_TYPE 'BIGINT'),
	fkProfile long NOT NULL OPTIONS(NAMEINSOURCE '"fkProfile"', NATIVE_TYPE 'BIGINT'),
...
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
	CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
	CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
	CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(NAMEINSOURCE '"UserDefinedProducts"', UPDATABLE 'TRUE', CARDINALITY '4');

The view is:

CREATE VIEW UserDefinedProducts (
	fkProduct long NOT NULL AUTO_INCREMENT,
	fkProfile long NOT NULL,
...
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct, fkProfile),
	CONSTRAINT fkUserDefinedProductToProduct FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fKUserDefinedProductToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkProfile_fkProduct_idCode_InProducts_UNIQUE UNIQUE(fkProduct, fkProfile, idCode),
	CONSTRAINT idCodeInUserDefinedProducts_idx INDEX(idCode),
	CONSTRAINT fKUserDefinedProductToAccount_idx INDEX(fkProfile)
) OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
	udp.fkProduct as fkProduct,
	udp.fkProfile as fkProfile,
...
FROM UserDefinedProducts_SRC as udp;

And here is the relevant input trigger:

CREATE TRIGGER ON UserDefinedProducts INSTEAD OF INSERT AS
	FOR EACH ROW 
	BEGIN ATOMIC
   		DECLARE long vIdProduct;

		INSERT INTO 
			Products(fkDatabaseKey)
		VALUES
			(0);

		vIdProduct = cast(generated_key('idProduct') as long);
		key.fkProduct = vIdProduct;
		-- key.fkProfile = new.fkProfile;

		-- create a new record for the quickly added product
		INSERT INTO 
			UserDefinedProducts_SRC(fkProduct, fkProfile, idCode, product_name, origins, brands, quantity, 
									serving_quantity, nova_group, nutrition_grade_fr, energy_100g, carbohydrates_100g, 
									sugars_100g, proteins_100g, fat_100g, saturated_fat_100g, saturated_fat_modifier, 
									salt_100g, salt_modifier, fiber_100g)
		VALUES
			(vIdProduct, new.fkProfile, new.idCode, new.product_name, new.origins, new.brands, new.quantity, new.serving_quantity,
			 new.nova_group, new.nutrition_grade_fr, new.energy_100g, new.carbohydrates_100g, new.sugars_100g,
			 new.proteins_100g, new.fat_100g, new.saturated_fat_100g, new.saturated_fat_modifier, new.salt_100g,
			 new.salt_modifier, new.fiber_100g);
	END;

Do you have an idea what is going wrong?

Maybe I am still misunderstanding the key group. My thinking is that it reflects the keys that are defined in the view. not these of the underlaying table. is this correct?

> GENERATED_KEY returns NULL if used in INSTEAD OF INSERT Triggers in DDL files
> -----------------------------------------------------------------------------
>
>                 Key: TEIID-5763
>                 URL: https://issues.jboss.org/browse/TEIID-5763
>             Project: Teiid
>          Issue Type: Bug
>    Affects Versions: 12.2
>            Reporter: Christoph John
>            Assignee: Steven Hawkins
>            Priority: Critical
>             Fix For: 12.3
>
>
> The issue arrised in:
> https://developer.jboss.org/message/989700#989700
> Following sceanarios are  given:
> Variant 1:
> Table Product(id), primary key = autoincrement;
> Table QuicklyAddedProduct, primary key is foreign key on Product.id
> Table Diary_SRC
> View Diary on Diary_SRC
> INSTEAD OF INSERT Trigger on Diary should :
> -create new record on Product, 
> -get autoincremented Product.id of new record,
> - create new QuicklyAddedProduct with returned Product.id as primary key
> - create new Diary record with QuicklyAddedProduct 
> {
>    INSERT a new record on Product;
>    idProduct = CONVERT(GENERATED_KEY('idProduct'),long); // fails
> }
> Variant 2:
> INSTEAD OF INSERT trigger on Diary should :
> - create new QuicklyAddedProduct 
> - add Quickly added prodcut to Diary
> additionally a INSTEAD OF trigger on QuicklyAddedProduct exists which:
> - creates new Product record
> - uses returned GENERATED_KEY(Product.id) to add record on QuicklyAddedProduct with Product.id as primary key

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