Creating a Sequence with Mixed Case Name vs UPPER CASE name Problem

[Former Member]

I have noticed with HANA, that it appears to be lenient with the case of Column and table names (ie, lower, UPPER, MiXeD cases); however, there does seem to be an issue when creating a sequence with Mixed case.

If I do

create sequence "acl_sid_KEY_SEQ" increment by 1 start with 1 no cycle ;

SELECT ACL_SID_KEY_SEQ.NEXTVAL from DUMMY;

SELECT acl_sid_KEY_SEQ.NEXTVAL from DUMMY;

Both selects will fail:

Could not execute 'SELECT ACL_SID_KEY_SEQ.NEXTVAL from DUMMY' in 21 ms 897 µs . SAP DBTech JDBC: [313] (at 7): invalid sequence: ACL_SID_KEY_SEQ: line 1 col 8 (at pos 7)

Could not execute 'SELECT acl_sid_KEY_SEQ.NEXTVAL from DUMMY' in 21 ms 903 µs . SAP DBTech JDBC: [313] (at 7): invalid sequence: ACL_SID_KEY_SEQ: line 1 col 8 (at pos 7)

However, if I keep the case of the sequence all upper case, I can do:

create sequence "ACL_SID_KEY_SEQ" increment by 1 start with 1 no cycle ;

SELECT ACL_SID_KEY_SEQ.NEXTVAL from DUMMY;

SELECT acl_sid_KEY_SEQ.NEXTVAL from DUMMY;

Both selects return with 1, then 2 respectively.

Is this a bug?

[lbreddemann]

Nope, no bug.

When you don't enclose identifiers in double quotation marks (") they will be converted to upper case.

So, in your second example, you're actually querying the very same sequence while in your first example you're not specifying the correct sequence name.

- Lars

[Former Member]

Thank for the information. Is this behavior documented somewhere?

[lbreddemann]

Sure - somewhere in the documentation 😉

SAP HANA Reference: Introduction

It's pretty much the same behavior you'd find in Oracle.

- Lars