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.
