org.h2.jdbc.JdbcSQLException: Order by expression ... must be in the result list

[Chris Rompot]

I receive the error org.h2.jdbc.JdbcSQLException: Order by expression "SCOPETREED1_.ANCESTOR_DEPTH" must be in the result list in this case; SQL statement:

select distinct optionlist0_.option_list_id as option1_29_, optionlist0_.change_date as change2_29_, optionlist0_.change_user as change3_29_, optionlist0_.code as code29_, optionlist0_.name as name29_, optionlist0_.scope_id as scope8_29_, optionlist0_.shared as shared29_, optionlist0_.sql_text as sql7_29_ from option_list optionlist0_ cross join scope_tree scopetreed1_ where (optionlist0_.scope_id in (select scopetreed2_.ancestor_scope_id from scope_tree scopetreed2_, scope scopedo3_ where scopetreed2_.ancestor_scope_id=scopedo3_.scope_id and scopetreed2_.scope_id=? and (scopetreed2_.ancestor_scope_id is not null))) and optionlist0_.shared=1 and optionlist0_.code=? and optionlist0_.scope_id=scopetreed1_.scope_id order by scopetreed1_.ancestor_depth desc limit ? [90068-176] when executing the HQL-to-SQL query below in h2.  It works fine against MySQL 5.6.27, and the order by should not have to be a result column in the select list.  Removing the DISTINCT had no effect.

SELECT DISTINCT optionlist0_.option_list_id AS option1_29_,

   optionlist0_.change_date AS change2_29_,

   optionlist0_.change_user AS change3_29_,

   optionlist0_.code AS code29_,

   optionlist0_.name AS name29_,

   optionlist0_.scope_id AS scope8_29_,

   optionlist0_.shared AS shared29_,

   optionlist0_.sql_text AS sql7_29_

FROM option_list optionlist0_ CROSS JOIN scope_tree scopetreed1_

WHERE (optionlist0_.scope_id IN

  (SELECT scopetreed2_.ancestor_scope_id

   FROM scope_tree scopetreed2_, scope scopedo3_

   WHERE scopetreed2_.ancestor_scope_id = scopedo3_.scope_id

   AND scopetreed2_.scope_id = 80

   AND (scopetreed2_.ancestor_scope_id IS NOT NULL)))

   AND optionlist0_.shared = 1

   AND optionlist0_.code = 'statesWithNoStatewideIdentifier'

   AND optionlist0_.scope_id = scopetreed1_.scope_id

ORDER BY scopetreed1_.ancestor_depth DESC

LIMIT 1

[Chris Rompot]

Further research and experimentation with other databases has shown that the error thrown is valid and no H2 bug exists.  Besides H2, Oracle, SQL Server, and HSQLDB all require that ORDER BY items be in the SELECT DISTINCT list.  MySQL does not, and its laxity in SQL syntax checking seems to be the problem rather H2's.