[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (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 Labels: core, dialect, sqlserver Attachments: SQLServer2005Dialect-2.java, SQLServer2005Dialect.java Original Estimate: 5m Remaining Estimate: 5m 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.