Hi all,

I create reporting HQLs dynamically that also can contain HAVING clause. The problem is that hibernate always generates aliases to properties in the select cause, but the generated sql won't work (derby 10.1.2.1), because derby doesn't like aliases in select clause:

Caused by: java.sql.SQLException: Column 'JOB0_.COL_0_0_' 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 'JOB0_.COL_0_0_' is not a column in the target table.

I tried to run an sql statement without aliases (at SELECT) and it worked.

Is it possible to force hibernate not to use aliases in the generated SQL?

Or do I have to use native SQL query for that?

HQL:
select job.jobId, user.name, script.name, job.executionPriority, count(job.jobId)
from Job as job inner join job.script as script inner join job.user as user where (user.name in (:p0)) and ((script.name like '%test%') and (job.executionPriority<:p1) and (job.executionPriority>:p2))
group by job.jobId, user.name, script.name, job.executionPriority
having count(job.jobId)>:p3 order by job.jobId

The generated SQL (show_sql=true):

select
job0_.JOB_ID as col_0_0_,
accountinf2_.NAME as col_1_0_,
resourcein1_.NAME as col_2_0_,
job0_.EXEC_PRIORITY as col_3_0_,
count(job0_.JOB_ID) as col_4_0_
from
APP.ET_JOB job0_
inner join
APP.ET_OBJECT resourcein1_
on job0_.SCRIPT_ID=resourcein1_.OBJECT_ID
inner join
APP.ET_ACCOUNT accountinf2_
on job0_.ACCOUNT_ID=accountinf2_.ACCOUNT_ID
where
(
accountinf2_.NAME in (
?
)
)
and (
resourcein1_.NAME like '%test%'
)
and job0_.EXEC_PRIORITY<?
and job0_.EXEC_PRIORITY>?
group by
job0_.JOB_ID ,
accountinf2_.NAME ,
resourcein1_.NAME ,
job0_.EXEC_PRIORITY
having
count(job0_.JOB_ID)>?
order by
job0_.JOB_ID

Hibernate version: 3.2.0 cr1

Name and version of the database you are using: Derby 10.1.2.1


Full stack trace of any exception that occurs:
job0_.JOB_ID
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:2148)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2029)
at org.hibernate.loader.Loader.list(Loader.java:2024)
at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:392)
at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:333)
at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:172)
at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1109)
at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
at com.etixpert.zagreus.server.data.JobDAO.report(JobDAO.java:214)
at com.etixpert.zagreus.server.data.Test.main(Test.java:76)
Caused by: java.sql.SQLException: Column 'JOB0_.COL_0_0_' 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 'JOB0_.COL_0_0_' is not a column in the target table.
at org.apache.derby.client.am.SQLExceptionFactory.getSQLException(SQLExceptionFactory.java:45)
at org.apache.derby.client.am.SqlException.getSQLException(SqlException.java:307)
at org.apache.derby.client.am.Connection.prepareStatement(Connection.java:390)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:497)
at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:415)
at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:139)
at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1561)
at org.hibernate.loader.Loader.doQuery(Loader.java:661)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
at org.hibernate.loader.Loader.doList(Loader.java:2145)
... 9 more
Caused by: org.apache.derby.client.am.SqlException: Column 'JOB0_.COL_0_0_' 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 'JOB0_.COL_0_0_' is not a column in the target table.
at org.apache.derby.client.am.Statement.completeSqlca(Statement.java:1647)
at org.apache.derby.client.net.NetStatementReply.parsePrepareError(NetStatementReply.java:533)
at org.apache.derby.client.net.NetStatementReply.parsePRPSQLSTTreply(NetStatementReply.java:138)
at org.apache.derby.client.net.NetStatementReply.readPrepareDescribeOutput(NetStatementReply.java:47)
at org.apache.derby.client.net.StatementReply.readPrepareDescribeOutput(StatementReply.java:39)
at org.apache.derby.client.net.NetStatement.readPrepareDescribeOutput_(NetStatement.java:138)
at org.apache.derby.client.am.Statement.readPrepareDescribeOutput(Statement.java:1276)
at org.apache.derby.client.am.PreparedStatement.readPrepareDescribeInputOutput(PreparedStatement.java:1432)
at org.apache.derby.client.am.PreparedStatement.flowPrepareDescribeInputOutput(PreparedStatement.java:1510)
at org.apache.derby.client.am.PreparedStatement.prepare(PreparedStatement.java:241)
at org.apache.derby.client.am.Connection.prepareStatementX(Connection.java:1536)
at org.apache.derby.client.am.Connection.prepareStatement(Connection.java:377)
... 16 more

Thnx
Georg

Could the problem be caused by this issue with Derby?
- http://issues.apache.org/jira/browse/DERBY-84

We are looking at using Derby ourselves (embedded in a fat client), and this is one of the major issues we need to address if we're going to use it.

We haven't done any work on analysing the potential effects of this defect, but it seems like it might be a major showstopper with regard to the use of Derby and Hibernate.

On the resolution front, it looks like the Derby developers are going with the old "well, we're conformant to the spec, even if it means those kinds of queries are unusable now". Similar to their position on not supporting unique constraints on nullable columns.

If you need those kinds of queries, I'm not sure what the path forward from here is.

Looks like the Hibernate guys will not be changing the way Hibernate uses column aliases for one database; and the Derby devs don't seem to want to change their support for column aliases to be in line with that of other major databases.
Stalemate.

Hi

I saw this issue, and I hoped therefore, that there is a way to switch off the aliases in Hibernate, (or it should be included in derby dialect).

For me it is strange, that the error comes even if I use alias only in select clause, and in other parts of the sql command (where, group by, having) only the column names.

I tried also the nightly builds of derby, and with that I also get the error, so yes, Derby team seams not to change this approach.

Thnx for the answer.

Cheers,
Georg