title: Spring Data Native query pagination not working
body: <p>I am using Spring Boot 2.0.2.RELEASE with Spring Data JPA. I am trying to do implement pagination with native query in MySql, my code is :</p>
  
  <pre><code>@Query(nativeQuery=true, value = "SELECT DISTINCT ud.latitude,ud.longitude,u.user_id userId FROM users u \n" +
              "INNER JOIN user_devices ud ON u.id = ud.user_id\n" +
              "WHERE ud.access_token IS NOT NULL AND ud.user_id &lt;&gt; 1\n" +
              "ORDER BY calculateDistanceByLatLong(:userLat, :userLong, ud.latitude, ud.longitude) ASC \n#pageable\n",
              countQuery = "SELECT COUNT(DISTINCT u.id) FROM users u \n" +
                      "INNER JOIN user_devices ud ON u.id = ud.user_id\n" +
              "WHERE ud.access_token IS NOT NULL AND ud.user_id &lt;&gt; 1 \n#pageable\n")
      public Page&lt;LocationProjection&gt; listNearByUsers(@Param("userLat")String userLatitude,@Param("userLong") String userLongitude, Pageable pageable) throws Exception;
  </code></pre>
  
  <p>I got the reference from <a href="https://stackoverflow.com/questions/49625873/java-spring-jpa-pagination-not-working-if-more-than-one-page">this link.</a></p>
  
  <p>And also reviewed <a href="https://stackoverflow.com/questions/38349930/spring-data-and-native-query-with-pagination">this link.</a></p>
  
  <p>But it's not adding any pagination code.
  When I try to use :</p>
  
  <pre><code>userDao.listNearByUsers(userDeviceEntity.getLatitude(),userDeviceEntity.getLongitude(), PageRequest.of(pageNo, maxResults))
  </code></pre>
  
  <p>And for example set pageNo=0 and maxResults =1,all the results are displayed. So no pagination is being implemented.
  I printed the fired query, it is :</p>
  
  <pre><code>SELECT
  DISTINCT ud.latitude,
  ud.longitude,
  u.user_id userId 
  FROM
  users u  
  INNER JOIN
  user_devices ud 
  ON u.id = ud.user_id 
  WHERE
  ud.access_token IS NOT NULL 
  AND ud.user_id &lt;&gt; 1 
  ORDER BY
  calculateDistanceByLatLong(?,
  ?,
  ud.latitude,
  ud.longitude) ASC  #pageable
  </code></pre>
  
  <p>And the count query as :</p>
  
  <pre><code>SELECT
  COUNT(DISTINCT u.id) 
  FROM
  users u  
  INNER JOIN
  user_devices ud 
  ON u.id = ud.user_id 
  WHERE
  ud.access_token IS NOT NULL 
  AND ud.user_id &lt;&gt; 1  #pageable 
  </code></pre>
  
  <p>I thought Spring Data would add "LIMIT 0,1" in the main query but it's not working. The DAO interface is :</p>
  
  <pre><code>@Repository
  public interface UserDao extends JpaRepository&lt;UserEntity, Integer&gt;{}
  </code></pre>
  
  <p>Please suggest some solution.</p>