You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-dev@db.apache.org by "Bryan Pendleton (JIRA)" <ji...@apache.org> on 2007/07/27 17:05:18 UTC
[jira] Commented: (DERBY-2970) Hibernate Joins fail if you use
Derby (aliases in select clause trump aliases in from and where clauses)
[ 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.