Affects Version/s: 6.2.10 EE GA1, 6.2.X EE, 7.0.0 DXP SP1, Master
The customer is using the following method to search users based on keywords and order by "status" column. The SQL query behind the API is not working against the DB2 when the order by "status" is passed.
A test portlet has been provided and this issue has been reproduced using it.
Steps to Reproduce:
1. Configure Liferay to use DB2 10.1
2. Set dialect in portal-ext: hibernate.dialect=org.hibernate.dialect.DB2400Dialect
3. Start Liferay
4. Deploy attached NewPortlet
5. Add 'New' portlet to a page
Query executes successfully.
Query does not execute successfully with "org.hibernate.exception.SQLGrammarException: could not execute query" in logs. (Full stacktrace is attached as db2_order_by_error.txt)
For master / ee-7.0.x, this portal property must be set, as the default behavior is to do an index search:
The error code that is reported says: