@cap-js/hana generates broken sql when using now() in combination with between

[dimrat]

Description of erroneous behaviour

We are using @cap-js/hana in combination with @sap/hana-client. The generated sql seems to be broken if use now() in combination with between

Here's a simplified version of our cds model

namespace sap.capire.bookshop;

entity Person {
  key ID   : Integer;
      name : String
}

entity Role {
  key ID   : Integer;
      name : String
}

entity PersonAccessControl {
  key person                : Association to Person;
  key personRole            : Association to Role;
      fromValidityTimestamp : Timestamp;
      toValidityTimestamp   : Timestamp;
}

We have a query defined as follows:

      const accessControls = await SELECT.from`sap.capire.PersonAccessControl`
        .where`
          person_ID=123
          AND ( now() between fromValidityTimestamp and toValidityTimestamp) 
       `.columns`
          personRole{
            ID
          }
      `

When running the query we got the following error. As you can see, the = true) at the end of the statement is wrong.

[sql] - BEGIN
[sql-json] - SELECT personRole FROM sap_capire_PersonAccessControl WHERE person_ID = ? and (session_context('$now') between fromValidityTimestamp and toValidityTimestamp  = true) 123
[sql] - ROLLBACK
[cds] - Error: sql syntax error: incorrect syntax near "=": line 1 col 159 (at pos 159)
    at Socket.<anonymous> (/home/dr/dra-test/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: 257,
  sqlState: 'HY000',
  query: "SELECT personRole FROM sap_capire_PersonAccessControl WHERE person_ID = ? and (session_context('$now') between fromValidityTimestamp and toValidityTimestamp  = true)"
}
[error] - 500 > {
  code: '257',
  message: 'sql syntax error: incorrect syntax near "=": line 1 col 159 (at pos 159)'
}

Detailed steps to reproduce

1. cds init test --add samples,hana
2. npm install
3. npm add @sap/hana-client
4. add the entities from above to schema.cds
5. cds deploy -2 hana
6. Place the query into an appropriate handler, for simplicity i used a this.before("*", "*")
7. DEBUG=all cds watch --profile hybrid
8. Run any odata request, e.g. http://localhost:4004/odata/v4/catalog/Books

Details about your project

| @cap-js/asyncapi | 1.0.2 |
| @cap-js/cds-types | 0.6.5 |
| @cap-js/hana | 1.2.0 |
| @cap-js/openapi | 1.0.5 |
| @cap-js/sqlite | 1.7.3 |
| @sap/cds | 8.2.0 |
| @sap/cds-compiler | 5.2.0 |
| @sap/cds-dk (global) | 8.1.2 |
| @sap/cds-fiori | 1.2.7 |
| @sap/cds-foss | 5.0.1 |
| @sap/cds-mtxs | 2.0.5 |
| @sap/eslint-plugin-cds | 3.0.4 |
| Node.js | v20.17.0 |
| home | /home/dr/dra-test/node_modules/@sap/cds |

[patricebender]

@BobdenOs do you have some insights here? I find it suspicious that no alias is added to the query. It does not look like it went through cqn4sql. I can't reproduce it in my bookshop:

> query  = SELECT.from(Books).where`ID != 42 AND ( now() between author.dateOfBirth  and author.dateOfDeath)`.columns`author { ID }`
Query {
  SELECT: {
    from: { ref: [ 'sap.capire.bookshop.Books' ] },
    where: [
      { ref: [ 'ID' ] },
      '!=',
      { val: 42 },
      'and',
      {
        xpr: [
          { func: 'now', args: [] },
          'between',
          { ref: [ 'author', 'dateOfBirth' ] },
          'and',
          { ref: [ 'author', 'dateOfDeath' ] }
        ]
      }
    ],
    columns: [
      {
        ref: [ 'author' ],
        expand: [ { ref: [ 'ID' ] } ]
      }
    ]
  }
}
> query.toSQL().sql
SELECT json_insert('{}', '$."author"', author->'$') as _json_
FROM (
        SELECT (
                SELECT json_insert('{}', '$."ID"', ID) as _json_
                FROM (
                        SELECT author2.ID
                        FROM sap_capire_bookshop_Authors as author2
                        WHERE Books.author_ID = author2.ID
                        LIMIT ?
                    )
            ) as author
        FROM sap_capire_bookshop_Books as Books
            left JOIN sap_capire_bookshop_Authors as author ON author.ID = Books.author_ID
        WHERE Books.ID <> ?
            and (
                session_context('$now') between author.dateOfBirth and author.dateOfDeath
            )
    )