Uploaded image for project: 'Hibernate ORM'
  1. HHH-11704

SubQuery in select clause with join with main table create wrong SQL

    Details

    • Type: Bug
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects versions: 5.2.3
    • Fix versions: None
    • Components: query-criteria
    • Labels:
      None
    • Environment:
      Oracle 11g, Hibernate 5.2.3
    • Bug Testcase Reminder (view):

      Bug reports should generally be accompanied by a test case!

    • Last commented by a user?:
      true
    • Sprint:

      Description

      I want to do a select with a subQuery joind with the main query in the select like this:

      SELECT c.ID,  c.NAME,
        (SELECT MAX(o.CREATED_DATE) FROM ORDER o  WHERE o.CUSTOMER_ID=c.ID) as LAST_ORDER
      FROM CUSTOMER c
      WHERE c.AREA=?;
      

      I use this code to do the select:

          public List<CustomerRad> getCustomersAndLastOrder(String area) {
              CriteriaBuilder builder = getCriteriaBuilder();
              CriteriaQuery<CustomerRad> mainQuery = builder.createQuery(CustomerRad.class);
              Root<CustomerEntity> root = mainQuery.from(CustomerEntity.class);
      
              Subquery<LocalDateTime> sqOrder = mainQuery.subquery(LocalDateTime.class);
              Root<OrderEntity> orderRoot = sqOrder.from(OrderEntity.class);
      
              mainQuery.where(builder.equal(root.get(CustomerEntity_.area), area));
              sqOrder.select(builder.greatest(orderRoot.get(OrderEntity_.createdDate)));
              sqOrder.where(builder.equal(orderRoot.get(OrderEntity_.arbeidsflyt), root));
      
              mainQuery.select(
                      builder.construct(CustomerRad.class,
                              root.get(CustomerEntity_.id),
                              root.get(CustomerEntity_.name),
                              sqOrder.getSelection()
                      )
              );
      
              return getEntityManager().createQuery(mainQuery).getResultList();
          }
      

      And get this SQL, that produce the correct result, but got an unnecasery join in the subquery. (formatted to make it more readable

      SELECT customeren0_.ID     AS col_0_0_,
        customeren0_.NAME AS col_1_0_,
        (SELECT MAX(orderentit1_.CREATED_DATE)
        FROM ORDER orderentit1_,
          CUSTOMER customeren2_
        WHERE orderentit1_.CUSTOMER_ID=customeren2_.ID
        AND orderentit1_.CUSTOMER_ID  =customeren0_.ID
        ) AS col_2_0_
      FROM CUSTOMER customeren0_
      WHERE customeren0_.AREA=?;
      

      I would expect it to be like this:

      SELECT customeren0_.ID     AS col_0_0_,
        customeren0_.NAME AS col_1_0_,
        (SELECT MAX(orderentit1_.CREATED_DATE)
        FROM ORDER orderentit1_
        WHERE orderentit1_.CUSTOMER_ID=customeren0_.ID
        ) AS col_2_0_
      FROM CUSTOMER customeren0_
      WHERE customeren0_.AREA=?;
      

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              atokle Atle Tokle
              Participants:
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: