Variable orderByHasOutputColumnRef is set to late, if ref has length 2, the return skips setting it to true

[oklemenz2]

https://github.com/cap-js/cds-dbs/blob/f39097c0e991e7726960c6aa3bab6a81efe2669f/hana/lib/HANAService.js#L392C14-L392C15

[BobdenOs]

@oklemenz2 does this query throw a SQL error ?

As this is the intended behavior when the order by clause references a column from the source and not a column from the output columns it is not required to have an additional wrapper as the alias ref[0] points to the source and the ref[1] points to the column exposed by the source. So the quoting of the output columns shouldn't prevent the aliased order by ref to be resolved.

[oklemenz2]

Exactly this SQL is not valid and throws an error:

[HY000][260] SAP DBTech JDBC: [260] (at 6047): invalid column name: _TASKEXECUTION. createdAt: line 63 col 10 (at pos 6047)
  

Why wrapping also this case for orderBy it works. We patched our version to continue...

Order by should be

"createdAt"

instead of

ORDER BY _taskExecution.createdAt ASC

We now always apply wrapping for orderBy.

[BobdenOs]

I tried to reproduce the issue in one of our tests, but the SQL queries are running without errors and are producing the correct behavior.

  test('order by selected result column with join aliases', async () => {
    const { Authors } = cds.entities('sap.capire.bookshop')
    const res = await SELECT
      .columns`ID,createdAt,books.price as price,books.genre.name as genre`
      .from`${Authors} as Authors`
      .where`Authors.ID in ${{ list: [{ val: 170 }, { val: 150 }] }} and books.ID = ${251} or Authors.ID in ${{ list: [{ val: 170 }, { val: 150 }] }} and books.ID = ${252}`
      .orderBy`Authors.createdAt desc`
  })

The produced working simple sql query

SELECT
  Authors.ID as "ID",
  to_char(Authors.createdAt,'YYYY-MM-DD"T"HH24:MI:SS.FF3"Z"') as "createdAt",
  TO_NVARCHAR(books.price) as "price",
  genre.name as "genre"
FROM
  sap_capire_bookshop_Authors as Authors
  left JOIN sap_capire_bookshop_Books as books ON books.author_ID = Authors.ID
  left JOIN sap_capire_bookshop_Genres as genre ON genre.ID = books.genre_ID
WHERE
  Authors.ID in (SELECT * FROM JSON_TABLE(?, '$' COLUMNS("val" DOUBLE PATH '$.val')))
  and books.ID = ?
  or Authors.ID in (SELECT * FROM JSON_TABLE(?, '$' COLUMNS("val" DOUBLE PATH '$.val')))
  and books.ID = ?
ORDER BY
  Authors.createdAt DESC