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.
On Tuesday, March 8, 2016 at 10:33:18 PM UTC-6, Chris Rompot wrote: > > 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 > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
