We're updating the issue view to help you get more done. 

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

Description

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

1 2 3 4 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:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 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

1 2 3 4 5 6 7 8 9 10 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:

1 2 3 4 5 6 7 8 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=?;

Environment

Oracle 11g, Hibernate 5.2.3

Status

Assignee

Unassigned

Reporter

Atle Tokle

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

5.2.3

Priority

Major