[hibernate-issues] [Hibernate-JIRA] Created: (HHH-7019) SQLServer2005Dialect, SQLServer2008Dialect issues with subqueries
SQLServer2005Dialect, SQLServer2008Dialect issues with subqueries
-----------------------------------------------------------------
Key: HHH-7019
URL: https://hibernate.onjira.com/browse/HHH-7019
Project: Hibernate ORM
Issue Type: Bug
Components: core
Affects Versions: 3.6.9
Environment: Microsoft SQLServer 2005, Microsoft SQLServer 2008
Reporter: Matthew Brock
Priority: Minor
Attachments: SQLServer2005Dialect.java
There are a few bugs with the way the SQLServer2005Dialect is generating the pseudo-limit
wrapper (ROW_NUMBER() OVER).
---
#1: Indeterminate return results when strings are returned from subqueries
line 116: StringBuilder sb = new StringBuilder(querySqlString.trim().toLowerCase());
Take the following example:
@Formula("(select case when name = 'Smith' then 'Neo' else name
end)")
public String getName() { ... }
toLowerCase() will lose any capitalization in subselects and break the CASE statement.
FIX: Move toLowerCase() test down the chain, don't modify original query.
---
#2: GenericJDBCException whenever subqueries are part of a SELECT
line 118: int orderByIndex = sb.indexOf("order by");
line 147: int distinctIndex = sql.indexOf(DISTINCT);
line 163: sql.substring(sql.indexOf(SELECT) + SELECT.length(), sql.indexOf(FROM));
This issue stems from using indexOf() to search the original SQL for specific keywords
that could possibly exist in subqueries (@Formulas tend to be the biggest offenders).
Example:
@Formula("(select distinct(a.zip) from address a where a.person_id = id")
public String getZip() { ... }
The DISTINCT and FROM keywords will break the query generation.
FIX: Since all @Formulas are wrapped in parenthesis when they are aliased, I simply count
the number of open parenthesis when doing an indexOf() search on SQL statements and ignore
any results if the number of open parenthesis doesn't equal 0.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira