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.

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.

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 Assignee: Lukasz Antoniak Priority: Minor Labels: core, dialect, sqlserver Fix For: 4.1.4 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.