Query gets "invalid order by" when using hsql db while Mysql does not ( result must be one column only)

Question

While this query is running fine in a MySQL database:

SELECT DISTINCT user_id  
FROM table_name 
WHERE user_name IN (:userNameList) 
  AND user_type IN ('client','vip')   
ORDER BY user_login_time DESC
LIMIT 10  OFFSET 0

When I'm trying to run the same query via HSQL, I'm getting this error:

nested exception is java.sql.SQLSyntaxErrorException: invalid ORDER BY expression in statement [SELECT DISTINCT user_id FROM table_name WHERE user_name IN (:userNameList) AND user_type IN ('client','vip') ORDER BY user_login_time desc LIMIT 10 OFFSET 0]

Answer 1

The solution for this problem requires me to change the query: in HSQL we must select the order by column. without it, we are getting this error.

SELECT DISTINCT user_id, user_login_time  
FROM table_name 
WHERE user_name IN (:userNameList) 
  AND user_type IN ('client','vip')   
ORDER BY user_login_time DESC
LIMIT 10  OFFSET 0

But this solution returns two columns instead of one column.

By using JDBCTAmplate, I found a way to return only one column

String query = "SELECT DISTINCT user_id , user_login_time  FROM table_name WHERE user_name IN (:userNameList) AND user_type IN ('client','vip') ORDER BY user_login_time DESC LIMIT 10  OFFSET 0";
    
List<String> userIds = new ArrayList<String>();
List<String> userIdList = List.of("12345667", "123443235");
Map<String, Object> sqlParameterSource = new HashMap<>();
sqlParameterSource.put("userNameList", userIdList);
                
userIds = namedParamJDBCTemplate.query(query, new MapSqlParameterSource(sqlParameterSource), new ResultSetExtractor<List>() {
                 
                    @Override
                    public List extractData(ResultSet resultSet) throws SQLException, DataAccessException {
                        List<String> listRes = new ArrayList<String>();
                        while (resultSet.next()) {
                            listRes.add(resultSet.getString(USER_ID));
                        }
                        return listRes;
                    }
                });

This solution allows me to run the query via HSQL and still getting only one column as a result.