[ https://hibernate.onjira.com/browse/HHH-7043?page=com.atlassian.jira.plug... ] Steve Ebersole updated HHH-7043: -------------------------------- Pull Requests: https://github.com/hibernate/hibernate-orm/pull/272 (was: https://github.com/hibernate/hibernate-orm/pull/270) -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7043?page=com.atlassian.jira.plug... ] Steve Ebersole reopened HHH-7043: --------------------------------- -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7043?page=com.atlassian.jira.plug... ] Steve Ebersole updated HHH-7043: -------------------------------- Fix Version/s: (was: 4.1.0) -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira

[ https://hibernate.onjira.com/browse/HHH-7043?page=com.atlassian.jira.plug... ] William Rose edited comment on HHH-7043 at 5/6/12 10:33 PM: ------------------------------------------------------------ An alternative query template might help. The current query template is: {code:sql} WITH query AS ( select ROW_NUMBER() OVER(<<order by clause>>) AS __hibernate_row_nr__ , <<query columns>> from <<table spec>> <<rest of query>> ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? {code} Because the ROW_NUMBER() function is at the same level as the original query expressions, it cannot refer to their aliased names. If another level is introduced, it can: {code:sql} WITH q1 AS ( select <<query columns>> from <<table spec>> <<rest of query>> ), q2 AS ( SELECT ROW_NUMBER() OVER(<<order by clause>>) AS __hibernate_row_nr__ , * FROM q1 ) SELECT * FROM q2 WHERE __hibernate_row_nr__ BETWEEN ? AND ? {code} The drawback to this query template is that if people order by column names that are not aliased and are qualified (e.g. a.col1 instead of col1), then it won't work for them. I ran into this issue when trying to use fetch limits on a query with a group by: {code:sql} SELECT widget_code , MAX(purchase_date) AS last_purchase_date FROM widget_purchases GROUP BY widget_code ORDER BY last_purchase_date DESC {code} If this is wrapped in the default query template it will not work: in the alternative above it would work. was (Author: wrose): I think the issue here is slightly different, in that the query template used for pagination could be better constructed. The current query template is: {code:sql} WITH query AS ( select ROW_NUMBER() OVER(<<order by clause>>) AS __hibernate_row_nr__ , <<query columns>> from <<table spec>> <<rest of query>> ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ? {code} Because the ROW_NUMBER() function is at the same level as the original query expressions, it cannot refer to their aliased names. If another level is introduced, it can: {code:sql} WITH q1 AS ( select <<query columns>> from <<table spec>> <<rest of query>> ), q2 AS ( SELECT ROW_NUMBER() OVER(<<order by clause>>) AS __hibernate_row_nr__ , * FROM q1 ) SELECT * FROM q2 WHERE __hibernate_row_nr__ BETWEEN ? AND ? {code} -- This message is automatically generated by JIRA. For more information on JIRA, see: http://www.atlassian.com/software/jira