[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-6950) Bogus string replacement in SQLServer2005Dialect.insertRowNumberFunction()

[Roberto Italia (JIRA)]

    [ http://opensource.atlassian.com/projects/hibernate/browse/HHH-6950?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=45080#comment-45080 ] 

Roberto Italia commented on HHH-6950:
-------------------------------------

The fix stated above doesn't work in all cases. Consider the following query with a sub-select in the column-clause:

select persistent0_.id as col_0_0_,
(select max(persistent1_.acceptancedate)
from av_advisoryvariant persistent1_
where persistent1_.clientid=persistent0_.id) as col_1_0_
from c_customer persistent0_
where persistent0_.type='v'

Here again, the output of SQLServer2005Dialect.insertRowNumberFunction() is wrong:

select persistent0_.id as col_0_0_,
(select max(persistent1_.acceptancedate),
ROW_NUMBER() OVER (order by persistent0_.id) as __hibernate_row_nr__
from av_advisoryvariant persistent1_
where persistent1_.clientid=persistent0_.id) as col_1_0_
from c_customer persistent0_
where persistent0_.type='v'

The correct output should be:

select persistent0_.id as col_0_0_,
(select max(persistent1_.acceptancedate)
from av_advisoryvariant persistent1_
where persistent1_.clientid=persistent0_.id) as col_1_0_,
ROW_NUMBER() OVER (order by persistent0_.id) as __hibernate_row_nr__
from c_customer persistent0_
where persistent0_.type='v'

 

> Bogus string replacement in SQLServer2005Dialect.insertRowNumberFunction()
> --------------------------------------------------------------------------
>
>                 Key: HHH-6950
>                 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6950
>             Project: Hibernate Core
>          Issue Type: Bug
>          Components: core, query-sql
>    Affects Versions: 4.0.0.Final
>         Environment: Hibernate 4.0.0, SQLServer 2008R2
>            Reporter: Roberto Italia
>              Labels: dialect, sqlserver
>
> The fix for HHH-6849 introduced a problem with column-names containing the character sequence 'from'.
> For example, assume the string builder contains the following query before the call to SQLServer2005Dialect.insertRowNumberFunction() (formatted for better reading):
> select persistent0_.rid as rid1688_,
>        persistent0_.deviationfromtarget as deviati16_1688_,
>        persistent0_.sortindex as sortindex1688_
> from m_evalstate persistent0_
> where persistent0_.customerid=?
> After the call to SQLServer2005Dialect.insertRowNumberFunction(), the string builder contains:
> select persistent0_.rid as rid1688_,
>        persistent0_.deviatio,
>        ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__nfromtarget as deviati16_1688_,
>        persistent0_.sortindex as sortindex1688_
> from m_evalstate persistent0_
> where persistent0_.customerid=?
> But the intention of the fix was:
> select persistent0_.rid as rid1688_,
>        persistent0_.deviationfromtarget as deviati16_1688_,
>        persistent0_.sortindex as sortindex1688_,
>        ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__
> from m_evalstate persistent0_
> where persistent0_.customerid=?
> A fix would be the following code instead of the current:
> final String myFrom = " " + FROM + " ";
> int selectEndIndex = sql.indexOf( myFrom );
> sql.insert( selectEndIndex, ", ROW_NUMBER() OVER (" + orderby + ") as __hibernate_row_nr__" );

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