[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5700) Hibernate generates invalid SQL for criteria queries containing a predicate testing with a Long object.

Hibernate generates invalid SQL for criteria queries containing a predicate testing with a Long object. ------------------------------------------------------------------------------------------------------- Key: HHH-5700 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5700 Project: Hibernate Core Issue Type: Bug Components: core, query-criteria Affects Versions: 3.5.6 Environment: MySQL Ver 14.14 Distrib 5.1.50, for Win32 (ia32), using dialect org.hibernate.dialect.MySQLMyISAMDialect Reporter: Eduardo Born I have created a criteria query to get the "permissions" attribute from the Role entity with id = 2, but when executed by Hibernate the generated SQL is invalid. This is the criteria query: EntityManager entityManager = getEntityManager(); CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder(); CriteriaQuery<Object> criteriaQuery = criteriaBuilder.createQuery(); Class<?> queryScopeClass = temp.pack.commons.user.Role.class; Root<?> from = criteriaQuery.from(queryScopeClass); Path<?> idAttrPath = from.get("id"); // also tried criteriaBuilder.equal(attributePath, new Long(2)) Predicate predicate = criteriaBuilder.equal(attributePath, criteriaBuilder.literal(new Long(2))) criteriaQuery.where(predicate); Path<?> attributePath = from.get("permissions"); PluralAttributePath<?> pluralAttrPath = (PluralAttributePath<?>)attributePath; PluralAttribute<?, ?, ?> pluralAttr = pluralAttrPath.getAttribute(); Join<?, ?> join = from.join((SetAttribute<Object,?>)pluralAttr); TypedQuery<Object> typedQuery = entityManager.createQuery(criteriaQuery.select(join)); return (List<P>)typedQuery.getResultList(); When executed, Hibernate attempts to execute this query: select permission1_.PERMISSION_ID as PERMISSION1_12_, permission1_.IS_REQUIRED as IS2_12_, permission1_.SOURCE_ROLE_ID as SOURCE3_12_, permission1_.TARGET_ROLE_ID as TARGET4_12_ from ( select ROLE_ID, NAME, DESCRIPTION, IS_ACTION, LABEL, null as FIRST_NAME, null as LAST_NAME, null as PASSWORD_HASH, 1 as clazz_ from GROUPS union select ROLE_ID, NAME, null as DESCRIPTION, null as IS_ACTION, null as LABEL, FIRST_NAME, LAST_NAME, PASSWORD_HASH, 2 as clazz_ from USERS ) role0_ inner join PERMISSIONS permission1_ on role0_.ROLE_ID=permission1_.SOURCE_ROLE_ID where (role0_.ROLE_ID=2L ) Note the last line. The "2L" fails with the following exception: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1235) at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1168) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:250) at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:260) at temp.pack.dao.impl.DefaultDAOService.getProperties(DefaultDAOService.java:628) ... Caused by: org.hibernate.exception.SQLGrammarException: could not execute query at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) at org.hibernate.loader.Loader.doList(Loader.java:2452) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2192) at org.hibernate.loader.Loader.list(Loader.java:2187) at org.hibernate.hql.classic.QueryTranslatorImpl.list(QueryTranslatorImpl.java:936) at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1258) at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:241) ... 20 more Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column '2L' in 'where clause' at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source) at java.lang.reflect.Constructor.newInstance(Unknown Source) at com.mysql.jdbc.Util.handleNewInstance(Util.java:409) at com.mysql.jdbc.Util.getInstance(Util.java:384) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1054) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3562) at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3494) at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1960) at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2114) at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2696) at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2105) at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2264) at com.jolbox.bonecp.PreparedStatementHandle.executeQuery(PreparedStatementHandle.java:179) at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208) at org.hibernate.loader.Loader.getResultSet(Loader.java:1869) at org.hibernate.loader.Loader.doQuery(Loader.java:718) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.doList(Loader.java:2449) ... 27 more Clearly that "L" should not be included in the query. Here is how my entities were annotated: @Entity(name="Role") @Table(name = "ROLES") @Inheritance(strategy = InheritanceType.TABLE_PER_CLASS) public abstract class Role implements Serializable { /** * The id of this role. Internal use only. * * @since 1.0 */ @Id @GeneratedValue protected long id; /** * Set of permissions granted to this role. * * @since 1.0 */ @OneToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE }, mappedBy="sourceRole") protected Set<Permission> permissions = new HashSet<Permission>(); ... } public class Permission implements Serializable { private static final long serialVersionUID = 1L; /** * The id of this permission. Used internally for persistence. * * @since 1.0 */ @Id @GeneratedValue @Column(name = "PERMISSION_ID") protected long id; /** * The group to which the owner of this permission is being granted permission to. * * @since 1.0 */ @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE }) @JoinColumn(name = "TARGET_ROLE_ID") @ForeignKey(name = "FK_TARGET_GROUP_PERMISSION_ID", inverseName = "FK_PERMISSION_ID_TARGET_GROUP") protected Group targetGroup; /** * The role that has been granted this permission. * * @since 1.0 */ @ManyToOne(cascade = { CascadeType.PERSIST, CascadeType.MERGE }) @JoinColumn(name = "SOURCE_ROLE_ID") @ForeignKey(name = "FK_SOURCE_GROUP", inverseName = "FK_GROUP_PERMISSIONS") private Role sourceRole; ... } I have the impression this is happening to all criteria queries with Predicates testing for objects of Long type. I was using dialect org.hibernate.dialect.MySQLMyISAMDialect. Thank you! Eduardo