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

Criteria generates illegal sql for subquery from entity with composite key

Description

I trying to get count from entity with composite key:
@Entity
@Table(name = "FIM_MFU_FILE_LOC")
class FimMfuFileLoc extends Serializable{
@Id
@BeanProperty
@Column(name = "FEED_ID")
var feedId: String = _

@Id
@org.hibernate.annotations.Type(`type` = "org.jadira.usertype.dateandtime.joda.PersistentLocalDate")
@BeanProperty
@Column(name = "COB_DT")
var cobDt: LocalDate = _

@Column(name = "FILE_PATH", length = 150, nullable = false)
var filePath: String = null
}

  • One way:
    val criteria = cb.createQuery(classOf[java.lang.Long])
    val e = criteria.from(entityType)
    em.createQuery(criteria.select(cb.countDistinct(e)).where(filter)).getSingleResult
    As result:
    select count(distinct fimmfufile0_.FEED_ID, fimmfufile0_.COB_DT) as col_0_0_ from FIM_MFU_FILE_LOC fimmfufile0_ where 1=1
    It is not valid sql for oracle

  • Ok, another way
    val criteria = cb.createQuery(classOf[java.lang.Long])
    val e = criteria.from(classOf[FimMfuFileLoc])
    val sub = criteria.subquery(classOf[FimMfuFileLoc])
    val e_ = sub.from(classOf[FimMfuFileLoc])
    sub.select(e_).distinct(true).where(filter)
    em.createQuery(criteria.select(cb.count(e)).where(cb.exists(sub).asInstanceOf[Expression[JBoolean]])).getSingleResult
    So, result is
    : select count as col_0_0_ from FIM_MFU_FILE_LOC fimmfufile0_ where exists (select distinct (fimmfufile1_.FEED_ID, fimmfufile1_.COB_DT) from FIM_MFU_FILE_LOC fimmfufile1_ where 1=1)
    distinct (fimmfufile1_.FEED_ID, fimmfufile1_.COB_DT) is NOT VALID,
    but distinct fimmfufile1_.FEED_ID, fimmfufile1_.COB_DT is VALID

Environment

None

Status

Assignee

Unassigned

Reporter

nikl

Fix versions

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

4.3.11

Priority

Major