[hibernate-dev] SQLServer getLimitString() refactoring

[Demetz, Guenther]

Hi Lukasz,

>>Query "select id, description descr, (select max(id) from Product2) maximum from Product2" fails for me (even without paging)

This is because HQL apparently does not support the suppression of the "AS" keyword for aliases, so this query works only by using the Native-SQL api.


>>I think that my modifications are more or less ready and you can view them here: 
>>https://github.com/lukasz-antoniak/hibernate-core/compare/SQLServerLimitString

I noticed that in your branch 

s.createQuery( "from Product2 where description like :pattern order by id" ).setString( "pattern", "Kit%" ).setFirstResult( 0 ).setMaxResults( 2 ).list();

is producing following query (formatted output obtained with p6spy):

WITH query AS (SELECT
        inner_query.*,
        ROW_NUMBER() OVER (
    ORDER BY
        CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
    FROM
        ( select
            TOP(3) product2x0_.id as id0_,
            product2x0_.description as descript2_0_ 
        from
            Product2 product2x0_ 
        where
            product2x0_.description like 'Kit%' 
        order by
            product2x0_.id ) inner_query ) SELECT
            id0_,
            descript2_0_ 
        FROM
            query 
        WHERE
            __hibernate_row_nr__ >= 1 
            AND __hibernate_row_nr__ < 3  

The result is correct, but anyway I have 2 little objections:

- I expected to find TOP(2) instead of TOP(3). 
    Although due the  __hibernate_row_nr__ restriction the final result is correct, I find this a little disconcerting..

- I hoped that due to the changes in Dialect.class you were already able to implement also HHH-7370
    In front of such complex transformation as it has become now, I believe that HHH-7370 gains importance, 
    what do you think about?
    
        select
            TOP(2) product2x0_.id as id0_,
            product2x0_.description as descript2_0_ 
        from
            Product2 product2x0_ 
        where
            product2x0_.description like 'Kit%' 
        order by
            product2x0_.id

best regards
G.D.