[hana] identifier too long

[dimrat]

Description of erroneous behaviour

We have a large normalized data model, resulting in requests coming from the ui with multiple nested expands. Our entities have pretty long names so in some cases we are reaching the hana identifier limit, effectively breaking our application.

Think of something like

GET http://localhost:4004/odata/v4/catalog/books?$expand=longEntityName1/longEntityName2/longEntityName3...

The application worked with the old db adapter. I believe the way alias are generated has changed.

Detailed steps to reproduce

I hope you understand the issue but i can provide a github repo, if necessary.

Details about your project

@cap-js/asyncapi	1.0.2
@cap-js/cds-types	0.6.5
@cap-js/db-service	1.17.2
@cap-js/hana	1.6.1
@cap-js/openapi	1.1.1
@sap/cds	8.7.1
@sap/cds-compiler	5.7.0
@sap/cds-foss	5.0.1
Node.js	v20.17.0

[patricebender]

Hi @dimrat,

could you please provide such a failing SQL statement, so that we can understand where the identifier limit is reached.

→ you can use DEBUG=sql env variable to get the SQLs logged.

Thanks.

[dimrat]

Hi @patricebender ,

it's 127 chars. Here's an (exaggerated) sample to reproduce it

data-model

using { cuid } from '@sap/cds/common';

namespace sap.capire.bookshop;

entity veryVeryVeryVeryVeryVeryVeryLongEntityName1 : cuid {
  veryVeryVeryVeryVeryVeryVeryLongEntityName2 : Association to veryVeryVeryVeryVeryVeryVeryLongEntityName2 
}

entity veryVeryVeryVeryVeryVeryVeryLongEntityName2 : cuid {
  veryVeryVeryVeryVeryVeryVeryLongEntityName3 : Association to veryVeryVeryVeryVeryVeryVeryLongEntityName3 
}

entity veryVeryVeryVeryVeryVeryVeryLongEntityName3 : cuid {}

service

service AdminService {
  entity veryVeryVeryVeryVeryVeryVeryLongEntityName1 as projection on my.veryVeryVeryVeryVeryVeryVeryLongEntityName1;
}

http request

###
GET http://localhost:4005/odata/v4/admin/veryVeryVeryVeryVeryVeryVeryLongEntityName1?$expand=veryVeryVeryVeryVeryVeryVeryLongEntityName2($expand=veryVeryVeryVeryVeryVeryVeryLongEntityName3)
Authorization: alice:

error

[cds] - Error: identifier is too long: the identifer "VERYVERYVERYVERYVERYVERYVERYLONGENTITYNAME1_VERYVERYVERYVERYVERYVERYVERYLONGENTITYNAME2_VERYVERYVERYVERYVERYVERYVERYLONGENTITYNAME3" is too long. Maximum length is 127: line 1 col 1571 (at pos 1570)
    at Socket.<anonymous> (/home/dr/cap8-alias-bug/node_modules/@sap/hana-client/lib/index.js:53:13)
    at Socket.emit (node:events:519:28)
    at addChunk (node:internal/streams/readable:559:12)
    at readableAddChunkPushByteMode (node:internal/streams/readable:510:3)
    at Readable.push (node:internal/streams/readable:390:5)
    at Pipe.onStreamRead (node:internal/stream_base_commons:191:23)
    at Pipe.callbackTrampoline (node:internal/async_hooks:130:17) {
  code: 286,
  sqlState: 'HY000',
  query: `WITH veryVeryVeryVeryVeryVeryVeryLongEntityName1 as (SELECT *,('$[' || lpad("$$RN$$",6,'0')) as _path_ FROM (SELECT *,ROW_NUMBER() OVER ( ORDER BY ID ASC) as "$$RN$$" FROM (SELECT veryVeryVeryVeryVeryVeryVeryLongEntityName1.ID,veryVeryVeryVeryVeryVeryVeryLongEntityName1.veryVeryVeryVeryVeryVeryVeryLongEntityName2_ID FROM AdminService_veryVeryVeryVeryVeryVeryVeryLongEntityName1 as veryVeryVeryVeryVeryVeryVeryLongEntityName1 ORDER BY veryVeryVeryVeryVeryVeryVeryLongEntityName1.ID ASC LIMIT 1000) as veryVeryVeryVeryVeryVeryVeryLongEntityName1) as veryVeryVeryVeryVeryVeryVeryLongEntityName1),veryVeryVeryVeryVeryVeryVeryLongEntityName1_veryVeryVeryVeryVeryVeryVeryLongEntityName2 as (SELECT *,((_parent_path_ || '].veryVeryVeryVeryVeryVeryVeryLongEntityName2[') || lpad("$$RN$$",6,'0')) as _path_ FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY _parent_path_) as "$$RN$$" FROM (SELECT veryVeryVeryVeryVeryVeryVeryLongEntityName2.ID,veryVeryVeryVeryVeryVeryVeryLongEntityName2.veryVeryVeryVeryVeryVeryVeryLongEntityName3_ID,veryVeryVeryVeryVeryVeryVeryLongEntityName1._path_ as _parent_path_ FROM sap_capire_bookshop_veryVeryVeryVeryVeryVeryVeryLongEntityName2 as veryVeryVeryVeryVeryVeryVeryLongEntityName2 inner JOIN veryVeryVeryVeryVeryVeryVeryLongEntityName1 as veryVeryVeryVeryVeryVeryVeryLongEntityName1 ON veryVeryVeryVeryVeryVeryVeryLongEntityName1.veryVeryVeryVeryVeryVeryVeryLongEntityName2_ID = veryVeryVeryVeryVeryVeryVeryLongEntityName2.ID) as veryVeryVeryVeryVeryVeryVeryLongEntityName2) as veryVeryVeryVeryVeryVeryVeryLongEntityName2 WHERE "$$RN$$" = 1),veryVeryVeryVeryVeryVeryVeryLongEntityName1_veryVeryVeryVeryVeryVeryVeryLongEntityName2_veryVeryVeryVeryVeryVeryVeryLongEntityName3 as (SELECT *,((_parent_path_ || '].veryVeryVeryVeryVeryVeryVeryLongEntityName3[') || lpad("$$RN$$",6,'0')) as _path_ FROM (SELECT *,ROW_NUMBER() OVER (PARTITION BY _parent_path_) as "$$RN$$" FROM (SELECT veryVeryVeryVeryVeryVeryVeryLongEntityName3.ID,veryVeryVeryVeryVeryVeryVeryLongEntityName2._path_ as _parent_path_ FROM sap_capire_bookshop_veryVeryVeryVeryVeryVeryVeryLongEntityName3 as veryVeryVeryVeryVeryVeryVeryLongEntityName3 inner JOIN veryVeryVeryVeryVeryVeryVeryLongEntityName1_veryVeryVeryVeryVeryVeryVeryLongEntityName2 as veryVeryVeryVeryVeryVeryVeryLongEntityName2 ON veryVeryVeryVeryVeryVeryVeryLongEntityName2.veryVeryVeryVeryVeryVeryVeryLongEntityName3_ID = veryVeryVeryVeryVeryVeryVeryLongEntityName3.ID) as veryVeryVeryVeryVeryVeryVeryLongEntityName3) as veryVeryVeryVeryVeryVeryVeryLongEntityName3 WHERE "$$RN$$" = 1) SELECT * FROM (SELECT _path_ as "_path_",'{}' as "_blobs_",'{"veryVeryVeryVeryVeryVeryVeryLongEntityName2":null}' as "_expands_",(SELECT ID as "ID",veryVeryVeryVeryVeryVeryVeryLongEntityName2_ID as "veryVeryVeryVeryVeryVeryVeryLongEntityName2_ID" FROM JSON_TABLE('{}', '$' COLUMNS("'$$FaKeDuMmYCoLuMn$$'" FOR ORDINALITY)) FOR JSON ('format'='no', 'omitnull'='no', 'arraywrap'='no') RETURNS NVARCHAR(2147483647)) as "_json_" FROM veryVeryVeryVeryVeryVeryVeryLongEntityName1) UNION ALL (SELECT _path_ as "_path_",'{}' as "_blobs_",'{"veryVeryVeryVeryVeryVeryVeryLongEntityName3":null}' as "_expands_",(SELECT ID as "ID",veryVeryVeryVeryVeryVeryVeryLongEntityName3_ID as "veryVeryVeryVeryVeryVeryVeryLongEntityName3_ID" FROM JSON_TABLE('{}', '$' COLUMNS("'$$FaKeDuMmYCoLuMn$$'" FOR ORDINALITY)) FOR JSON ('format'='no', 'omitnull'='no', 'arraywrap'='no') RETURNS NVARCHAR(2147483647)) as "_json_" FROM veryVeryVeryVeryVeryVeryVeryLongEntityName1_veryVeryVeryVeryVeryVeryVeryLongEntityName2) UNION ALL (SELECT _path_ as "_path_",'{}' as "_blobs_",'{}' as "_expands_",(SELECT ID as "ID" FROM JSON_TABLE('{}', '$' COLUMNS("'$$FaKeDuMmYCoLuMn$$'" FOR ORDINALITY)) FOR JSON ('format'='no', 'omitnull'='no', 'arraywrap'='no') RETURNS NVARCHAR(2147483647)) as "_json_" FROM veryVeryVeryVeryVeryVeryVeryLongEntityName1_veryVeryVeryVeryVeryVeryVeryLongEntityName2_veryVeryVeryVeryVeryVeryVeryLongEntityName3) ORDER BY "_path_" ASC`
}
[error] - 500 > {
  code: '286',
  message: 'identifier is too long: the identifer "VERYVERYVERYVERYVERYVERYVERYLONGENTITYNAME1_VERYVERYVERYVERYVERYVERYVERYLONGENTITYNAME2_VERYVERYVERYVERYVERYVERYVERYLONGENTITYNAME3" is too long. Maximum length is 127: line 1 col 1571 (at pos 1570)'
}