on 09-08-2015 2:22 PM
While developing HANA SQL Script procedures (in HANA SP07 revision 74) we've had several occurrences of SQL Script performing incorrectly due to HANA's optimization for parallel execution.
It seems that any procedure with the following pattern could give incorrect behaviour...
(1) select some data, joining on several tables
(2) call another procedure that updates one of the tables
(3) select the data again using same/similar SQL as step-1.
We are finding that the optimizer sometimes ignores the fact that the other procedure may modify the data, and the step (3) query returns data that does not use the updates, presumably because of parallel execution or data caching or whatever.
Today I've put together a toy example that replicates/proves the problem. The code only produces the correct result if I force sequential execution using the 'SEQUENTIAL EXECUTION'. It also works fine in the debugger, for the same reason.
This seems like a very serious issue as it makes SQL Scripts rather untrustworthy! The incorrect results may not always be noticeable immediately, and when they are noticed it can be very hard to track down what is going on. We have had to workaround this problem by moving parts of our procedures into separate sub procedures, to prevent HANA trying to perform SQL in parallel incorrectly. For example, we would move step-3 into a separate procedure.
Have others found the same problem? Is this something known to be fixed in later revisions?
Here is the toy example database table, table variable type, procedure code, and console query...
table 'MYITEM'
----------------------------------------
table.schemaName = "...";
table.tableType = COLUMNSTORE;
table.columns = [
{name = "ITEM_ID"; sqlType = VARCHAR; nullable = false; length = 20; comment = "dummy comment";},
{name = "ITEM_VER"; sqlType = INTEGER; nullable = false; comment = "dummy comment";},
{name = "IS_CURRENT"; sqlType = TINYINT; nullable = false;}];
table.primaryKey.pkcolumns = ["ITEM_ID","ITEM_VER"];
----------------------------------------
TYPE "TYPE1" {
ID : String(20);
VER : Integer;
};
----------------------------------------
PROCEDURE "PROC1" (OUT tt_out "..TYPE1" )
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
-- SEQUENTIAL EXECUTION -- uncomment to make the code work correctly!
item_ids = SELECT 'ITEM-1' AS ID FROM DUMMY;
details =
SELECT
iid.ID,
item.ITEM_VER AS VER
FROM
:item_ids iid
INNER JOIN "MYITEM" item ON (item.ITEM_ID = iid.ID) AND (item.IS_CURRENT = 1);
CALL "PROC2"(:details);
tt_out =
SELECT
iid.ID,
item.ITEM_VER AS VER
FROM
:item_ids iid
INNER JOIN "MYITEM" item ON (item.ITEM_ID = iid.ID) AND (item.IS_CURRENT = 1);
END;
---------------------------------------
PROCEDURE "...PROC2" (IN tt_items "...TYPE1")
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
new_versions =
SELECT
item.ITEM_ID,
item.ITEM_VER + 1 AS ITEM_VER
FROM
"MYITEM" item,
:tt_items tti
WHERE
item.ITEM_ID = tti.ID AND
item.ITEM_VER = tti.VER;
UPDATE "MYITEM" item
SET IS_CURRENT = 0
FROM
:tt_items tti
WHERE
item.ITEM_ID = tti.ID AND
item.ITEM_VER = tti.VER;
INSERT INTO "MYITEM" (item_id, item_ver, is_current)
SELECT
newv.ITEM_ID,
newv.ITEM_VER,
1 AS IS_CURRENT
FROM
:new_versions newv;
END;
----------------------------------
console...
delete from "MYITEM";
insert into "MYITEM" (item_id, item_ver, is_current)
select 'ITEM-1' as ITEM_ID, 1 as ITEM_VER, 1 AS IS_CURRENT FROM DUMMY UNION ALL
select 'ITEM-2' as ITEM_ID, 1 as ITEM_VER, 1 AS IS_CURRENT FROM DUMMY;
call "PROC1"(?);
----------------------------------
You would be best served to open an SAP Incident in the SMP for a question like this. It's likely no one (or at least very few!) on SCN will have detailed information about a specific issue like this.
Regards,
Justin
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.