Spring Data JPA Native Query Pagination Doesn't work on 2nd Page

Question

I am using pagination on a native query (SQL Server) which works on the initial page request, page 0, but fails with an invalid column name when I try to get the results for the 2nd page. The generated query works in SSMS but does not in Spring.

Any insight is appreciated.

Here is the error:

Hibernate: 
    /* dynamic native SQL query */ WITH query AS (SELECT
        inner_query.*,
        ROW_NUMBER() OVER (
    ORDER BY
        CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
    FROM
        ( SELECT
            TOP(?) p.ProductKey as page0_,
            p.ProductName as page1_,
            p.Sku as page2_,
            p.MaterialKey as page3_,
            p.MfgItemNumber as page4_,
            p.PackageSize as page5_,
            p.PriceUom as page6_,
            p.CasePack as page7_,
            p.ProductDescription as page8_,
            P.MediaDomain as page9_,
            p.ImagePath as page10_,
            p.ImageName as page11_,
            plp.Price as page12_,
            plp.ListPrice as page13_,
            p.SearchText  as page14_ 
        FROM
            [dbo].[BdmProductCategory] pc 
        INNER JOIN
            [dbo].BdmProduct p 
                on pc.ProductKey = p.productKey 
        INNER JOIN
            [dbo].BdmPriceListProduct plp 
                on plp.ProductKey = p.ProductKey 
        WHERE
            pc.CategoryKey = ? 
            AND pc.ProductKey > 0 
            AND pc."Delete" = 0 
            AND plp.PriceListKey = ?   -- #pageable   
        order by
            sku asc ) inner_query ) SELECT
            page0_,
            page1_,
            page2_,
            page3_,
            page4_,
            page5_,
            page6_,
            page7_,
            page8_,
            page9_,
            page10_,
            page11_,
            page12_,
            page13_,
            page14_ 
        FROM
            query 
        WHERE
            __hibernate_row_nr__ >= ? 
            AND __hibernate_row_nr__ < ?
2017-08-18 08:47:45.844 TRACE 19316 --- [nio-6193-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [2] as [BIGINT] - [4143]
2017-08-18 08:47:45.845 TRACE 19316 --- [nio-6193-exec-2] o.h.type.descriptor.sql.BasicBinder      : binding parameter [3] as [BIGINT] - [657]
2017-08-18 08:47:45.980  WARN 19316 --- [nio-6193-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : SQL Error: 0, SQLState: S1093
2017-08-18 08:47:45.980 ERROR 19316 --- [nio-6193-exec-2] o.h.engine.jdbc.spi.SqlExceptionHelper   : The column name productKey is not valid.
2017-08-18 08:47:45.990 ERROR 19316 --- [nio-6193-exec-2] o.a.c.c.C.[.[.[/].[dispatcherServlet]    : Servlet.service() for servlet [dispatcherServlet] in context with path [] threw exception [Request processing failed; nested exception is org.springframework.orm.jpa.JpaSystemException: could not execute query; nested exception is org.hibernate.exception.GenericJDBCException: could not execute query] with root cause com.microsoft.sqlserver.jdbc.SQLServerException: The column name productKey is not valid.

Here is the model:

@Entity
@Table(name = "vw_BdmProductInfo")  // A view!
public class BdmProductInfo {

@Id
private Long productKey;
private Long    materialKey;
private String  sku;
private BigDecimal  price;
private BigDecimal  listPrice;
private String  priceUom;
private String  casePack;
private String  packageSize;
private String  mfgItemNumber;
private String  productName;
private String  productDescription;
private String  mediaDomain;
private String  ImagePath;
private String  imageName;
private String searchText;

protected BdmProductInfo() {}

....

Here is the Repository:

@RestResource(path="findProductInfoByCategory")
@Query(value="SELECT p.ProductKey, p.ProductName, p.Sku, p.MaterialKey, p.MfgItemNumber, p.PackageSize, p.PriceUom, p.CasePack, p.ProductDescription, P.MediaDomain, p.ImagePath, p.ImageName, plp.Price, plp.ListPrice, p.SearchText  "
            + "FROM [dbo].[BdmProductCategory] pc "
            + "INNER JOIN [dbo].BdmProduct p on pc.ProductKey = p.productKey "
            + "INNER JOIN [dbo].BdmPriceListProduct plp on plp.ProductKey = p.ProductKey "
            + "WHERE pc.CategoryKey = :categoryKey "
            + "AND pc.ProductKey > 0 "
            + "AND pc.\"Delete\" = 0 "
            + "AND plp.PriceListKey = :priceListKey  \n-- #pageable\n ",
            countQuery="SELECT Count(*) "
            + "FROM [dbo].[BdmProductCategory] pc "
            + "INNER JOIN [dbo].BdmProduct p on pc.ProductKey = p.productKey "
            + "INNER JOIN [dbo].BdmPriceListProduct plp on plp.ProductKey = p.ProductKey "
            + "WHERE pc.categoryKey = :categoryKey "
            + "AND pc.ProductKey > 0 "
            + "AND pc.\"Delete\" = 0 "
            + "AND plp.PriceListKey = :priceListKey ",nativeQuery=true)
    Page<BdmProductInfo> findProductInfoByCategory(@Param("categoryKey") Long categoryKey, @Param("priceListKey") Long priceListKey, Pageable pageable );

Answer 1

After making aliases for the query columns, Hibernate used the aliases rather than trying to generate its own ... causing the problem. So in the query, aliased each column selected p.productKey as productKey, etc... and that worked to get subsequent pages ... FWIW for anyone else that experiences the issue...

Credits : @Don