[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-7019) SQLServer2005Dialect, SQLServer2008Dialect issues with subqueries
Matthew Brock (JIRA)
noreply at atlassian.com
Thu Feb 2 21:11:15 EST 2012
[ https://hibernate.onjira.com/browse/HHH-7019?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45356#comment-45356 ]
Matthew Brock commented on HHH-7019:
------------------------------------
I'd just like to add, the attached file is the fix for the 3.x branch, but I wouldn't be surprised if the 4.x branch / trunk doesn't have the same issues (not verified). I would have attached an SVN diff, except I'm unfamiliar with how to do a SVN checkout from the Hibernate repo since they've moved over to GIT.
Additionally, this fixes a few issues that were brought up after tickets had been closed (HHH-2655, for example). I'll go through the other existing SQLServerDialect tickets that I would guess are caused by the same bug and link them as I see them.
> 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.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
More information about the hibernate-issues
mailing list