My correct HQL generates correct SQL but wrong SQL gets executed.
I have this HQL (doesn't look nice, I know):
Code:
select count(*)
from Pripomienka as p
where
1=1 AND
p.autor LIKE :externyAutor_ and
exists (
from Komentar k
where
k.PK = (
select max(k2.PK)
from Komentar k2
where
k2.datumVytvorenia = (
select max(k3.datumVytvorenia)
from Komentar k3
where k3.pripomienka = p) and
k2.pripomienka = p) and
k.datumVytvorenia >= :odoslanieOd and
k.zadavatel.userInfo.externy = 1 )
In the mapping file for both classes Pripomienka and Komentar there is a filter defined:
Code:
<filter name="projekt" condition="Projekt = :projekt"/>
Generated SQL (from Hibernate log) looks OK:
Code:
select count(*) as col_0_0_ from PRIPOMIENKA pripomienk0_ where pripomienk0_.Projekt = ? and 1=1 and (pripomienk0_.AUTOR like ?) and (exists (select komentar1_.PK from KOMENTAR komentar1_, CTUSER ctuser4_, USERINFO userinfo5_ where komentar1_.Projekt = ? and ctuser4_.PK=userinfo5_.CTUSER and komentar1_.ZADAVATEL=ctuser4_.PK and komentar1_.PK=(select max(komentar2_.PK) from KOMENTAR komentar2_ where komentar2_.Projekt = ? and komentar2_.DATUMVYTVORENIA=(select max(komentar3_.DATUMVYTVORENIA) from KOMENTAR komentar3_ where komentar3_.Projekt = ? and komentar3_.PRIPOMIENKA=pripomienk0_.PK) and komentar2_.PRIPOMIENKA=pripomienk0_.PK) and komentar1_.DATUMVYTVORENIA>=? and userinfo5_.EXTERNY=1))
But executing SQL throws org.hibernate.exception.SQLGrammarException caused by java.sql.SQLException "Error converting data type nvarchar to numeric."
SQL query extracted from SQLGrammarException really is incorrect (notice unresolved parameter substitutions :projekt.projekt)
Code:
select count(*) as col_0_0_ from PRIPOMIENKA pripomienk0_ where pripomienk0_.Projekt = :projekt.projekt and 1=1 and (pripomienk0_.AUTOR like ?) and (exists (select komentar1_.PK from KOMENTAR komentar1_, CTUSER ctuser4_, USERINFO userinfo5_ where komentar1_.Projekt = :projekt.projekt and ctuser4_.PK=userinfo5_.CTUSER and komentar1_.ZADAVATEL=ctuser4_.PK and komentar1_.PK=(select max(komentar2_.PK) from KOMENTAR komentar2_ where komentar2_.Projekt = :projekt.projekt and komentar2_.DATUMVYTVORENIA=(select max(komentar3_.DATUMVYTVORENIA) from KOMENTAR komentar3_ where komentar3_.Projekt = :projekt.projekt and komentar3_.PRIPOMIENKA=pripomienk0_.PK) and komentar2_.PRIPOMIENKA=pripomienk0_.PK) and komentar1_.DATUMVYTVORENIA>=? and userinfo5_.EXTERNY=1))
When I remove
AND p.autor LIKE :externyAutor_ from HQL, query executes just fine. Same as executing just
AND p.autor LIKE :externyAutor_ without the big EXISTS clause.
I believe, it is a Hibernate bug, isn't it?
jan soltis
Full stack trace of any exception that occurs:
java.sql.SQLException
Error converting data type nvarchar to numeric.
SQLState: 37000
errorCode: 8114
Stack Trace:
* net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:364)
* net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2754)
* net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2195)
* net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:784)
* net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(JtdsResultSet.java:143)
* net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:378)
* net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedStatement.java:672)
* org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
* org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:137)
* org.hibernate.loader.Loader.getResultSet(Loader.java:1676)
* org.hibernate.loader.Loader.doQuery(Loader.java:662)
* org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:223)
* org.hibernate.loader.Loader.doList(Loader.java:2147)
* org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2026)
* org.hibernate.loader.Loader.list(Loader.java:2021)
* org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:369)
* org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:296)
* org.hibernate.impl.SessionImpl.list(SessionImpl.java:985)
* org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
* org.hibernate.impl.AbstractQueryImpl.uniqueResult(AbstractQueryImpl.java:650)
Hibernate version:
Same behavior in both 3.0.5 and 3.1 beta2
Name and version of the database you are using:
MS SQL Server
Login
FAQ
Search