@Fetch(FetchMode.SELECT) was initially added like a FetchType.LAZY while FetchMode.SELECT was equivalent to FetchType.EAGER. Thatw as long before JPA 1.0.

Now, when it comes to JPA 2.1 Entity Graphs, the standard says nothing about this Hibernate-specific annotation.

Steve Ebersole Will we ever consider that if we annotate an association with @Fetch(FetchMode.SELECT) and the user wants to fetch it via Entity Graphs, then we should use secondary SELECTs rather than JOINs?

Now, let's get back to your statement:

since in the former case it will load potentially millions of rows and apply the pagination in memory, in the latter it will throw an exception.

If your collection has millions of rows, you are doing it all wrong. Collections are only useful when the number of child records is rather small. Otherwise, use @ManyToOne and queries, which allow you to use pagination as well.

As for HHH000104: firstResult/maxResults specified with collection fetch;, you can address it elegantly using Window Functions as explained in this article.