Kuali Rice Development
  1. Kuali Rice Development
  2. KULRICE-14280

Problem with JPA and wanting to do left outer join on entity bo

    Details

    • Type: Bug Fix Bug Fix
    • Status: Closed Closed
    • Priority: Major Major
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 2.5.6
    • Component/s: Development
    • Security Level: Public (Public: Anyone can view)
    • Labels:
      None
    • Similar issues:
      KULRICE-11496Convert KCB entities from OJB to JPA
      KULRICE-3906When KEW is JPA-enabled, blanket approvals on documents with split and join nodes fail to execute properly
      KULRICE-6000sequence number column in join table between parent and child propositions breaks agenda persistence
      KULRICE-14269JPA predicates cause unnecessary SQL joins and performance degradation
      KULRICE-13149Address problems with JPA related caching
      KULRICE-3856JPA - Modify creation of PersistenceUnit such that it automatically imports persistent classes from the KNS that may need to be joined with client-side
      KULRICE-11002Advanced Lookup Demo - integration test for BO persistence
      KULRICE-2998Recreate the KIM views in the Rice database
      KULRICE-4003Amend and test Rice's JPA Criteria API
      KULRICE-11725Finish KIM RoleDao conversion for JPA
    • KAI Review Status:
      Not Required
    • KTI Review Status:
      Not Required
    • Code Review Status:
      Not Required
    • Include in Release Notes?:
      Yes

      Description

      See https://kualico.atlassian.net/browse/RICE-1

      When attempting to do a person lookup with by role, get an error message like the following:

      org.springframework.orm.jpa.JpaSystemException: Exception [EclipseLink-4002] (Eclipse Persistence Services - 2.5.2.v20140319-9ad6abd): org.eclipse.persistence.exceptions.DatabaseException
      Internal Exception: java.sql.SQLException: Too many tables; MySQL can only use 61 tables in a join
      Error Code: 1116
      

        Activity

        Hide
        Eric Westfall added a comment -

        The issue with this one is that it was attempting to do a query for EntityBo by more than one principal id. The default strategy that an internal class (PredicateUtils) was using to convert the lookup criteria into predicates was create an OR per principal id. For large roles this causes many joins between the KRIM_ENTITY_T and KRIM_PRINCIPL_T tables.
        I fixed this by modifying it to use an "IN" clause in this case instead. While I was at it, I made this "Oracle safe" by detecting if there were more than 1000 items in the IN(...) list (which is oracles limit) and splitting them into multiple IN(...) clauses (OR'd together).

        Show
        Eric Westfall added a comment - The issue with this one is that it was attempting to do a query for EntityBo by more than one principal id. The default strategy that an internal class (PredicateUtils) was using to convert the lookup criteria into predicates was create an OR per principal id. For large roles this causes many joins between the KRIM_ENTITY_T and KRIM_PRINCIPL_T tables. I fixed this by modifying it to use an "IN" clause in this case instead. While I was at it, I made this "Oracle safe" by detecting if there were more than 1000 items in the IN(...) list (which is oracles limit) and splitting them into multiple IN(...) clauses (OR'd together).

          People

          • Assignee:
            Unassigned
            Reporter:
            Eric Westfall
          • Votes:
            0 Vote for this issue
            Watchers:
            1 Start watching this issue

            Dates

            • Created:
              Updated:
              Resolved:

              Structure Helper Panel