[hibernate-issues] [Hibernate-JIRA] Commented: (HHH-5700) Hibernate generates invalid SQL for criteria queries containing a predicate testing with a Long object.
Eduardo Born (JIRA)
noreply at atlassian.com
Thu Oct 28 20:15:48 EDT 2010
[ http://opensource.atlassian.com/projects/hibernate/browse/HHH-5700?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=38919#action_38919 ]
Eduardo Born commented on HHH-5700:
-----------------------------------
BTW, adding quotes to the 2L also works, so this might be a problem with the dialect I suppose.
> 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
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
More information about the hibernate-issues
mailing list