We're updating the issue view to help you get more done. 

SQLServer2005Dialect, SQLServer2008Dialect issues with subqueries

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.

Environment

Microsoft SQLServer 2005, Microsoft SQLServer 2008

Status

Assignee

Lukasz Antoniak

Reporter

Matthew Brock

Fix versions

backPortable

None

Suitable for new contributors

None

Requires Release Note

Affirmative

Pull Request

None

backportDecision

None

Components

Affects versions

3.6.9

Priority

Minor