Uploaded image for project: '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
    • Status: Closed
    • Priority: 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
    • 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
      

        Attachments

          Activity

          Hide
          ewestfal 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
          ewestfal 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:
              ewestfal Eric Westfall
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: