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

[Matthew Brock (JIRA)]

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