db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Charlie Hubbard (JIRA)" <j...@apache.org>
Subject [jira] Created: (DERBY-2970) Hibernate Joins fail if you use Derby (aliases in select clause trump aliases in from and where clauses)
Date Tue, 24 Jul 2007 14:07:31 GMT
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.


Mime
View raw message