title: subquery and aliases with criteria api
body: <p>My question is rather simple: I have this query:</p>
  
  <pre><code> SELECT * FROM TABLE t WHERE 
      (SELECT count(*) FROM TABLE tbis WHERE  TRUNC(t.date) = TRUNC(tbis.date))&gt;1 ;
  </code></pre>
  
  <p>the field date is a timestamp.</p>
  
  <p>How do I do that with the criteria API?</p>
  
  <p>I have something like this (trunc() is valid with my db server, the sql request works.):</p>
  
  <pre><code>Criteria crit = session.createCriteria(Myclass.class, "t");
  crit.createAlias("t.date", "dateT");
  
  DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");
  subcrit.createAlias("tbis.date", "dateTbis");
  
  subcrit.add(Restrictions.sqlRestriction("TRUNC(dateT) = TRUNC(dateTbis)"));
  
  subcrit.setProjection(Projections.count("id"));
  
  crit .add(Subqueries.gt(1, subcrit));
  return crit.list();
  </code></pre>
  
  <p>Somehow in the log, i got:
  org.hibernate.QueryException: not an association: date</p>
  
  <p>I tried various things, but couldn't get it to work...</p>
  
  <p>I tried: </p>
  
  <pre><code>Criteria crit = session.createCriteria(Myclass.class, "t");
  
  DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");
  
  subcrit.add(Restrictions.sqlRestriction("TRUNC(t.date) = TRUNC(tbis.date)"));
  
  subcrit.setProjection(Projections.count("id"));
  
  crit .add(Subqueries.gt(1, subcrit));
  
  return crit.list();
  </code></pre>
  
  <p>and the SQL condition generated was:</p>
  
  <pre><code>TRUNC(t.date) = TRUNC(tbis.date))
  </code></pre>
  
  <p>with the log:</p>
  
  <pre><code>18:34:04,461 WARN  [JDBCExceptionReporter] SQL Error: 904, SQLState: 42000
  18:34:04,461 ERROR [JDBCExceptionReporter] ORA-00904: "T"."DATE": invalid identifier
  
  18:34:04,461 WARN  [CriteriaAdapter] list exception: 
  org.hibernate.exception.SQLGrammarException: could not execute query
  </code></pre>
  
  <p>Also</p>
  
  <pre><code>Criteria crit = session.createCriteria(Myclass.class, "t");
  
  DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");
  
  subcrit.add(Restrictions.sqlRestriction("TRUNC({t}.date) = TRUNC({tbis}.date)"));
  
  subcrit.setProjection(Projections.count("id"));
  
  crit .add(Subqueries.gt(1, subcrit));
  
  return crit.list();
  </code></pre>
  
  <p>with the condition becoming:</p>
  
  <pre><code>TRUNC({t}.date) = TRUNC({tbis}.date))
  </code></pre>
  
  <p>and the logs:</p>
  
  <pre><code>18:36:28,206 WARN  [TxConnectionManager] Connection error occured: org.jboss.resource.connectionmanager.TxConnectionManager$TxConnectionEventListener@1b3febd3[state=NORMAL mc=org.jboss.resource.adapter.jdbc.xa.XAManagedConnection@4cb16baf handles=1 lastUse=1343284588160 permit=true trackByTx=true mcp=org.jboss.resource.connectionmanager.JBossManagedConnectionPool$OnePool@7f16f514 context=org.jboss.resource.connectionmanager.InternalManagedConnectionPool@3c34353b xaResource=org.jboss.resource.adapter.jdbc.xa.XAManagedConnection@4cb16baf txSync=null]
  java.lang.NullPointerException
  at oracle.jdbc.driver.T4C8Oall.getNumRows(T4C8Oall.java:1153)
  [...]
  at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
  </code></pre>
  
  <p>Or even: </p>
  
  <pre><code>Criteria crit = session.createCriteria(Myclass.class, "t");
  
  DetachedCriteria subcrit = DetachedCriteria.forclass(MyClass.class, "tbis");
  
  subcrit.add(Restrictions.sqlRestriction("TRUNC({t.date}) = TRUNC({tbis.date})"));
  
  subcrit.setProjection(Projections.count("id"));
  
  crit .add(Subqueries.gt(1, subcrit));
  
  return crit.list();
  </code></pre>
  
  <p>With the same result as previously.</p>
  
  <p>I think criteria is unable to detect the alias of the subquery... But I have to access  that value, any workaround?</p>