EntityQuery fails on getResultCount with composite primary keys on Oracle

[typhoon1978]

Hi my friends, I'm trying to interact with an Oracle enterprise database through Seam, but I'm encountering lot of problems.
My development platform is based on Eclipse 3.4.2 / JBoss Tools / Seam 2.1.0-GA / JBoss AS 5.1.0-GA.
I try to explain the problem in detail, giving you all the components.

Here it is the table definition:

create table WV_DWH.WV0514A_FILIALE
(
  SQ_FILIALE_ID NUMBER not null,
  DT_REFERENCE  DATE not null,
  CD_FILIALE    VARCHAR2(100) not null,
  DS_FILIALE    VARCHAR2(400),
  SQ_RDT_ID     NUMBER not null,
  CD_CAB        VARCHAR2(6),
  CD_ABI        VARCHAR2(5),
  SQ_BANCA_ID   NUMBER
)

alter table WV_DWH.WV0514A_FILIALE
  add constraint WV0514A_PK primary key (DT_REFERENCE, SQ_FILIALE_ID)
  using index 
  tablespace WVU_STAGE_INDX_S0
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 128K
    next 128K
    minextents 1
    maxextents unlimited
    pctincrease 0
  );

So we have a table with a composite primary key made up by a REFERENCE DATE and an ID.

The Seam Generate Entities Eclipse mapping function creates two entity classes made up by an @Embeddable class (the ID) in which we have declared:
- Date dtReference
- BigDecimal sqFilialeId
and the table class in which we have declared:
- Wv0514aFilialeId id
- String cdFiliale
- String dsFiliale
- BigDecimal sqRdtId
- String cdCab
- String cdAbi
- BigDecimal sqBancaId

Here the classes definition:

package it.cogmps.massivesim.entity;

// Generated 6-lug-2009 15.46.51 by Hibernate Tools 3.2.4.GA

import java.math.BigDecimal;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Embeddable;
import javax.persistence.Temporal;
import javax.persistence.TemporalType;

import org.hibernate.validator.NotNull;

/**
 * Wv0514aFilialeId generated by hbm2java
 */
@Embeddable
public class Wv0514aFilialeId implements java.io.Serializable {

     private static final long serialVersionUID = -1437555899840893475L;
     private Date dtReference;
     private BigDecimal sqFilialeId;

     public Wv0514aFilialeId() {
     }

     public Wv0514aFilialeId(Date dtReference, BigDecimal sqFilialeId) {
          this.dtReference = dtReference;
          this.sqFilialeId = sqFilialeId;
     }

     @Temporal(TemporalType.DATE)
     @Column(name = "DT_REFERENCE", nullable = false, length = 7)
     @NotNull
     public Date getDtReference() {
          return this.dtReference;
     }

     public void setDtReference(Date dtReference) {
          this.dtReference = dtReference;
     }

     @Column(name = "SQ_FILIALE_ID", nullable = false, precision = 22, scale = 0)
     @NotNull
     public BigDecimal getSqFilialeId() {
          return this.sqFilialeId;
     }

     public void setSqFilialeId(BigDecimal sqFilialeId) {
          this.sqFilialeId = sqFilialeId;
     }

     public boolean equals(Object other) {
          if ((this == other))
               return true;
          if ((other == null))
               return false;
          if (!(other instanceof Wv0514aFilialeId))
               return false;
          Wv0514aFilialeId castOther = (Wv0514aFilialeId) other;

          return ((this.getDtReference() == castOther.getDtReference()) || (this
                    .getDtReference() != null
                    && castOther.getDtReference() != null && this.getDtReference()
                    .equals(castOther.getDtReference())))
                    && ((this.getSqFilialeId() == castOther.getSqFilialeId()) || (this
                              .getSqFilialeId() != null
                              && castOther.getSqFilialeId() != null && this
                              .getSqFilialeId().equals(castOther.getSqFilialeId())));
     }

     public int hashCode() {
          int result = 17;

          result = 37
                    * result
                    + (getDtReference() == null ? 0 : this.getDtReference()
                              .hashCode());
          result = 37
                    * result
                    + (getSqFilialeId() == null ? 0 : this.getSqFilialeId()
                              .hashCode());
          return result;
     }

}

package it.cogmps.massivesim.entity;

// Generated 6-lug-2009 15.46.51 by Hibernate Tools 3.2.4.GA

import java.math.BigDecimal;
import javax.persistence.AttributeOverride;
import javax.persistence.AttributeOverrides;
import javax.persistence.Column;
import javax.persistence.EmbeddedId;
import javax.persistence.Entity;
import javax.persistence.Table;
import org.hibernate.validator.Length;
import org.hibernate.validator.NotNull;

/**
 * Wv0514aFiliale generated by hbm2java
 */
@Entity
@Table(name = "WV0514A_FILIALE", schema="WV_DWH")
public class Wv0514aFiliale implements java.io.Serializable {

     private static final long serialVersionUID = 349405820102500746L;
     private Wv0514aFilialeId id;
     private String cdFiliale;
     private String dsFiliale;
     private BigDecimal sqRdtId;
     private String cdCab;
     private String cdAbi;
     private BigDecimal sqBancaId;

     public Wv0514aFiliale() {
     }

     public Wv0514aFiliale(Wv0514aFilialeId id, String cdFiliale,
               BigDecimal sqRdtId) {
          this.id = id;
          this.cdFiliale = cdFiliale;
          this.sqRdtId = sqRdtId;
     }

     public Wv0514aFiliale(Wv0514aFilialeId id, String cdFiliale,
               String dsFiliale, BigDecimal sqRdtId, String cdCab, String cdAbi,
               BigDecimal sqBancaId) {
          this.id = id;
          this.cdFiliale = cdFiliale;
          this.dsFiliale = dsFiliale;
          this.sqRdtId = sqRdtId;
          this.cdCab = cdCab;
          this.cdAbi = cdAbi;
          this.sqBancaId = sqBancaId;
     }

     @EmbeddedId
     @AttributeOverrides( {
               @AttributeOverride(name = "dtReference", column = @Column(name = "DT_REFERENCE", nullable = false, length = 7)),
               @AttributeOverride(name = "sqFilialeId", column = @Column(name = "SQ_FILIALE_ID", nullable = false, precision = 22, scale = 0)) })
     @NotNull
     public Wv0514aFilialeId getId() {
          return this.id;
     }

     public void setId(Wv0514aFilialeId id) {
          this.id = id;
     }

     @Column(name = "CD_FILIALE", nullable = false, length = 100)
     @NotNull
     @Length(max = 100)
     public String getCdFiliale() {
          return this.cdFiliale;
     }

     public void setCdFiliale(String cdFiliale) {
          this.cdFiliale = cdFiliale;
     }

     @Column(name = "DS_FILIALE", length = 400)
     @Length(max = 400)
     public String getDsFiliale() {
          return this.dsFiliale;
     }

     public void setDsFiliale(String dsFiliale) {
          this.dsFiliale = dsFiliale;
     }

     @Column(name = "SQ_RDT_ID", nullable = false, precision = 22, scale = 0)
     @NotNull
     public BigDecimal getSqRdtId() {
          return this.sqRdtId;
     }

     public void setSqRdtId(BigDecimal sqRdtId) {
          this.sqRdtId = sqRdtId;
     }

     @Column(name = "CD_CAB", length = 6)
     @Length(max = 6)
     public String getCdCab() {
          return this.cdCab;
     }

     public void setCdCab(String cdCab) {
          this.cdCab = cdCab;
     }

     @Column(name = "CD_ABI", length = 5)
     @Length(max = 5)
     public String getCdAbi() {
          return this.cdAbi;
     }

     public void setCdAbi(String cdAbi) {
          this.cdAbi = cdAbi;
     }

     @Column(name = "SQ_BANCA_ID", precision = 22, scale = 0)
     public BigDecimal getSqBancaId() {
          return this.sqBancaId;
     }

     public void setSqBancaId(BigDecimal sqBancaId) {
          this.sqBancaId = sqBancaId;
     }

}

The seam generator creates also a wonderful UI to interact with the database. So now I'm ready to deploy the project to JBoss and browse my data through the UI, but it's here that I encounter the first problems...

The data browsing is perfect for the tables without a composite primary key, but not for the objects described above...

I saw the SQL queries in standard output written by

<property name="hibernate.show_sql" value="true"/>

setting in the persistence.xml configuration file.
I noticed that it tryies to make pagination through a couple of queries:

15:11:01,604 INFO  [STDOUT] Hibernate: 
    select
        * 
    from
        ( select
            wv0514afil0_.DT_REFERENCE as DT1_50_,
            wv0514afil0_.SQ_FILIALE_ID as SQ2_50_,
            wv0514afil0_.CD_ABI as CD3_50_,
            wv0514afil0_.CD_CAB as CD4_50_,
            wv0514afil0_.CD_FILIALE as CD5_50_,
            wv0514afil0_.DS_FILIALE as DS6_50_,
            wv0514afil0_.SQ_BANCA_ID as SQ7_50_,
            wv0514afil0_.SQ_RDT_ID as SQ8_50_ 
        from
            WV_DWH.WV0514A_FILIALE wv0514afil0_ ) 
    where
        rownum <= ?

15:11:01,792 INFO  [STDOUT] Hibernate: 
    select
        * 
    from
        ( select
            count((wv0514afil0_.DT_REFERENCE,
            wv0514afil0_.SQ_FILIALE_ID)) as col_0_0_ 
        from
            WV_DWH.WV0514A_FILIALE wv0514afil0_ ) 
    where
        rownum <= ?

I think that in the second query it's trying to count how many distinct primary objects are there, but the query is wrong...
The

count((wv0514afil0_.DT_REFERENCE,
            wv0514afil0_.SQ_FILIALE_ID))

statement has a bad syntax and so I've got the exception...

15:11:01,885 WARN  [JDBCExceptionReporter] SQL Error: 907, SQLState: 42000
15:11:01,885 ERROR [JDBCExceptionReporter] ORA-00907: missing right parenthesis

Here it is the full stack trace:

Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
     at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:614)
     at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:128)
     at org.jboss.seam.framework.EntityQuery.initResultCount(EntityQuery.java:135)
     at org.jboss.seam.framework.EntityQuery.getResultCount(EntityQuery.java:126)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at org.jboss.seam.util.Reflections.invoke(Reflections.java:22)
     at org.jboss.seam.intercept.RootInvocationContext.proceed(RootInvocationContext.java:32)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:56)
     at org.jboss.seam.transaction.RollbackInterceptor.aroundInvoke(RollbackInterceptor.java:28)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.transaction.TransactionInterceptor$1.work(TransactionInterceptor.java:97)
     at org.jboss.seam.util.Work.workInTransaction(Work.java:47)
     at org.jboss.seam.transaction.TransactionInterceptor.aroundInvoke(TransactionInterceptor.java:91)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.core.MethodContextInterceptor.aroundInvoke(MethodContextInterceptor.java:44)
     at org.jboss.seam.intercept.SeamInvocationContext.proceed(SeamInvocationContext.java:68)
     at org.jboss.seam.intercept.RootInterceptor.invoke(RootInterceptor.java:107)
     at org.jboss.seam.intercept.JavaBeanInterceptor.interceptInvocation(JavaBeanInterceptor.java:185)
     at org.jboss.seam.intercept.JavaBeanInterceptor.invoke(JavaBeanInterceptor.java:103)
     at it.cogmps.massivesim.session.Wv0514aFilialeList_$$_javassist_seam_3.getResultCount(Wv0514aFilialeList_$$_javassist_seam_3.java)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
     at java.lang.reflect.Method.invoke(Method.java:597)
     at javax.el.BeanELResolver.getValue(BeanELResolver.java:62)
     at javax.el.CompositeELResolver.getValue(CompositeELResolver.java:54)
     at com.sun.faces.el.FacesCompositeELResolver.getValue(FacesCompositeELResolver.java:72)
     at org.jboss.el.parser.AstPropertySuffix.getValue(AstPropertySuffix.java:53)
     at org.jboss.el.parser.AstValue.getValue(AstValue.java:67)
     at org.jboss.el.parser.AstChoice.getValue(AstChoice.java:29)
     at org.jboss.el.parser.AstChoice.getValue(AstChoice.java:29)
     at org.jboss.el.ValueExpressionImpl.getValue(ValueExpressionImpl.java:186)
     at com.sun.facelets.el.ELText$ELTextVariable.writeText(ELText.java:184)
     at com.sun.facelets.el.ELText$ELTextComposite.writeText(ELText.java:108)
     at com.sun.facelets.compiler.TextInstruction.write(TextInstruction.java:45)
     ... 58 more
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
     at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:90)
     at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
     at org.hibernate.loader.Loader.doList(Loader.java:2231)
     at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2125)
     at org.hibernate.loader.Loader.list(Loader.java:2120)
     at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:401)
     at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:361)
     at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
     at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1148)
     at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
     at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:88)
     ... 94 more
Caused by: java.sql.SQLException: ORA-00907: missing right parenthesis

     at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:145)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
     at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
     at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)
     at oracle.jdbc.driver.T4CPreparedStatement.doOall8(T4CPreparedStatement.java:216)
     at oracle.jdbc.driver.T4CPreparedStatement.executeForDescribe(T4CPreparedStatement.java:922)
     at oracle.jdbc.driver.OracleStatement.executeMaybeDescribe(OracleStatement.java:1122)
     at oracle.jdbc.driver.T4CPreparedStatement.executeMaybeDescribe(T4CPreparedStatement.java:973)
     at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1244)
     at oracle.jdbc.driver.OraclePreparedStatement.executeInternal(OraclePreparedStatement.java:3469)
     at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:3514)
     at org.jboss.resource.adapter.jdbc.WrappedPreparedStatement.executeQuery(WrappedPreparedStatement.java:342)
     at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
     at org.hibernate.loader.Loader.getResultSet(Loader.java:1808)
     at org.hibernate.loader.Loader.doQuery(Loader.java:697)
     at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
     at org.hibernate.loader.Loader.doList(Loader.java:2228)
     ... 102 more

Please help me,
Thanks in advance.

Alex

[taccart.thierry.accart.name]

Hi

I think that it's not Sean, but hibernate who's guily : see bug HSEARCH-306...

We're using Microsoft SQLServer and I just discovered the same issue as yours :
the ejbql

select count(userRole) from UserRole userRole

is sent to sql as

declare @P1 int
set @P1=NULL
declare @P2 int
set @P2=0
declare @P3 int
set @P3=0
exec sp_cursorprepexec @P1 output, @P2 output, NULL, N'select count((userrole0_.roleId, userrole0_.userId)) as col_0_0_ from TP_UM_UserRole userrole0_', 16, 8193, @P3 output
select @P1, @P2, @P3

and SQLServer does not understand the part

  count((userrole0_.roleId, userrole0_.userId)).

That's too bad, because a simple

   select count(*) from TP_UM_UserRole 

would work fine. Only index would be scanned I think.

[taccart.thierry.accart.name]

Just created http://opensource.atlassian.com/projects/hibernate/browse/HHH-4044

[typhoon1978]

Hi my friend, I think the problem is in org.jboss.seam.framework.EntityQuery. I solved the problem subclassing it and overriding the method getCountEjbql in the following way:

package demo.session;

import java.util.regex.Matcher;
import java.util.regex.Pattern;

import org.jboss.seam.framework.EntityQuery;

public class OracleCompliantEntityQuery<T> extends EntityQuery<T> {

     private static final long serialVersionUID = -8919405191396687051L;

     private static final Pattern FROM_PATTERN = Pattern.compile(
               "(^|\\s)(from)\\s", 2);
     private static final Pattern WHERE_PATTERN = Pattern.compile(
               "\\s(where)\\s", 2);
     private static final Pattern ORDER_PATTERN = Pattern.compile(
               "\\s(order)(\\s)+by\\s", 2);

     @Override
     protected String getCountEjbql() {
          String ejbql = getRenderedEjbql();
          Matcher fromMatcher = FROM_PATTERN.matcher(ejbql);
          if (!fromMatcher.find()) {
               throw new IllegalArgumentException("no from clause found in query");
          } else {
               int fromLoc = fromMatcher.start(2);
               Matcher orderMatcher = ORDER_PATTERN.matcher(ejbql);
               int orderLoc = orderMatcher.find() ? orderMatcher.start(1) : ejbql
                         .length();
               Matcher whereMatcher = WHERE_PATTERN.matcher(ejbql);
               int whereLoc = whereMatcher.find() ? whereMatcher.start(1)
                         : orderLoc;
               String subject = "*";
               return (new StringBuilder(ejbql.length() + 15)).append(
                         "select count(").append(subject).append(") ").append(
                         ejbql.substring(fromLoc, whereLoc).replace("join fetch",
                                   "join"))
                         .append(ejbql.substring(whereLoc, orderLoc)).toString()
                         .trim();
          }
     }

}

package demo.session;

import demo.entity.User;

import java.util.Arrays;

import org.jboss.seam.annotations.Name;
import org.jboss.seam.framework.EntityQuery;

@Name("userList")
public class UserList extends EntityQuery<User> {

     private static final String EJBQL = "select user from User user";

     private static final String[] RESTRICTIONS = {
               "lower(user.txUser) like lower(concat(#{userList.user.txUser},'%'))",
               "lower(user.dsUser) like lower(concat(#{userList.user.dsUser},'%'))",
               "lower(user.txPassword) like lower(concat(#{userList.user.txPassword},'%'))", };

     private User user = new User();

     public UserList() {
          setEjbql(EJBQL);
          setRestrictionExpressionStrings(Arrays.asList(RESTRICTIONS));
          setMaxResults(25);
     }

     public User getUser() {
          return user;
     }
}

I don't know if my subclassing is correct and complete, but extending my OracleCompliantEntityQuery instead of EntityQuery it runs correctly now...

Now I correctly have:

select count(*) from user

instead of the count on the composite primary keys fields :-D
Let me know what you think about this solution.

Thanks in advance,
Alex.

[taccart.thierry.accart.name]

your workaround seems correct... as a workaround, but any time you (anybody) run a select count (entity) from Entity entity, the problem will be there.

The fact is that ejbql is not correctly transformed in native query language.
It's a problem at least for MSSQL, but also for http://opensource.atlassian.com/projects/hibernate/browse/HHH-3096.

Rgds

[radar.marco.detoni.nextep.it]

Hello,
I have a similar issue with firebird 2.1, the syntax    
select first ? count((column1, column2, column3))
raises an exception in firebird:
Caused by: org.firebirdsql.jdbc.FBSQLException: GDS Exception. 335544569. Dynamic SQL Error
SQL error code = -104
Token unknown - line 1, column 48
,

Regards

[exuisitus]

I know, the thread is a little bit old, but maybe my contribution is helpful to other persons.

Try this override of the getCountEjbql(), which is less verbose:

/* 
 * Set the wild card flag to true (which is obviously set to false when
 * you use jpa compliant persistence. This flag must be true, if a composite
 * primary key is used by the entity the list consists of. If the primary
 * key is a simple key, this override is not necessary.
 *
 * The documentation in the seam source says, the use of the wild card (*) in a count
 * query is not jpa compliant, so I'm not sure, if it works  with a persistence 
 * provider other than Hibernate.
 */
        @Override
     protected String getCountEjbql() {
          setUseWildcardAsCountQuerySubject(true);
          return super.getCountEjbql();                    
     }

Hope, it helps.