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

[Christoph John (Jira)]

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

Christoph John commented on TEIID-5763:
---------------------------------------

Hello Steven, here is the first example:

{code:java}
CREATE VIEW QuicklyAddedProducts (
	fkProduct long NOT NULL AUTO_INCREMENT,
	fkProfile long NOT NULL,
	product_name string(20) NOT NULL,
	energy_100g double  NOT NULL,
	carbohydrates_100g double  NOT NULL,
	proteins_100g double  NOT NULL,
	fat_100g double  NOT NULL,	
	CONSTRAINT "PRIMARY" PRIMARY KEY(fkProduct),
	CONSTRAINT fkQuicklyAddedProductsToProducts FOREIGN KEY(fkProduct) REFERENCES Products(idProduct),
	CONSTRAINT fkQuicklyAddedProductsToAccount FOREIGN KEY(fkProfile) REFERENCES Account(idProfile),
	CONSTRAINT fkQuicklyAddedProductsToAccount_idx INDEX(fkProfile)
)
OPTIONS(UPDATABLE 'TRUE')
AS
SELECT
	qap.fkProduct as fkProduct,
	qap.fkProfile as fkProfile,
	qap.product_name as product_name,
	qap.energy_100g as energy_100g,
	qap.carbohydrates_100g as carbohydrates_100g,
	qap.proteins_100g as proteins_100g,
	qap.fat_100g as fat_100g
FROM QuicklyAddedProducts_SRC as qap;
-- ///////////
CREATE TRIGGER ON QuicklyAddedProducts INSTEAD OF INSERT AS
	FOR EACH ROW 
	BEGIN ATOMIC
   		DECLARE long vIdProduct;
		INSERT INTO 
			Products(fkDatabaseKey)
		VALUES
			(2);		
		vIdProduct = cast(generated_key('idProduct') as long);
		key.fkProduct = vIdProduct;
		-- create a new record for the quickly added product
		INSERT INTO 
			QuicklyAddedProducts_SRC(fkProfile, product_name, energy_100g, carbohydrates_100g, proteins_100g, fat_100g)
		VALUES
			(new.fkProfile, new.product_name, new.energy_100g, new.carbohydrates_100g, new.proteins_100g, new.fat_100g);
	END;
{code}

When I sent the following request to add a new record

{"fkProduct":"0","fkProfile":"1","product_name":"This is a test","energy_100g":1255,"carbohydrates_100g":60.08,"proteins_100g":24.02,"fat_100g":15.89}

{"@odata.context":"https://morpheus.fritz.box/odata4/svc/my_nutri_diary/$metadata#QuicklyAddedProducts","{color:red}fkProduct":"0"{color},"fkProfile":"1","product_name":"This is a test","energy_100g":1255.0,"carbohydrates_100g":60.08,"proteins_100g":24.02,"fat_100g":15.89}


I get this object back. As a matter of principle in the past I always had to send a dummy for the primary key. Here fkProduct is the foreign key. Note, without defining a INSERT Trigger, the response correctly contains the key of the newly created item. In case of having a custom insert, simply the default dummy value is sent back. Hence if I would sent "1"as dummy, I receive "1" back instead of "0" like in the example above. However, the real autogenerated key which I would expect is a different one.



> 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)