Criteria generates illegal sql for subquery from entity with composite key
Description
Activity
Steve EbersoleOctober 28, 2015 at 3:24 AM
As part of verifying that this issue affects 5.0, please just set the "Affects version". Leave the "verify-affects-5.0" label and leave the issue in "Awaiting Response" status; these are critical for us to be able to track these verifications and triage them. Thanks.
Steve EbersoleOctober 27, 2015 at 7:15 PM
This bug report does not indicate that the reported issue affects version 5.x. Versions prior to 5.x are no longer maintained. It would be a great help to the Hibernate team and community for someone to verify that the reported issue still affects version 5.x. If so, please add the 5.x version that you verified with to the list of affected-versions and attach the (preferably SSCCE) test case you used to do the verification to the report; from there the issues will be looked at during our triage meetings.
For details, see http://in.relation.to/2015/10/27/great-jira-cleanup-2015/
niklSeptember 11, 2015 at 2:59 PM
I don't know exactly, but I think the latest versions of hibernate orm contain the bug
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