[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias

[Piotr Findeisen (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=44670#comment-44670 ] 

Piotr Findeisen edited comment on HHH-6914 at 12/21/11 9:52 AM:
----------------------------------------------------------------

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.
	 */
	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 = "hiborder_" + (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}

      was (Author: piofinsy):
    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