[Hibernate-JIRA] Created: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
4:48 p.m.
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
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
4:52 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914?page=c...
]
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
4:54 p.m.
New subject: [Hibernate-JIRA] Issue Comment Edited: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914?page=c...
]
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
10:11 p.m.
New subject: [Hibernate-JIRA] Commented: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914?page=c...
]
Ryan Connolly commented on HHH-6914:
------------------------------------
I'm seeing this with the hibernate4 version that comes with JBoss AS 7.0.2 as well.
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 ORM
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
12:42 a.m.
New subject: [Hibernate-JIRA] Commented: (HHH-6914) SQL Server: Query with limit fails for HQL with ORDER BY over select clause alias
[
http://opensource.atlassian.com/projects/hibernate/browse/HHH-6914?page=c...
]
Gail Badner commented on HHH-6914:
----------------------------------
Please create a pull request (preferable) or attach a patch with your fix and an update to
org.hibernate.test.pagination.PaginationTest that reproduces your issue.
Thanks,
Gail
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 ORM
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
4483
days inactive
4517
days old
hibernate-issues@lists.jboss.org
4 comments
3 participants
participants (3)
-
Gail Badner (JIRA) -
Piotr Findeisen (JIRA)
-
Ryan Connolly (JIRA)