title: Issue with Join Fetch Hibernate query
body: <p>I am new using hibernate join fetch queries and would like some help on the below matter.
  I have one Entity that has quite a few OneToMany associations. And in the rest call
  I need to load all of those associations to prepare the result.</p>
  <p>I was using Jpa findAll(Pageable) method however on production seeing huge load on read queries. I understand that I ran into N+1 problem which I am trying to solve however getting bellow error:</p>
  <pre><code>   {
    &quot;title&quot; : &quot;Internal Server Error&quot;,
    &quot;status&quot; : 500,
    &quot;detail&quot; : &quot;firstResult/maxResults specified with collection fetch. In memory pagination was about to be applied. Failing because 'Fail on pagination over collection fetch' is enabled.; nested exception is org.hibernate.HibernateException: firstResult/maxResults specified with collection fetch. In memory pagination was about to be applied. Failing because 'Fail on pagination over collection fetch' is enabled.&quot;,
    &quot;cause&quot; : {
      &quot;title&quot; : &quot;Internal Server Error&quot;,
      &quot;status&quot; : 500,
      &quot;detail&quot; : &quot;firstResult/maxResults specified with collection fetch. In memory pagination was about to be applied. Failing because 'Fail on pagination over collection fetch' is enabled.&quot;
    }
  }
  </code></pre>
  <p>====================
  using Running with Spring Boot v2.5.4, Spring v5.3.9 and mysql 5.7
  Here is my code snippet:</p>
  <p>Entity:</p>
  <pre><code>package com.pitstop.catalogue.domain;
  
  import com.fasterxml.jackson.annotation.JsonIgnoreProperties;
  import com.pitstop.catalogue.util.EligibleOffer;
  import org.hibernate.annotations.BatchSize;
  import org.hibernate.annotations.CacheConcurrencyStrategy;
  import org.hibernate.annotations.Fetch;
  import org.hibernate.annotations.FetchMode;
  
  import javax.persistence.*;
  import javax.validation.constraints.NotNull;
  import javax.validation.constraints.Size;
  import java.io.Serializable;
  import java.time.Instant;
  import java.util.*;
  
  /**
   * A Product.
   */
  @Entity
  @Table(name = &quot;product&quot;)
  @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
  public class Product implements Serializable {
  
      private static final long serialVersionUID = 1L;
  
      public static final String PRODUCT_NAME = &quot;name&quot;;
      public static final String PRODUCT_CATEGORY = &quot;category_id&quot;;
      public static final String PRODUCT_UNIT_PRICE = &quot;unit_price&quot;;
  
      @Id
      @GeneratedValue(strategy = GenerationType.IDENTITY)
      private Long id;
  
      @NotNull
      @Size(max = 250)
      @Column(name = PRODUCT_NAME, length = 50, nullable = false)
      private String name;
  
      @Size(max = 1000)
      @Column(name = &quot;description&quot;, length = 200)
      private String description;
      
      private Long brandId;
  
      @Size(max = 500)
      @Column(name = &quot;product_icon_url&quot;, length = 500)
      private String productIconUrl;
  
      @Column(name = PRODUCT_UNIT_PRICE)
      private Double unitPrice;
  
      @Column(name = &quot;gst_rate&quot;)
      private Double gstRate;
  
      @Size(max = 45)
      @Column(name = &quot;part_no&quot;, length = 45)
      private String partNo;
  
      @Size(max = 45)
      @Column(name = &quot;serial_no&quot;, length = 45)
      private String serialNo;
  
      @Size(max = 45)
      @Column(name = &quot;unit_type&quot;, length = 45)
      private String unitType;
  
      @Column(name = &quot;line_item_id&quot;)
      private Long lineItemId;
  
      private Long categoryId;
  
      @OneToMany(mappedBy = &quot;product&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @Fetch(FetchMode.JOIN)
      @JsonIgnoreProperties(value = {&quot;product&quot;}, allowSetters = true)
      @BatchSize(size = 20)
      @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
      private Set&lt;Offer&gt; offers = new HashSet&lt;&gt;();
  
      @OneToMany(mappedBy = &quot;product&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @Fetch(FetchMode.JOIN)
      @JsonIgnoreProperties(value = {&quot;product&quot;}, allowSetters = true)
      @BatchSize(size = 20)
      @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
      private Set&lt;ProductAttributes&gt; productAttributes = new HashSet&lt;&gt;();
  
      @OneToMany(mappedBy = &quot;product&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @Fetch(FetchMode.JOIN)
      @JsonIgnoreProperties(value = {&quot;product&quot;, &quot;productModel&quot;}, allowSetters = true)
      @BatchSize(size = 20)
      @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
      private Set&lt;ProductModelMapping&gt; productModelMappings = new HashSet&lt;&gt;();
  
      @OneToMany(mappedBy = &quot;product&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
      @Fetch(FetchMode.JOIN)
      @JsonIgnoreProperties(value = {&quot;product&quot;}, allowSetters = true)
      @BatchSize(size = 20)
      @org.hibernate.annotations.Cache(usage = CacheConcurrencyStrategy.READ_ONLY)
      private Set&lt;ProductSeller&gt; productSellers = new HashSet&lt;&gt;();
  
      @Size(max = 45)
      @Column(name = &quot;tag&quot;, length = 50)
      private String tag;
  
      @Column(name = &quot;created&quot;)
      private Instant created;
  
      @Column(name = &quot;created_by&quot;, length = 100)
      private String createdBy;
  
      public Long getId() {
          return id;
      }
  
      public void setId(Long id) {
          this.id = id;
      }
  
      public String getName() {
          return name;
      }
  
      public void setName(String name) {
          this.name = name;
      }
  
      public String getDescription() {
          return description;
      }
  
      public void setDescription(String description) {
          this.description = description;
      }
  
      public String getProductIconUrl() {
          return productIconUrl;
      }
  
      public void setProductIconUrl(String productIconUrl) {
          this.productIconUrl = productIconUrl;
      }
  
      public Double getUnitPrice() {
          return unitPrice;
      }
  
      public void setUnitPrice(Double unitPrice) {
          this.unitPrice = unitPrice;
      }
  
      public Double getGstRate() {
          return gstRate;
      }
  
      public void setGstRate(Double gstRate) {
          this.gstRate = gstRate;
      }
  
      public String getPartNo() {
          return partNo;
      }
  
      public void setPartNo(String partNo) {
          this.partNo = partNo;
      }
  
      public String getSerialNo() {
          return serialNo;
      }
  
      public void setSerialNo(String serialNo) {
          this.serialNo = serialNo;
      }
  
      public String getUnitType() {
          return unitType;
      }
  
      public void setUnitType(String unitType) {
          this.unitType = unitType;
      }
  
      public Set&lt;Offer&gt; getOffers() {
          return offers;
      }
  
      public void setOffers(Set&lt;Offer&gt; offers) {
          this.offers = offers;
      }
  
      public Set&lt;ProductAttributes&gt; getProductAttributes() {
          return productAttributes;
      }
  
      public void setProductAttributes(Set&lt;ProductAttributes&gt; productAttributes) {
          this.productAttributes = productAttributes;
      }
  
      public Set&lt;ProductModelMapping&gt; getProductModelMappings() {
          return productModelMappings;
      }
  
      public void setProductModelMappings(Set&lt;ProductModelMapping&gt; productModelMappings) {
          this.productModelMappings = productModelMappings;
      }
  
      public Set&lt;ProductSeller&gt; getProductSellers() {
          return productSellers;
      }
  
      public void setProductSellers(Set&lt;ProductSeller&gt; productSellers) {
          this.productSellers = productSellers;
      }
  
      public Long getLineItemId() {
          return lineItemId;
      }
  
      public void setLineItemId(Long lineItemId) {
          this.lineItemId = lineItemId;
      }
  
      @Transient
      public double getSellingPrice() {
          // As discussed with Rohit, iterate over all the valid offers (by date)
          // and then apply which has the highest discount value. This is for Pitstop offers only.
          // Sellers offers will be created by sellers.
          if (this.offers.size() &gt; 0) {
              Double sellingPriceFromOffers = EligibleOffer.apply(this.offers, this.unitPrice);
              return sellingPriceFromOffers;
          }
          return this.unitPrice;
      }
  
      @Transient
      public double getProductUnitPrice() {
          return this.unitPrice;
      }
  
      @Transient
      public double getProductGstRate() {
          if (callSellers() != null &amp;&amp; callSellers().size() &gt; 0) {
              ProductSeller productSeller = callSellers().get(0);
              return productSeller.getGstRate();
          }
          return gstRate;
      }
  
      @Transient
      public List&lt;ProductSeller&gt; callSellers() {
          //sorting products by seller price in ascending order
          List&lt;ProductSeller&gt; sellers = new ArrayList&lt;&gt;();
          sellers.addAll(productSellers);
          Collections.sort(sellers, (Comparator.comparing(ProductSeller::getUnitPrice)));
          return sellers;
      }
  
      public String getTag() {
          return tag;
      }
  
      public void setTag(String tag) {
          this.tag = tag;
      }
  
      public Instant getCreated() {
          return created;
      }
  
      public void setCreated(Instant created) {
          this.created = created;
      }
  
      public String getCreatedBy() {
          return createdBy;
      }
  
      public void setCreatedBy(String createdBy) {
          this.createdBy = createdBy;
      }
  
      public Long getBrandId() {
          return brandId;
      }
  
      public void setBrandId(Long brandId) {
          this.brandId = brandId;
      }
  
      public Long getCategoryId() {
          return categoryId;
      }
  
      public void setCategoryId(Long categoryId) {
          this.categoryId = categoryId;
      }
  
      @Override
      public String toString() {
          return &quot;Product{&quot; +
                  &quot;id=&quot; + id +
                  &quot;, name='&quot; + name + '\'' +
                  &quot;, description='&quot; + description + '\'' +
                  &quot;, brand=&quot; + brandId +
                  &quot;, productIconUrl='&quot; + productIconUrl + '\'' +
                  &quot;, unitPrice=&quot; + unitPrice +
                  &quot;, gstRate=&quot; + gstRate +
                  &quot;, partNo='&quot; + partNo + '\'' +
                  &quot;, serialNo='&quot; + serialNo + '\'' +
                  &quot;, unitType='&quot; + unitType + '\'' +
                  &quot;, lineItemId=&quot; + lineItemId +
                  &quot;, category=&quot; + categoryId +
                  &quot;, offers=&quot; + offers +
                  &quot;, productAttributes=&quot; + productAttributes +
                  &quot;, productModelMappings=&quot; + productModelMappings +
                  &quot;, productSellers=&quot; + productSellers +
                  &quot;, tag='&quot; + tag + '\'' +
                  &quot;, created=&quot; + created +
                  &quot;, createdBy='&quot; + createdBy + '\'' +
                  '}';
      }
  }
  </code></pre>
  <p>Repository:</p>
  <pre><code>@Repository
  public interface ProductRepository extends JpaRepository&lt;Product, Long&gt;, JpaSpecificationExecutor&lt;Product&gt; {
  
      List&lt;Product&gt; findAllByCategoryId(Long categoryId);
  
      List&lt;Product&gt; findAllByBrandId(Long brandId);
  
      @Query(value = &quot;select p &quot; +
              &quot;from Product p &quot; +
              &quot;left join fetch p.productAttributes productAttributes&quot;,
      countQuery = &quot;select count(p) from Product p&quot;)
      Page&lt;Product&gt; findAll(Pageable pageable);
  }
  </code></pre>