[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
Piotr Findeisen (JIRA)
noreply at atlassian.com
Wed Dec 21 10:52:19 EST 2011
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44670#comment-44670 ]
Piotr Findeisen commented on HHH-6914:
--------------------------------------
As a fix a propose the following addition in {{SQLServer2005Dialect.getLimitString(String querySqlString, boolean hasOffset)}} between those two statements:
{code}
// HHH-5715 bug fix
replaceDistinctWithGroupBy(sb);
insertRowNumberFunction(sb, orderby);
{code}
so that it looks like:
{code}
// HHH-5715 bug fix
replaceDistinctWithGroupBy(sb);
if (orderby.contains("?") || orderby.contains("col_")) {
/*
* If ORDER BY clause references SELECT clause aliases, then it cannot be directly translated into
* ROW_NUMBER() OVER (...) expression -- first, the original query must by pushed into subquery, so that the
* aliases become first-class identifiers. If we make query a subquery, then ORDER BY clauses referencing
* table column names directly (e.g. TABLE0.SOMECOLUMN), become invalid and must be fixed. /PIOFIN 20111220
*/
Pattern qualifiedName = Pattern.compile("\\b\\w+\\.\\w+\\b");
Matcher orderByMatcher = qualifiedName.matcher(orderby);
StringBuffer newOredrBy = new StringBuffer();
int i = 0;
boolean foundOrdeingOverQualifiedName = false;
while (orderByMatcher.find()) {
foundOrdeingOverQualifiedName = true;
String expression = orderByMatcher.group(0);
Pattern alias = Pattern.compile("\\b" + Pattern.quote(expression) + "\\b\\s+(?i:as)\\s+(\\w+)");
Matcher aliasMatcher = alias.matcher(sb);
if (aliasMatcher.find()) {
orderByMatcher.appendReplacement(newOredrBy, Matcher.quoteReplacement(aliasMatcher.group(1)));
} else {
// Add new alias to the query and use it.
String newAlias = "syncorder_" + (i++);
orderByMatcher.appendReplacement(newOredrBy, newAlias);
final int selectEndIndex = sb.indexOf("select") + "select".length();
sb.insert(selectEndIndex, String.format(" %s AS %s, ", expression, newAlias));
}
}
if (foundOrdeingOverQualifiedName) {
orderByMatcher.appendTail(newOredrBy);
orderby = newOredrBy.toString();
}
sb.insert(0, "select temp_.* from (");
sb.append(") temp_");
}
insertRowNumberFunction(sb, orderby);
{code}
> SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
> ---------------------------------------------------------------------------------
>
> Key: HHH-6914
> URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914
> Project: Hibernate Core
> Issue Type: Bug
> Components: core
> Affects Versions: 3.6.7
> Environment: MS SQL Server 2008
> Reporter: Piotr Findeisen
> Labels: sqlserver
>
> If the HQL like:
> {code}
> SELECT u.id AS id, u.login AS login FROM User u
> {code}
> is executed with limit and offset, then {{SQLServer2008Dialect}} produces SQL like
> {code}
> WITH query AS (select ROW_NUMBER() OVER (order by col_1) as __hibernate_row_nr__, user.id as col_0, user.login as col_1 from users user ) SELECT * FROM query WHERE __hibernate_row_nr__ BETWEEN ? AND ?
> {code}
> This fails, as one select clause expression {{(select ROW_NUMBER() OVER (order by col_1) as __hibernate_row_nr__}} refers to another useing {{col_1}} alias.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list