cancel
Showing results for 
Search instead for 
Did you mean: 

Bug in HANA's parallel execution of SQL Script?

Former Member
0 Kudos

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"(?);

----------------------------------

Accepted Solutions (0)

Answers (1)

Answers (1)

justin_molenaur2
Contributor
0 Kudos

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

Former Member
0 Kudos

Thanks Justin,

We have now raised a SAP incident, and I will post a message here if a useful resolution comes back.

David