Uploaded image for project: 'Hibernate ORM'
  1. HHH-7019

SQLServer2005Dialect, SQLServer2008Dialect issues with subqueries

    Details

    • Type: Bug
    • Status: Closed
    • Priority: Minor
    • Resolution: Fixed
    • Affects versions: 3.6.9
    • Fix versions: 4.1.4
    • Components: hibernate-core
    • Environment:
      Microsoft SQLServer 2005, Microsoft SQLServer 2008
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Requires Release Note:
      Affirmative
    • Last commented by a user?:
      true
    • Sprint:

      Description

      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.

        Attachments

          Issue links

            Activity

              People

              • Votes:
                4 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Time Tracking

                  Estimated:
                  Original Estimate - 5m
                  5m
                  Remaining:
                  Remaining Estimate - 5m
                  5m
                  Logged:
                  Time Spent - Not Specified
                  Not Specified