[hibernate-issues] [Hibernate-JIRA] Issue Comment Edited: (HHH-7019) SQLServer2005Dialect, SQLServer2008Dialect issues with subqueries

[Matthew Brock (JIRA)]

    [ https://hibernate.onjira.com/browse/HHH-7019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45357#comment-45357 ] 

Matthew Brock edited comment on HHH-7019 at 2/2/12 9:29 PM:
------------------------------------------------------------

+added SQLServer2005Dialect-2.java
Small bugfix

      was (Author: brockmatt):
    Small bugfix.
  
> 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.

--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira