[Hibernate-JIRA] Created: (EJB-376) EntityManager.createQuery does not recognize COUNT CASE statement
by Mark Plutowski (JIRA)
EntityManager.createQuery does not recognize COUNT CASE statement
-----------------------------------------------------------------
Key: EJB-376
URL: http://opensource.atlassian.com/projects/hibernate/browse/EJB-376
Project: Hibernate Entity Manager
Issue Type: Bug
Components: EntityManager
Affects Versions: 3.4.0.CR2, 3.3.1.GA
Environment: Tested with both Hibernate 3.2.5.ga and Hibernate 3.3.0.CR2, Postgres 8.1, Postgres 8.2, Linux Ubuntu.
Reporter: Mark Plutowski
I searched the Hibernate forums and did not find mention of this issue or one that was closely related. I also posted this to the Hibernate Users forum, and did not receive any replies recognizing the bug or stating that it has been resolved or has a workaround.
To summarize, EntityManager.createQuery does not recognize SQL related to applying a COUNT to a CASE, whereas it does correctly run SQL that applies a SUM to a CASE. More detail follows.
--- Summary Overview ---
EntityManager.createQuery runs a SELECT query containing the following HQL/JQL snippet runs correctly:
" SUM ( CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN 1 ELSE 0 END ) "
However, the following do not work:
" COUNT ( DISTINCT CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END ) "
" COUNT ( DISTINCT (CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END ) ) "
" COUNT ( CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END ) "
The first two statements run and return correct results when translated to SQL and run against the Postgresql database in PgAdmin.
Here's the exception thrown when using COUNT ( DISTINCT CASE WHEN
101976 [main] ERROR org.hibernate.hql.PARSER - line 1:93: unexpected token: CASE
101977 [main] ERROR org.hibernate.hql.PARSER - line 1:98: unexpected token: WHEN
Here's the exception thrown when the extra parenthesis is added to give COUNT ( DISTINCT (CASE WHEN
85587 [main] ERROR org.hibernate.hql.PARSER - line 1:93: unexpected token: (
85588 [main] ERROR org.hibernate.hql.PARSER - line 1:99: unexpected token: WHEN
--- Additional Detail ---
Here is the complete SQL for the COUNT (DISTINCT (CASE use case:
String queryString = "Select c.id.pubId, c.id.clipId, c.duration, " +
"count(distinct s.slotId) , " +
" COUNT ( DISTINCT (CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN cs.componentId ELSE NULL END ) ) " +
"from CmClip c " +
"join c.cmClipSlots s " +
"left outer join c.csComponents cs " +
"where (s.adSizeX * c.width) >= :adWidth " +
"and (s.adSizeY * c.height) >= :adHeight " +
"and (c.isQaDone = true or c.isQaDone is null) " +
"and c.isEnabled = true " +
"group by c.id.pubId, c.id.clipId, c.duration " +
"order by " + orderByCol;
This results in the following exception:
85587 [main] ERROR org.hibernate.hql.PARSER - line 1:93: unexpected token: (
85588 [main] ERROR org.hibernate.hql.PARSER - line 1:99: unexpected token: WHEN
Here is the SQL for the SUM CASE sql snippet that does run and return correct results:
String queryString = "Select c.id.pubId, c.id.clipId, c.duration, " +
"count(distinct s.slotId) , " +
" SUM ( CASE WHEN (cs.serveTs BETWEEN :beginTs AND :endTs ) THEN 1 ELSE 0 END ) " +
"from CmClip c " +
"join c.cmClipSlots s " +
"left outer join c.csComponents cs " +
"where (s.adSizeX * c.width) >= :adWidth " +
"and (s.adSizeY * c.height) >= :adHeight " +
"and (c.isQaDone = true or c.isQaDone is null) " +
"and c.isEnabled = true " +
"group by c.id.pubId, c.id.clipId, c.duration " +
"order by " + orderByCol;
Here is the code snippet that performs the transaction:
float roFactor = (float) 0.5;
transactionMgr.begin();
List<Object[]> result = entityMgr.createQuery(queryString)
.setParameter("adWidth", (widthF-roFactor)).
setParameter("adHeight", (heightF-roFactor))
.setParameter("beginTs", beginTs, javax.persistence.TemporalType.TIMESTAMP)
.setParameter("endTs", endTs, javax.persistence.TemporalType.TIMESTAMP)
.getResultList();
transactionMgr.commit();
The fact that "SUM (CASE" is parsed and run correctly, whereas "COUNT (CASE" throws an exception seems to indicate a bug. I replicated this using both (Hibernate 3.2.5.ga with EntityManager 3.3.1.GA) and (Hibernate 3.3.0.CR2 with EntityManager 3.4.0.CR2).
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (HHH-2951) Restrictions.eq when passed null, should create a NullRestriction
by David Sheldon (JIRA)
Restrictions.eq when passed null, should create a NullRestriction
-----------------------------------------------------------------
Key: HHH-2951
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2951
Project: Hibernate3
Issue Type: Improvement
Components: query-criteria
Affects Versions: 3.2.1
Reporter: David Sheldon
Priority: Trivial
If you create a Restriction using eq, and pass null to it, then the generated SQL is "where foo = null", which always returns false.
The programmer will almost certainly have intended to get "where foo is null".
Ideally, we could change:
public static SimpleExpression eq(String propertyName, Object value) {
return new SimpleExpression(propertyName, value, "=");
}
to
public static SimpleExpression eq(String propertyName, Object value) {
if (value == null) {
return isNull(propertyName);
}
else {
return new SimpleExpression(propertyName, value, "=");
}
}
Unforunately that wont work, as isNull doesn't return a SimpleExpression.
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (HHH-3238) impossible to get the rowcount for a criteria that has projections
by hbMailingList (JIRA)
impossible to get the rowcount for a criteria that has projections
------------------------------------------------------------------
Key: HHH-3238
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3238
Project: Hibernate3
Issue Type: Bug
Affects Versions: 3.2.6
Environment: hibernate 3.2.6, oracle 10g
Reporter: hbMailingList
Projections.rowCount correctly returns the NUMBER_OF_ROWS for the criteria's resultset.
However if the criteria already has a projection then rowcount no longer returns the NUMBER_OF_ROWS in the result set.
For example:
Criteria c=session.CreateCriteria(Cat.class);
ProjectionList pl = ...;
....
pl.add(Projections.groupProperty("color"));
pl.add(Projections.rowCount());
c.setProjections(pl);
c.list();
will result in the following query:
select color, count(*) from cats group by color
I would expect it to result in:
select count(*) from (select color from cats group by color)
after all if I wanted the count by color I could have simply done:
Projections.count(...)
I really need this feature for my project...if you don't think this will make the release, would you please give me a hint and I'll submit a patch.
Thanks for all the hard work.
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (HHH-2218) java.util.ConcurrentModificationException at org.hibernate.pretty.Printer.toString(Printer.java:90)
by rm (JIRA)
java.util.ConcurrentModificationException at org.hibernate.pretty.Printer.toString(Printer.java:90)
---------------------------------------------------------------------------------------------------
Key: HHH-2218
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2218
Project: Hibernate3
Type: Bug
Components: core
Versions: 3.1.3
Environment: Hibernate 3.1.3, spring 2.1.8 on Jboss 4.0.2 with Oracle 10g
Reporter: rm
Similar issues have been logged since May 2005 and have been marked as 'Fixed' by Gavin especially for Session.toString() related cases. However, I am also facing similar issues in Printer.toString()...
The saving grace is that when I set the debug level to INFO, the problem goes away...
Am copy/pasting the relevent stacktrace...I have also debugged this on eclipse and the exact location is the call to "new Printer(....)" in AbstractFlushingEventListener.java
java.util.ConcurrentModificationException
10:07:28,421 INFO [STDOUT] at java.util.HashMap$HashIterator.nextEntry(HashMap.java:787)
10:07:28,421 INFO [STDOUT] at java.util.HashMap$ValueIterator.next(HashMap.java:817)
10:07:28,421 INFO [STDOUT] at org.hibernate.pretty.Printer.toString(Printer.java:90)
10:07:28,421 INFO [STDOUT] at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:97)
10:07:28,421 INFO [STDOUT] at org.hibernate.event.def.DefaultAutoFlushEventListener.onAutoFlush(DefaultAutoFlushEventListener.java:35)
10:07:28,421 INFO [STDOUT] at org.hibernate.impl.SessionImpl.autoFlushIfRequired(SessionImpl.java:954)
10:07:28,421 INFO [STDOUT] at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1099)
10:07:28,421 INFO [STDOUT] at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
PS: This is my first attempt at logging issues at JIRA. Kindly point out any deficiencies or irregularities in this attempt.
--
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (ANN-784) MapBinder.createFormulatedValue() does not honor DB schema name when creating query
by Sven Panko (JIRA)
MapBinder.createFormulatedValue() does not honor DB schema name when creating query
-----------------------------------------------------------------------------------
Key: ANN-784
URL: http://opensource.atlassian.com/projects/hibernate/browse/ANN-784
Project: Hibernate Annotations
Issue Type: Bug
Components: binder
Affects Versions: 3.4.0.GA
Environment: Hibernate 3.3.0.SP1, PostgresQL 8.3
Reporter: Sven Panko
I detected a problem with a ManyToMany mapping using a Map in the following setup:
@Entity
@Table(name = "entitya", schema = "myschema")
class EntityA {
...
@ManyToMany(cascade = { CascadeType.PERSIST, CascadeType.MERGE })
@MapKey(name = "identifier")
@JoinTable(name = "entitya_entityb", schema="myschema", joinColumns = @JoinColumn(name = "entitya_pk"), inverseJoinColumns = @JoinColumn(name = "entityb_pk"))
@Cascade( { org.hibernate.annotations.CascadeType.SAVE_UPDATE, org.hibernate.annotations.CascadeType.DELETE_ORPHAN })
@Cache(usage = CacheConcurrencyStrategy.READ_WRITE)
private Map<String, EntityB> entityBMap = new HashMap<String, EntityB>();
}
@Entity
@Table(name = "entityb", schema = "myschema")
class EntityB {
...
}
The SQL generated to load the entityBMap collection contains a subquery in the select clause, which looks like this:
select a8.identifier from entityb a8 where a8.pk=entityb0_.entityb_pk (entityb0_.entityb_pk comes from the outer select clause)
The problem here is, that entityb in the from clause is not prepended by the schema name "myschema" (the outer query is correctly doing this in the join clause, however). I tracked this down to the createFormulatedValue() method in the MapBinder class; the responsible code snippet is contained in lines 292 - 296:
StringBuilder fromAndWhereSb = new StringBuilder( " from " )
.append( associatedClass.getTable().getName() )
//.append(" as ") //Oracle doesn't support it in subqueries
.append( " " )
.append( alias ).append( " where " );
associatedClass.getTable().getName() does not return the fully qualified table name, so I think this should look something like this:
StringBuilder fromAndWhereSb = new StringBuilder( " from " );
if (associatedClass.getTable().getSchema() != null) {
fromAndWhereSb.append(associatedClass.getTable().getSchema()).append('.');
}
fromAndWhereSb.append( associatedClass.getTable().getName() )
//.append(" as ") //Oracle doesn't support it in subqueries
.append( " " )
.append( alias ).append( " where " );
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (HHH-3502) getIdentifierMethod of BasicLazyInitializer does not match if method invoked through an interface with different return type
by Sean Bridges (JIRA)
getIdentifierMethod of BasicLazyInitializer does not match if method invoked through an interface with different return type
----------------------------------------------------------------------------------------------------------------------------
Key: HHH-3502
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3502
Project: Hibernate Core
Issue Type: Bug
Affects Versions: 3.3.1
Reporter: Sean Bridges
Priority: Minor
I have an entity interface,
public interface IEntity {
public Object getId();
}
and I have an entity, say User that implements this interface with an id method
@Id
public Long getId() {
return id;
}
I can call getId() on an uninitialized proxy with,
user.getId()
however, if I call getId() in this way,
((IEntity) user)).getId()
I get org.hibernate.LazyInitializationException: could not initialize proxy - no Session
Looking at the code, BasicLazyInitializer has these lines,
else if ( isUninitialized() && method.equals(getIdentifierMethod) ) {
return getIdentifier();
}
the method.equals(getIdentifierMethod) returns false. The methods are not equal() because they have different return types (this is allowed in 1.5 with covariant return types).
Hibernate should not use Method.equals(), but should instead use the code below (modified from Method.equals(Object)),
public boolean equalsIgnoringReturnType(Method m1, Method m2) {
if ((m1.getDeclaringClass() != m2.getDeclaringClass()) {
return false;
}
if (m1.getName() != m2.getName()) {
return false;
}
Class[] params1 = m1.getParameterTypes();
Class[] params2 = m2.getParameterTypes();
if (params1.length != params2.length) {
return false;
}
for (int i = 0; i < params1.length; i++) {
if (params1[i] != params2[i])
return false;
}
return true;
}
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (HHH-2744) QueryException raised for valid SQL query - problems with aliases parsing
by Michal Jastak (JIRA)
QueryException raised for valid SQL query - problems with aliases parsing
-------------------------------------------------------------------------
Key: HHH-2744
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2744
Project: Hibernate3
Issue Type: Bug
Affects Versions: 3.2.1
Environment: Tomcat 5.5, Hibernate Annotations 3.3.0 ga
Reporter: Michal Jastak
SQLQuery parser dose not parse queries correctly.
Trying to invoke:
session.createSQLQuery("select foo from bar where foo like '{%'");
ends with:
rg.hibernate.QueryException: Unmatched braces for alias path [select foo from bar where foo like '{%']
at org.hibernate.loader.custom.sql.SQLQueryParser.substituteBrackets(SQLQueryParser.java:74)
at org.hibernate.loader.custom.sql.SQLQueryParser.process(SQLQueryParser.java:51)
at org.hibernate.loader.custom.sql.SQLCustomQuery.<init>(SQLCustomQuery.java:110)
at org.hibernate.engine.query.NativeSQLQueryPlan.<init>(NativeSQLQueryPlan.java:43)
at org.hibernate.engine.query.QueryPlanCache.getNativeSQLQueryPlan(QueryPlanCache.java:114)
at org.hibernate.impl.AbstractSessionImpl.getNativeSQLQueryPlan(AbstractSessionImpl.java:137)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:142)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:152)
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 10 months
[Hibernate-JIRA] Created: (HHH-2031) Add functions to Dialect that can disable use of schema and catalog parts -- for HSQLDB support
by Brian Holmes (JIRA)
Add functions to Dialect that can disable use of schema and catalog parts -- for HSQLDB support
-----------------------------------------------------------------------------------------------
Key: HHH-2031
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2031
Project: Hibernate3
Type: Patch
Components: core
Versions: 3.1.3
Environment: This patch is against r9668 of /tags/v313/Hibernate3.
Reporter: Brian Holmes
Attachments: hibernate3-v313-support_schema_catalog_names.patch
HSQLDB does not support the use of three part names, that is Tables with schema and catalog parts.
This patch adds two functions to Dialect: supportsSchemaNames() and supportsCatalogNames(). Table.getQualifiedName() checks these values to decide whether to use or ignore the schema and catalog parts.
By default these are enabled in Dialect. This patch only disables their use in HSQLDialect.
This change is necessary for people who normally use Schema and Catalog names for access their primary database, but also use HSQLDB for Unit Testing. Without it, HSQLDB is unusable for Unit Testing because it fails on use of any catalog or schema names.
--
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....
-
For more information on JIRA, see:
http://www.atlassian.com/software/jira
11 years, 11 months
[Hibernate-JIRA] Created: (HHH-3644) Add support for "WITH UR" isolation clause on DB2
by Ricardo Fernandes (JIRA)
Add support for "WITH UR" isolation clause on DB2
-------------------------------------------------
Key: HHH-3644
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-3644
Project: Hibernate Core
Issue Type: Sub-task
Components: core
Affects Versions: 3.3.1
Environment: HIbernate 3.3.1 / DB2 9.x
Reporter: Ricardo Fernandes
The question is basically the same as Steve's, i.e. to force DB2 to use as few locks as possible on heavy load scenarios.
Although some might consider the use of the WITH UR clause a bad approach (since it allows dirty reads), the fact is that there are some cases where this is in fact acceptable, such as computing the total amount of rows of a query (for information purposes) or producing a high-level listing of items with very little detailed info. The bottom line is: if I need to trade performance for extremely accurate data, I surely want to be able to decide when this should happen.
I've already performed the changes that makes it possible to use both the FOR READ ONLY and the WITH UR clauses and I will be submitting a patch shortly so you can have a look at it. The strategy I've used was basically the following:
1. Added two new methods on the Dialect class:
String getDatabaseReadOnlyString(String sql) - for adding the READ ONLY clause
String getDirtyReadsString(String sql) - for adding the WITH UR clause
2. Changed the Query interface in order to allow the user to say whether he/she wants the query to allow dirty reads:
Query setAllowDirtyReads(boolean allowDirtyReads);
3. Added a default implementation on the AbstractQueryImpl which initializes the flag a false
4. Added a similar attribute on the QueryParameters class
5. Changed the prepareQueryStatement() method of the Loader class (just after the useLimit part in order:
a) Ask the dialect for the getDatabaseReadOnlyString() is there are no LockModes set (as did Steve)
b) Ask the dialect for the getDirtyReadsString() is the queyParameters allows dirty reads.
All the tests were well succeeded.
Hope you find these changes, at least, worth looking at.
Best Regards,
Ricardo
--
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....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
11 years, 11 months