[
https://issues.apache.org/jira/browse/DERBY-2970?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#action_12516037
]
Bryan Pendleton commented on DERBY-2970:
----------------------------------------
Thanks for the bug report! I'm having a little trouble boiling this down to a
pure-SQL Derby repro. Here's what I tried:
ij> create table customer (id int, name varchar(80));
0 rows inserted/updated/deleted
ij> create table keys (id int, customerid int, licensekey varchar(256));
0 rows inserted/updated/deleted
ij> select customer0.id as id_0, keys1.id as id_1 from customer customer0 inner
join keys keys1 on customer0.id = keys1.customerid where keys1.licensekey =
'abc';
ID_0 |ID_1
-----------------------
0 rows selected
ij> select customer0.id as id_0, keys1.id as id_1 from app.customer customer0
inner join app.keys keys1 on customer0.id = keys1.customerid where
keys1.licensekey = 'abc';
ID_0 |ID_1
-----------------------
0 rows selected
With respect to table and column aliases, I'm not sure I see anything different
between my example and yours, but mine doesn't trigger the 42X04 error.
Can you construct a variation of my SQL which demonstrates the problem?
> 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.
