Skip to Content
avatar image
Former Member

Issue with SQL statement (sequence.NEXTVAL) in SAP HANA SP80

Hi Experts,

SAP HANA fails to execute an insert statement for a global temp table with a sequence.next value evaluating and joining with a non empty column table.

The error message is as follows:

Could not execute 'INSERT INTO "Temp_IDs" ("ID",indexid) SELECT ID, "index_seq".NEXTVAL orderId FROM ...' in 3.777 seconds.

After that it seems that the indexserver crashes and brings the whole hana instance offline for some time. (Crash dump can be attached if needed)

The following isolated SQL script can reproduce the error on any SAP HANA instance version 80 or lower:

CREATE GLOBAL TEMPORARY COLUMN TABLE "Temp_IDs" (     "INDEXID" BIGINT CS_FIXED,     "ID" VARCHAR(36) );

CREATE sequence "index_seq" increment by 1 start with 1 minvalue 1 maxvalue 4611686018427387903 no cycle;
CREATE COLUMN TABLE "xyz::entity" (     "ID" VARCHAR(36) NOT NULL,     "IDTYPE" VARCHAR(36) NOT NULL,     "SOURCESYSTEM" VARCHAR(255), PRIMARY KEY ("ID") );


INSERT INTO "xyz::entity" (ID, IDTYPE) VALUES ('6A33824A-082A-4699-AC02-ADCB514C71E6', '6A33824A-082A-4699-AC02-ADCB514C71E6');
INSERT INTO  "Temp_IDs" ("ID",indexid) SELECT ID, "index_seq".NEXTVAL orderId FROM "xyz"."xyz::entity" LIMIT 1;


truncate table "Temp_IDs";
drop table  "Temp_IDs";
drop table "xyz::entity";
drop sequence "index_seq";

(Please note: if Temp_IDs is not a temp table, but a regular table then the issue doesn't occur; If there is no "index_seq".NEXTVAL call in the insert statement then the issue doesn't occur)

Does anybody know a proper "workaround" for this scenario (e.g. to get the next value from a sequence like sequence.NEXTVAL without calling sequence.NEXTVAL)?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 19, 2015 at 02:53 PM

    Just tested this on SP9 - no crash... I recommend to use a higher revision.

    - Lars

    Add comment
    10|10000 characters needed characters exceeded