cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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

Accepted Solutions (0)

Answers (1)

Answers (1)

lbreddemann
Active Contributor
0 Kudos

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

- Lars

Former Member
0 Kudos

Thank you for this information - well, I would like to use SP9... but we can't use SP9 yet till it is released for productive use which i guess will still need a few more month.

lbreddemann
Active Contributor
0 Kudos

I don't have a SPS 8 with rev. > 80 at hand, so I cannot try that.

In any case you can always also use the support channel to get a definitive statement on this apparent bug.

- Lars

Former Member
0 Kudos

Thx Lars, I'll try that as well 🙂

Former Member
0 Kudos

I can confirm that this issue does no longer appear in SP09 or later...