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

Limit Handler for SQL server doesn't support queries with alias( without 'as' keyboard ) in select clause.

Description

In Hibernate version 5 and above, it is observed that if the select clause contains alias for the columns with just a space without using 'as' keyword and when pagination is used, SQLServer limit handler transforms the query to "WITH query AS ( Select inner....) ". During this process it uses a regex pattern to identify the columns with alias.
The pattern used is ALIAS_PATTERN = Pattern.compile( "(?i)\\sas
s(.)+$" ).
This enforces all the columns with alias to have 'as' keyword in the query.
So when the query "select column1 c1, column2 c2 from Table1" is executed in SQL server with the limit clause, SQLServer2005LimitHandler transforms the query as " WITH query AS (..select column1 c1 as page0_, column2 c2 as page1_ from Table1.. ).
As it is observed since query didn't contain 'as' in the select clause columns, while executing the query it throws Error, saying Incorrect syntax near 'as' keyword.

Possible Fix: ALIAS_PATTERN has to be changed to support alias with or without 'as' keyword.
Files to be referenced :
1. org.hibernate.loader.Loader#executeQueryStatement
2. org.hibernate.dialect.pagination.SQLServer2005LimitHandler

Environment

None

Status

Assignee

Chris Cranford

Reporter

sachink

Fix versions

Priority

Critical