Hibernate generates invalid SQL for criteria queries containing a predicate testing with a Long object.

Description

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

Environment

MySQL Ver 14.14 Distrib 5.1.50, for Win32 (ia32), using dialect org.hibernate.dialect.MySQLMyISAMDialect

Activity

Show:
Eduardo Born
October 29, 2010, 2:13 AM

BTW, adding quotes to the 2L also works, so this might be a problem with the dialect I suppose.

Brett Meyer
April 7, 2014, 5:48 PM

In an effort to clean up, in bulk, tickets that are most likely out of date, we're transitioning all ORM 3 tickets to an "Awaiting Test Case" state. Please see http://in.relation.to/Bloggers/HibernateORMJIRAPoliciesAndCleanUpTactics for more information.

If this is still a legitimate bug in ORM 4, please provide either a test case that reproduces it or enough detail (entities, mappings, snippets, etc.) to show that it still fails on 4. If nothing is received within 3 months or so, we'll be automatically closing them.

Thank you!

Brett Meyer
July 8, 2014, 3:10 PM

Bulk rejecting stale issues. If this is still a legitimate issue on ORM 4, feel free to comment and attach a test case. I'll address responses case-by-case. Thanks!

Assignee

Unassigned

Reporter

Eduardo Born

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure