How to impose LIMIT on sub-query of JPA query?

There is no pure JPA solution for this. You could make use of a custom SQL function that runs during SQL query generation time. All JPA providers support something like that in one way or another.

If you don't want to implement that yourself or even want a proper API for constructing such queries, I can only recommend you the library I implemented called Blaze-Persistence.

Here is the documentation showcasing the limit/offset use case with subqueries: https://persistence.blazebit.com/documentation/core/manual/en_US/index.html#pagination

Your query could look like this with the query builder API:

criteriaBuilderFactory.create(entityManager, IpInfo.class)
  .where("rangeEnd").lt()
    .from(IpInfo.class, "subInfo")
    .select("subInfo.rangeStart")
    .where("subInfo.rangeStart").geExpression("1537022421")
    .orderByAsc("subInfo.rangeStart")
    .setMaxResults(1)
  .end()
  .where("1537022421").leExpression("rangeEnd")
  .orderByDesc("rangeEnd")
  .setMaxResults(1)

It essentially boils down to using the LIMIT SQL function that is registered by Blaze-Persistence. So when you bootstrap Blaze-Persistence with your EntityManagerFactory, you should even be able to use it like this

entityManager.createQuery(
    "select * from ipinfo where RangeEnd < LIMIT((" +
    "  select RangeStart " +
    "  from ipinfo " +
    "  where RangeStart >= 1537022421 " +
    "  order by RangeStart asc" +
    "),1) " +
    "and (1537022421 <= RangeEnd)" +
    "ORDER BY RangeEnd desc"
).setMaxResults(1)

If you are using EclipseLink the calling convention of such functions looks like OPERATOR('LIMIT', ...).

It is not currently possible to add a LIMIT (maxResults) or OFFSET (startIndex) via JPQL, nor is it possible to do so on a JPA subquery. There is an open JPA ticket to add this functionality:

https://github.com/eclipse-ee4j/jpa-api/issues/88

I don't know how to do that with JPQL but you can probably handle it with the Criteria API, at least i'm pretty sure we can do this with Hibernate criteria subqueries so i guess it's also possible with JPA even if the JPA criteria api appears a bit confusing to me.

Check this: JPA 2.0, Criteria API, Subqueries, In Expressions

Anyway you don't even need a limit on your subquery.

Your original query: select RangeStart from ipinfo where RangeStart >= 1537022421 order by RangeStart asc limit 1 It seems you want the minimum RangeStart of your ipinfo list, which is just above a given value. The min function has been made for that.

You could simply use a subquery like this:

select min(RangeStart) from ipinfo where RangeStart >= 1537022421

Even if you need other ipinfo returned on your subquery it could be done with something like that:

select RangeEnd, anything,blabla from ipinfo where RangeStart = (
    select min(RangeStart) from ipinfo where RangeStart >= 1537022421
)