Freakish query (SQL works but no entity found)
I have a query that if I take the hibernate generated sql and swap the ?s for the values hibernate bound in (from the log) and exec it gives me a result. Yet hibernate insists there is no result..
The HQL:
String query = "from Folder f where f.parent.id = :fid and upper(f.name) = upper(:fname)";
try {
f = (Folder)session.createQuery(query)
.setParameter("fid",folder.getId())
.setParameter("fname",name)
.getSingleResult();
the generated query with results replaced (if I run it manually I get results)
select folder0_.id as id69_, folder0_.name as name69_, folder0_.parent_id as parent4_69_, folder0_.defaultInFolder_id as defaultI5_69_, folder0_.defaultOutFolder_id as defaultO6_69_, folder0_.TYPE as TYPE69_ from Folder folder0_ where folder0_.parent_id=2 and upper(folder0_.name)=upper('INBOX')
this gives the result:
1 INBOX 2 org.jboss.mail.mailbox.Folder
The log
2006-02-14 16:54:45,114 DEBUG [org.hibernate.engine.query.QueryPlanCache] located HQL query plan in cache (from Folder f where f.parent.id = :fid and upper(f.name) = upper(:fname))2006-02-14 16:54:45,114 DEBUG [org.hibernate.engine.query.QueryPlanCache] located HQL query plan in cache (from Folder f where f.parent.id = :fid and upper(f.name) = upper(:fname))2006-02-14 16:54:45,114 DEBUG [org.hibernate.engine.query.HQLQueryPlan] find: from Folder f where f.parent.id = :fid and upper(f.name) = upper(:fname)2006-02-14 16:54:45,114 DEBUG [org.hibernate.engine.QueryParameters] named parameters: {fname="INBOX", fid=2}2006-02-14 16:54:45,114 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open PreparedStatement (open PreparedStatements: 0, globally: 0)2006-02-14 16:54:45,115 DEBUG [org.hibernate.jdbc.ConnectionManager] opening JDBC connection2006-02-14 16:54:45,116 DEBUG [org.hibernate.SQL] select folder0_.id as id69_, folder0_.name as name69_, folder0_.parent_id as parent4_69_, folder0_.defaultInFolder_id as defaultI5_69_, folder0_.defaultOutFolder_id as defaultO6_69_, folder0_.TYPE as TYPE69_ from Folder folder0_ where folder0_.parent_id=? and upper(folder0_.name)=upper(?)2006-02-14 16:54:45,116 DEBUG [org.hibernate.jdbc.AbstractBatcher] preparing statement2006-02-14 16:54:45,117 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() "INBOX" -> fname [2]2006-02-14 16:54:45,117 DEBUG [org.hibernate.type.StringType] binding '"INBOX"' to parameter: 22006-02-14 16:54:45,117 DEBUG [org.hibernate.loader.hql.QueryLoader] bindNamedParameters() 2 -> fid [1]2006-02-14 16:54:45,117 DEBUG [org.hibernate.type.LongType] binding '2' to parameter: 12006-02-14 16:54:45,117 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to open ResultSet (open ResultSets: 0, globally: 0)2006-02-14 16:54:45,117 DEBUG [org.hibernate.loader.Loader] processing result set2006-02-14 16:54:45,118 DEBUG [org.hibernate.loader.Loader] done processing result set (0 rows)2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close ResultSet (open ResultSets: 1, globally: 1)2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)
2006-02-14 16:54:45,117 DEBUG [org.hibernate.loader.Loader] processing result set2006-02-14 16:54:45,118 DEBUG [org.hibernate.loader.Loader] done processing result set (0 rows)2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close ResultSet (open ResultSets: 1, globally: 1)2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.AbstractBatcher] about to close PreparedStatement (open PreparedStatements: 1, globally: 1)2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.AbstractBatcher] closing statement2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.ConnectionManager] aggressively releasing JDBC connection2006-02-14 16:54:45,118 DEBUG [org.hibernate.jdbc.ConnectionManager] releasing JDBC connection [ (open PreparedStatements: 0, globally: 0) (open ResultSets: 0, globally: 0)]2006-02-14 16:54:45,118 DEBUG [org.hibernate.loader.Loader] total objects hydrated: 02006-02-14 16:54:45,118 DEBUG [org.hibernate.engine.StatefulPersistenceContext] initializing non-lazy collections2006-02-14 16:54:45,118 INFO [STDOUT] javax.persistence.NoResultException: No entity found for query
2006-02-14 16:54:45,121 INFO [STDOUT] at org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:47)
2006-02-14 16:54:45,121 INFO [STDOUT] at org.jboss.mail.mailbox.MailboxServiceImpl.org$jboss$mail$mailbox$MailboxServiceImpl$getSubfolderByName$aop(MailboxServiceImpl.java:494)
2006-02-14 16:54:45,121 INFO [STDOUT] at org.jboss.mail.mailbox.MailboxServiceImpl$getSubfolderByName_1350696378795929061.invokeNext(MailboxServiceImpl$getSubfolderByName_1350696378795929061.java)
Can anyone explain what I must be doing wrong and why there would be a difference? (other than Hypersonic sucks ;-) )
