SQLServer2005LimitHandler creates query with incorrect syntax
it happens in method *org.hibernate.dialect.pagination.SQLServer2005LimitHandler#fillAliasInSelectClause*
simple test case:
{code:java}
@Test
public void processSQLTest() {
String sql = "select this_.MfkID as MfkID1_30_3_, this_.MfkCascade as MfkCasca2_30_3_, this_.uuid as uuid3_30_3_, this_.MatToID as MatToID4_30_3_, this_.MfkSoft as MfkSoft5_30_3_, this_.MatFromID as MatFromI6_30_3_, this_.MfkDescription as MfkDescr7_30_3_, fromattrib1_.MatID as MatID1_25_0_, fromattrib1_.MtbID as MtbID2_25_0_, fromattrib1_.MatPrimaryKey as MatPrima3_25_0_, fromattrib1_.uuid as uuid4_25_0_, fromattrib1_.MatSize as MatSize5_25_0_, fromattrib1_.MlokDataTypeId as MlokData6_25_0_, fromattrib1_.MatSequenceNr as MatSeque7_25_0_, fromattrib1_.MatLogicalName as MatLogic8_25_0_, fromattrib1_.MatPhysicalName as MatPhysi9_25_0_, fromattrib1_.MatCanBeNull as MatCanB10_25_0_, fromattrib1_.MatReadonly as MatRead11_25_0_, fromattrib1_.jdbc_type as jdbc_ty12_25_0_, fromattrib1_.MatType as MatType13_25_0_, fromattrib1_.MatDescription as MatDesc14_25_0_, fromattrib1_.MatPrecision as MatPrec15_25_0_, table2_.MtbID as MtbID1_34_1_, table2_.uuid as uuid2_34_1_, table2_.MmtID as MmtID3_34_1_, table2_.MtbOrder as MtbOrder4_34_1_, table2_.MtbLogicalName as MtbLogic5_34_1_, table2_.MtbName as MtbName6_34_1_, table2_.MtbCreationDate as MtbCreat7_34_1_, table2_.MtbLastModifiedDate as MtbLastM8_34_1_, table2_.MtbDescription as MtbDescr9_34_1_, table2_.MdbID as MdbID10_34_1_, tabletype3_.MmtID as MmtID1_35_2_, tabletype3_.MmtName as MmtName2_35_2_, tabletype3_.MmtType as MmtType3_35_2_ from M_ForeignKey this_ inner join M_Attribute fromattrib1_ on this_.MatFromID=fromattrib1_.MatID inner join M_Table table2_ on fromattrib1_.MtbID=table2_.MtbID inner join M_TableType tabletype3_ on table2_.MmtID=tabletype3_.MmtID where tabletype3_.MmtType<>? order by this_.MfkID asc";
RowSelection selection = new RowSelection();
selection.setFirstRow(51);
selection.setMaxRows(51);
SQLServer2005LimitHandler sqlServer2005LimitHandler = new SQLServer2005LimitHandler();
String generatedQuery = sqlServer2005LimitHandler.processSql(sql, selection);
String expectedQuery = "WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( select TOP(?) this_.MfkID as MfkID1_30_3_, this_.MfkCascade as MfkCasca2_30_3_, this_.uuid as uuid3_30_3_, this_.MatToID as MatToID4_30_3_, this_.MfkSoft as MfkSoft5_30_3_, this_.MatFromID as MatFromI6_30_3_, this_.MfkDescription as MfkDescr7_30_3_, fromattrib1_.MatID as MatID1_25_0_, fromattrib1_.MtbID as MtbID2_25_0_, fromattrib1_.MatPrimaryKey as MatPrima3_25_0_, fromattrib1_.uuid as uuid4_25_0_, fromattrib1_.MatSize as MatSize5_25_0_, fromattrib1_.MlokDataTypeId as MlokData6_25_0_, fromattrib1_.MatSequenceNr as MatSeque7_25_0_, fromattrib1_.MatLogicalName as MatLogic8_25_0_, fromattrib1_.MatPhysicalName as MatPhysi9_25_0_, fromattrib1_.MatCanBeNull as MatCanB10_25_0_, fromattrib1_.MatReadonly as MatRead11_25_0_, fromattrib1_.jdbc_type as jdbc_ty12_25_0_, fromattrib1_.MatType as MatType13_25_0_, fromattrib1_.MatDescription as MatDesc14_25_0_, fromattrib1_.MatPrecision as MatPrec15_25_0_, table2_.MtbID as MtbID1_34_1_, table2_.uuid as uuid2_34_1_, table2_.MmtID as MmtID3_34_1_, table2_.MtbOrder as MtbOrder4_34_1_, table2_.MtbLogicalName as MtbLogic5_34_1_, table2_.MtbName as MtbName6_34_1_, table2_.MtbCreationDate as MtbCreat7_34_1_, table2_.MtbLastModifiedDate as MtbLastM8_34_1_, table2_.MtbDescription as MtbDescr9_34_1_, table2_.MdbID as MdbID10_34_1_, tabletype3_.MmtID as MmtID1_35_2_, tabletype3_.MmtName as MmtName2_35_2_, tabletype3_.MmtType as MmtType3_35_2_ from M_ForeignKey this_ inner join M_Attribute fromattrib1_ on this_.MatFromID=fromattrib1_.MatID inner join M_Table table2_ on fromattrib1_.MtbID=table2_.MtbID inner join M_TableType tabletype3_ on table2_.MmtID=tabletype3_.MmtID where tabletype3_.MmtType<>? order by this_.MfkID asc ) inner_query ) SELECT MfkID1_30_3_, MfkCasca2_30_3_, uuid3_30_3_, MatToID4_30_3_, MfkSoft5_30_3_, MatFromI6_30_3_, MfkDescr7_30_3_, page0_ FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?";
Assert.assertEquals("generated query is different", expectedQuery, generatedQuery);
}
{code}
{code:java}
Expected :WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( select TOP(?) this_.MfkID as MfkID1_30_3_, this_.MfkCascade as MfkCasca2_30_3_, this_.uuid as uuid3_30_3_, this_.MatToID as MatToID4_30_3_, this_.MfkSoft as MfkSoft5_30_3_, this_.MatFromID as MatFromI6_30_3_, this_.MfkDescription as MfkDescr7_30_3_, fromattrib1_.MatID as MatID1_25_0_, fromattrib1_.MtbID as MtbID2_25_0_, fromattrib1_.MatPrimaryKey as MatPrima3_25_0_, fromattrib1_.uuid as uuid4_25_0_, fromattrib1_.MatSize as MatSize5_25_0_, fromattrib1_.MlokDataTypeId as MlokData6_25_0_, fromattrib1_.MatSequenceNr as MatSeque7_25_0_, fromattrib1_.MatLogicalName as MatLogic8_25_0_, fromattrib1_.MatPhysicalName as MatPhysi9_25_0_, fromattrib1_.MatCanBeNull as MatCanB10_25_0_, fromattrib1_.MatReadonly as MatRead11_25_0_, fromattrib1_.jdbc_type as jdbc_ty12_25_0_, fromattrib1_.MatType as MatType13_25_0_, fromattrib1_.MatDescription as MatDesc14_25_0_, fromattrib1_.MatPrecision as MatPrec15_25_0_, table2_.MtbID as MtbID1_34_1_, table2_.uuid as uuid2_34_1_, table2_.MmtID as MmtID3_34_1_, table2_.MtbOrder as MtbOrder4_34_1_, table2_.MtbLogicalName as MtbLogic5_34_1_, table2_.MtbName as MtbName6_34_1_, table2_.MtbCreationDate as MtbCreat7_34_1_, table2_.MtbLastModifiedDate as MtbLastM8_34_1_, table2_.MtbDescription as MtbDescr9_34_1_, table2_.MdbID as MdbID10_34_1_, tabletype3_.MmtID as MmtID1_35_2_, tabletype3_.MmtName as MmtName2_35_2_, tabletype3_.MmtType as MmtType3_35_2_ from M_ForeignKey this_ inner join M_Attribute fromattrib1_ on this_.MatFromID=fromattrib1_.MatID inner join M_Table table2_ on fromattrib1_.MtbID=table2_.MtbID inner join M_TableType tabletype3_ on table2_.MmtID=tabletype3_.MmtID where tabletype3_.MmtType<>? order by this_.MfkID asc ) inner_query ) SELECT MfkID1_30_3_, MfkCasca2_30_3_, uuid3_30_3_, MatToID4_30_3_, MfkSoft5_30_3_, MatFromI6_30_3_, MfkDescr7_30_3_, page0_ FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?
Actual :WITH query AS (SELECT inner_query.*, ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ FROM ( select TOP(?) this_.MfkID as MfkID1_30_3_, this_.MfkCascade as MfkCasca2_30_3_, this_.uuid as uuid3_30_3_, this_.MatToID as MatToID4_30_3_, this_.MfkSoft as MfkSoft5_30_3_, this_.MatFromID as MatFromI6_30_3_, this_.MfkDescription as MfkDescr7_30_3_, as page0_ fromattrib1_.MatID as MatID1_25_0_, fromattrib1_.MtbID as MtbID2_25_0_, fromattrib1_.MatPrimaryKey as MatPrima3_25_0_, fromattrib1_.uuid as uuid4_25_0_, fromattrib1_.MatSize as MatSize5_25_0_, fromattrib1_.MlokDataTypeId as MlokData6_25_0_, fromattrib1_.MatSequenceNr as MatSeque7_25_0_, fromattrib1_.MatLogicalName as MatLogic8_25_0_, fromattrib1_.MatPhysicalName as MatPhysi9_25_0_, fromattrib1_.MatCanBeNull as MatCanB10_25_0_, fromattrib1_.MatReadonly as MatRead11_25_0_, fromattrib1_.jdbc_type as jdbc_ty12_25_0_, fromattrib1_.MatType as MatType13_25_0_, fromattrib1_.MatDescription as MatDesc14_25_0_, fromattrib1_.MatPrecision as MatPrec15_25_0_, table2_.MtbID as MtbID1_34_1_, table2_.uuid as uuid2_34_1_, table2_.MmtID as MmtID3_34_1_, table2_.MtbOrder as MtbOrder4_34_1_, table2_.MtbLogicalName as MtbLogic5_34_1_, table2_.MtbName as MtbName6_34_1_, table2_.MtbCreationDate as MtbCreat7_34_1_, table2_.MtbLastModifiedDate as MtbLastM8_34_1_, table2_.MtbDescription as MtbDescr9_34_1_, table2_.MdbID as MdbID10_34_1_, tabletype3_.MmtID as MmtID1_35_2_, tabletype3_.MmtName as MmtName2_35_2_, tabletype3_.MmtType as MmtType3_35_2_ from M_ForeignKey this_ inner join M_Attribute fromattrib1_ on this_.MatFromID=fromattrib1_.MatID inner join M_Table table2_ on fromattrib1_.MtbID=table2_.MtbID inner join M_TableType tabletype3_ on table2_.MmtID=tabletype3_.MmtID where tabletype3_.MmtType<>? order by this_.MfkID asc ) inner_query ) SELECT MfkID1_30_3_, MfkCasca2_30_3_, uuid3_30_3_, MatToID4_30_3_, MfkSoft5_30_3_, MatFromI6_30_3_, MfkDescr7_30_3_, page0_ FROM query WHERE __hibernate_row_nr__ >= ? AND __hibernate_row_nr__ < ?
{code}
SQLServer2005LimitHandler adds alias
{code:java}
as page0_
{code}
without expression to the inner selection query.
root cause exception:
{code:java}
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword 'as'.
{code}
|
|
