db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Kathey Marsden (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-2970) Hibernate Joins fail if you use Derby (aliases in select clause trump aliases in from and where clauses)
Date Fri, 14 Dec 2007 16:51:44 GMT

     [ https://issues.apache.org/jira/browse/DERBY-2970?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Kathey Marsden updated DERBY-2970:
----------------------------------

    Component/s: SQL

> 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
>          Components: SQL
>    Affects Versions: 10.3.1.4
>         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.


Mime
View raw message