Message Title

Create an inner query to select from a table. Then, create an outer query to perform the limit on that in order to provide better paging. Then put an 'ORDER BY' option onto the query.

In MySQL etc this is fine because of the limit clause exists, but we are getting strange results in SQL Server when it uses the TOP

and the sub queries.

Basically, SQL Server reports it as inaccurate as it has a ORDER BY in the sub query which is where it can't go. An example is (as produced by our application:

WITH query AS (
	SELECT inner_query.*,  
		ROW_NUMBER() OVER (ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__
	FROM ( 
		select this_.id as id1_461_0_, this_.account_id as account38_461_0_, this_.createdAt as createdA2_461_0_
		from Referral this_ 
		where  this_.id in (
					select distinct TOP(10) r_.id as y0_ 
					from Referral r_ 
					inner join Patient patient1_ on r_.patient_id=patient1_.id 
							) 
		ORDER BY this_.createdAt 
		) inner_query 
	)
SELECT id1_461_0_, account38_461_0_, createdA2_461_0_,
FROM query 
	WHERE __hibernate_row_nr__ >= 0
		AND __hibernate_row_nr__ < 10

I have created a test case for this which runs fine on the internal testing for Hibernate, but I am sure it will fail when you run it on SQL Server. It creates 19 records and does a select on the first ones.

package org.hibernate.test.pagination;

import org.hibernate.Criteria;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Order;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Subqueries;
import org.hibernate.testing.junit4.BaseCoreFunctionalTestCase;
import org.junit.After;
import org.junit.Test;

import static org.junit.Assert.assertThat;
import static org.hamcrest.core.Is.is;

import javax.persistence.*;

import java.io.Serializable;

import static org.hibernate.testing.transaction.TransactionUtil.doInHibernate;

/**
 * Created by matjohnson on 11/10/2016.
 */
public class PaginationOrderByTest extends BaseCoreFunctionalTestCase {

    @Override
    protected Class<?>[] getAnnotatedClasses() {
        return new Class[]{Student.class};
    }

    @Override
    protected void prepareTest() throws Exception {
        doInHibernate(this::sessionFactory, session -> {
            for (int i = 1; i < 20; i++) {
                final Student student2 = new Student();
                student2.setId(new Identifier( 4 + i, new Identifier2( 4 + i, 6L + i) )  );
                student2.setTheId(2);
                student2.setName( "Livia" + i );
                student2.setStatus( "active" + i );
                student2.setAge( i );
                session.save(student2);
            }
        });
    }

    @After
    public void tearDown() {
        doInHibernate(this::sessionFactory, session -> {
            session.createQuery("delete from Student").executeUpdate();
        });
    }

    @Test
    public void testGettingData() {
        doInHibernate(this::sessionFactory, session -> {
            Criteria theCriteria = session.createCriteria(Student.class);
            assertThat(theCriteria.list().size(), is(19));
        });
    }

    @Test
    public void testDetachedSearch() {
        doInHibernate(this::sessionFactory, session -> {
            DetachedCriteria theCriteria = DetachedCriteria.forClass(Student.class, "s");
            theCriteria.setProjection(Projections.distinct(Projections.property("s.theId")));

            final Criteria outer = session.createCriteria(Student.class,"d");
            outer.add(Subqueries.propertyIn("d.theId", theCriteria));
            outer.addOrder(Order.asc("name"));

            outer.setMaxResults(10);
            int page = 2;
            int resultsPerPage = 10;
            outer.setFirstResult((page - 1) * resultsPerPage);

            assertThat(outer.list().size(),is(9));
        });
    }

    @Entity(name = "Student")
    @Table(name = "STUDENT")
    public static class Student {


        @EmbeddedId
        private Identifier id;

        private int theId;

        private String name;
        private int age;

        @Column(name = "STATUS")
        private String status;

        public void setTheId(int id) {
            this.theId = id;
        }

        public void setId(Identifier id) {
            this.id = id;
        }

        public void setName(String name) {
            this.name = name;
        }

        public void setStatus(String status) {
            this.status = status;
        }

        public void setAge(int age) {
            this.age = age;
        }
    }

    @Embeddable
    public static class Identifier implements Serializable {

        private Integer id1;

        @Embedded
        private Identifier2 id2;

        public Identifier() {
        }

        public Identifier(Integer id1, Identifier2 id2) {
            this.id1 = id1;
            this.id2 = id2;
        }
    }

    @Embeddable
    public static class Identifier2 implements Serializable {

        private Integer id3;

        private Long id4;

        public Identifier2() {
        }

        public Identifier2(Integer id1, Long id2) {
            this.id3 = id1;
            this.id4 = id2;
        }
    }
}

Happy for it to be ourselves, but I can't see why this would produce invalid SQL for SQL Server when we are not changing anything. But I can see how it is valid for MySQL etc as the limit function seems to work fine.