Hibernate Joins fail if you use Derby (aliases in select clause trump aliases
in from and where clauses)
--------------------------------------------------------------------------------------------------------
Key: DERBY-2970
URL: https://issues.apache.org/jira/browse/DERBY-2970
Project: Derby
Issue Type: Bug
Affects Versions: 10.3.1.2
Environment: Hibernate v3, Derby 10.3.1.2, Sun's Java 1.6.0_01
Reporter: Charlie Hubbard
Using hibernate to do a simple join causes an unknown field exception from
derby's libraries. Hibernate uses a mixture of aliases to specify the select
clause which is different from the aliases used in the from and where clauses.
While strange this is perfectly legal to do. My tables have a very simple
structure. Customer has many licenses and they are linked by the shared
customer ID field. So it looks something like:
Customers:
---------------
id : identity
name : varchar(80)
...
LicenseKeys:
-----------------
id : identity
CustomerID : int
LicenseKey : varchar(256)
...
Here is the HQL I tried to execute against the DB:
select c, key from Customer c join c.keys as key where key.expirationDate <
current_timestamp() or key.maintenanceDate < current_timestamp()
Here is the SQL generated by hibernate:
Hibernate:
select
customer0_.id as id0_0_,
keys1_.id as id1_1_,
customer0_.address as address0_0_,
customer0_.city as city0_0_,
customer0_.company as company0_0_,
customer0_.email as email0_0_,
customer0_.name as name0_0_,
customer0_.state as state0_0_,
customer0_.zipcode as zipcode0_0_,
keys1_.creationDate as creation2_1_1_,
keys1_.CustomerID as CustomerID1_1_,
keys1_.expirationDate as expirati3_1_1_,
keys1_.licenseKey as licenseKey1_1_,
keys1_.maintenanceDate as maintena5_1_1_,
keys1_.serialNumber as serialNu6_1_1_,
keys1_.trial as trial1_1_
from
APP.Customers customer0_
inner join
APP.LicenseKeys keys1_
on customer0_.id=keys1_.CustomerID
where
keys1_.expirationDate<current timestamp
or keys1_.maintenanceDate<current timestamp
Here is the error coming out of my log file:
2007-07-24 09:48:38,357 [btpool0-4] WARN
org.hibernate.util.JDBCExceptionReporter - SQL Error: 30000, SQLState: 42X04
2007-07-24 09:48:38,357 [btpool0-4] ERROR
org.hibernate.util.JDBCExceptionReporter - Column 'CUSTOMER0_.ID' is either
not in any table in the FROM list or appears within a join specification and is
outside the scope of the join specification or appears in a HAVING clause and
is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'CUSTOMER0_.ID' is not a column in the target table.
2007-07-24 09:48:40,107 [Mail loader] INFO
com.emailarchive.demon.MailDemonLoader - Completed polling cycle in 922 ms.
Found 0 messages.
2007-07-24 09:48:40,482 [btpool0-4] WARN
org.hibernate.util.JDBCExceptionReporter - SQL Warning: 10000, SQLState: 01J01
2007-07-24 09:48:40,482 [btpool0-4] WARN
org.hibernate.util.JDBCExceptionReporter - Database
'webapps/licensingserver/WEB-INF/licensing' not created, connection made to
existing database instead.
2007-07-24 09:48:40.482::WARN: EXCEPTION
net.sourceforge.stripes.exception.StripesServletException: Unhandled exception
caught by the default exception handler.
at
net.sourceforge.stripes.exception.DefaultExceptionHandler.handle(DefaultExceptionHandler.java:40)
at
net.sourceforge.stripes.controller.StripesFilter.doFilter(StripesFilter.java:184)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1065)
at
org.mortbay.jetty.servlet.ServletHandler.handle(ServletHandler.java:365)
at
org.mortbay.jetty.security.SecurityHandler.handle(SecurityHandler.java:185)
at
org.mortbay.jetty.servlet.SessionHandler.handle(SessionHandler.java:181)
at
org.mortbay.jetty.handler.ContextHandler.handle(ContextHandler.java:689)
at org.mortbay.jetty.webapp.WebAppContext.handle(WebAppContext.java:391)
at
org.mortbay.jetty.handler.ContextHandlerCollection.handle(ContextHandlerCollection.java:146)
at
org.mortbay.jetty.handler.HandlerCollection.handle(HandlerCollection.java:114)
at
org.mortbay.jetty.handler.HandlerWrapper.handle(HandlerWrapper.java:139)
at org.mortbay.jetty.Server.handle(Server.java:285)
at
org.mortbay.jetty.HttpConnection.handleRequest(HttpConnection.java:457)
at
org.mortbay.jetty.HttpConnection$RequestHandler.headerComplete(HttpConnection.java:751)
at org.mortbay.jetty.HttpParser.parseNext(HttpParser.java:500)
at org.mortbay.jetty.HttpParser.parseAvailable(HttpParser.java:209)
at org.mortbay.jetty.HttpConnection.handle(HttpConnection.java:357)
at
org.mortbay.io.nio.SelectChannelEndPoint.run(SelectChannelEndPoint.java:329)
at
org.mortbay.thread.BoundedThreadPool$PoolThread.run(BoundedThreadPool.java:475)
Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
at
org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:67)
at
org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
at org.hibernate.loader.Loader.doList(Loader.java:2223)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2104)
at org.hibernate.loader.Loader.list(Loader.java:2099)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:378)
at
org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:338)
at
org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1121)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at
com.emailarchive.licensing.web.controller.AdministrationActionBean.expired(AdministrationActionBean.java:44)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:597)
at
net.sourceforge.stripes.controller.DispatcherHelper$6.intercept(DispatcherHelper.java:445)
at
net.sourceforge.stripes.controller.ExecutionContext.proceed(ExecutionContext.java:157)
at
net.sourceforge.stripes.controller.BeforeAfterMethodInterceptor.intercept(BeforeAfterMethodInterceptor.java:107)
at
net.sourceforge.stripes.controller.ExecutionContext.proceed(ExecutionContext.java:154)
at
net.sourceforge.stripes.controller.ExecutionContext.wrap(ExecutionContext.java:73)
at
net.sourceforge.stripes.controller.DispatcherHelper.invokeEventHandler(DispatcherHelper.java:443)
at
net.sourceforge.stripes.controller.DispatcherServlet.invokeEventHandler(DispatcherServlet.java:241)
at
net.sourceforge.stripes.controller.DispatcherServlet.doPost(DispatcherServlet.java:154)
at
net.sourceforge.stripes.controller.DispatcherServlet.doGet(DispatcherServlet.java:61)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:707)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:820)
at
org.mortbay.jetty.servlet.ServletHolder.handle(ServletHolder.java:491)
at
org.mortbay.jetty.servlet.ServletHandler$CachedChain.doFilter(ServletHandler.java:1074)
at
net.sourceforge.stripes.controller.StripesFilter.doFilter(StripesFilter.java:181)
... 17 more
Caused by: java.sql.SQLSyntaxErrorException: Column 'CUSTOMER0_.ID' is either
not in any table in the FROM list or appears within a join specification and is
outside the scope of the join specification or appears in a HAVING clause and
is not in the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'CUSTOMER0_.ID' is not a column in the target table.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown
Source)
at
org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown
Source)
at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown
Source)
at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:505)
at
org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:423)
at
org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1547)
at org.hibernate.loader.Loader.doQuery(Loader.java:673)
at
org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:236)
at org.hibernate.loader.Loader.doList(Loader.java:2220)
... 43 more
Caused by: java.sql.SQLException: Column 'CUSTOMER0_.ID' is either not in any
table in the FROM list or appears within a join specification and is outside
the scope of the join specification or appears in a HAVING clause and is not in
the GROUP BY list. If this is a CREATE or ALTER TABLE statement then
'CUSTOMER0_.ID' is not a column in the target table.
at
org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at
org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown
Source)
... 63 more
Caused by: ERROR 42X04: Column 'CUSTOMER0_.ID' is either not in any table in
the FROM list or appears within a join specification and is outside the scope
of the join specification or appears in a HAVING clause and is not in the GROUP
BY list. If this is a CREATE or ALTER TABLE statement then 'CUSTOMER0_.ID' is
not a column in the target table.
at org.apache.derby.iapi.error.StandardException.newException(Unknown
Source)
at
org.apache.derby.impl.sql.compile.ColumnReference.bindExpression(Unknown Source)
at
org.apache.derby.impl.sql.compile.ResultColumn.bindExpression(Unknown Source)
at
org.apache.derby.impl.sql.compile.ResultColumnList.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown
Source)
at
org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown
Source)
at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown
Source)
at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown
Source)
at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
at
org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown
Source)
... 57 more
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
