Fix order by clause missing "order by"

[el-hackerino]

The generated ORDER BY clause is incorrect, leading to syntax errors in the resulting query. The ELSE branch in lines 64-55 did it correctly (but I refactored the code to combine the code).

I'm not set up to add/run tests for this, but I think this change should be looked at ASAP since the bug breaks a lot of queries.

Check List:

• Unit tests: NO
• Documentation: NO

[filiphr]

@el-hackerino can you please provide an example query where this is causing issues? This code has been like this since 2017 and we have not received any issues about it.

Perhaps you are using it incorrectly. How are you passing the orderBy parameter?

[el-hackerino]

We're using a NativeQuery and passing the orderBy parameter. In Flowable 6.5, everything worked when we passed something like "CREATETIME ASC", resulting in a query starting with

SELECT SUB.* FROM ( select RES.* , row_number() over (order by RES.CREATE_TIME_ ASC ) rnk

Since Flowable 7.0, the result is

SELECT RES.* FROM ( select RES.*, row_number() over (RES.CREATE_TIME_ ASC ) as rnk 

because of this commit changing

order by ${orderByColumns}

into

${orderByForWindow}

in db2.properties.

The new orderByWindow value is generated either with order by included when there is no value and the default ID sorting is used or WITHOUT order by if a value is passed (see my suggested changes). The latter case produces a syntax error.

[filiphr]

@el-hackerino can you please share the java code you are using for this?

[filiphr]

@el-hackerino I've added a test case for this and I'll merge it once the tests pass.