I have subselect query that produces a QuerySyntaxError when using HQL but works well in SQL
what does not work:
Query = s.createQuery("from Bookmark b where (b.creationDate,b.urlStat.url) in (select max(b2.creationDate),b2.urlStat.url from Bookmark b2 group by b2.urlStat.url) order by b.creationDate DESC")
what does work:
SQLQuery bq = s.createSQLQuery("select {b.*} from bookmark b where (b.creationDate, b.url) in" + " (select max(b2.creationDate), b2.url from bookmark b2 group by b2.url)" + " order by b.creationDate DESC");
bq.addEntity("b",Bookmark.class);
I don't think there should be any parsing error in my HQL query.
Hibernate version: 3.0 (28.2.2005)
Full stack trace of any exception that occurs:
org.hibernate.hql.ast.QuerySyntaxError: expecting CLOSE, found ',' near line 1, column 49 [from hotmark.bo.Bookmark b where (b.creationDate,b.urlStat.url) in (select max(b2.creationDate),b2.urlStat.url from hotmark.bo.Bookmark b2 group by b2.urlStat.url) order by b.creationDate DESC]
at org.hibernate.hql.ast.ErrorCounter.throwQueryException(ErrorCounter.java:74)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:214)
at org.hibernate.hql.ast.QueryTranslatorImpl.doCompile(QueryTranslatorImpl.java:127)
at org.hibernate.hql.ast.QueryTranslatorImpl.compile(QueryTranslatorImpl.java:83)
at org.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:414)
at org.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:814)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:773)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:74)
at hotmark.web.ViewAllAction.execute(ViewAllAction.java:40)
at org.apache.struts.action.RequestProcessor.processActionPerform(RequestProcessor.java:419)
at org.apache.struts.action.RequestProcessor.process(RequestProcessor.java:224)
at org.apache.struts.action.ActionServlet.process(ActionServlet.java:1192)
at org.apache.struts.action.ActionServlet.doGet(ActionServlet.java:412)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:689)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at hotmark.persistence.HibernateFilter.doFilter(HibernateFilter.java:40)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:214)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:178)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:148)
at org.apache.coyote.http11.Http11Processor.process(Http11Processor.java:825)
at org.apache.coyote.http11.Http11Protocol$Http11ConnectionHandler.processConnection(Http11Protocol.java:738)
at org.apache.tomcat.util.net.PoolTcpEndpoint.processSocket(PoolTcpEndpoint.java:526)
at org.apache.tomcat.util.net.LeaderFollowerWorkerThread.runIt(LeaderFollowerWorkerThread.java:80)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:684)
at java.lang.Thread.run(Unknown Source)
Caused by: line 1:49: expecting CLOSE, found ','
at antlr.Parser.match(Parser.java:213)
at org.hibernate.hql.antlr.HqlBaseParser.primaryExpression(HqlBaseParser.java:3009)
at org.hibernate.hql.antlr.HqlBaseParser.atom(HqlBaseParser.java:2784)
at org.hibernate.hql.antlr.HqlBaseParser.unaryExpression(HqlBaseParser.java:2665)
at org.hibernate.hql.antlr.HqlBaseParser.multiplyExpression(HqlBaseParser.java:2691)
at org.hibernate.hql.antlr.HqlBaseParser.additiveExpression(HqlBaseParser.java:532)
at org.hibernate.hql.antlr.HqlBaseParser.relationalExpression(HqlBaseParser.java:2112)
at org.hibernate.hql.antlr.HqlBaseParser.equalityExpression(HqlBaseParser.java:1975)
at org.hibernate.hql.antlr.HqlBaseParser.negatedExpression(HqlBaseParser.java:1938)
at org.hibernate.hql.antlr.HqlBaseParser.logicalAndExpression(HqlBaseParser.java:1857)
at org.hibernate.hql.antlr.HqlBaseParser.logicalOrExpression(HqlBaseParser.java:1821)
at org.hibernate.hql.antlr.HqlBaseParser.expression(HqlBaseParser.java:1697)
at org.hibernate.hql.antlr.HqlBaseParser.logicalExpression(HqlBaseParser.java:1802)
at org.hibernate.hql.antlr.HqlBaseParser.whereClause(HqlBaseParser.java:366)
at org.hibernate.hql.antlr.HqlBaseParser.queryRule(HqlBaseParser.java:683)
at org.hibernate.hql.antlr.HqlBaseParser.selectStatement(HqlBaseParser.java:227)
at org.hibernate.hql.antlr.HqlBaseParser.statement(HqlBaseParser.java:150)
at org.hibernate.hql.ast.QueryTranslatorImpl.parse(QueryTranslatorImpl.java:208)
... 30 more
Name and version of the database you are using: PostgresSQL 8
I don't really have mapping xml files since I am using EJB 3 annotations. in Bookmark.java I have
@ManyToOne(
fetch=FetchType.EAGER)
@JoinColumn(name="url")
public UrlStat getUrlStat()
{
return urlStat;
}
and UrlStat @Id is getUrl.
|