Column aliases change from HQL to SQL automatically. help

toyrunr · **Joined:** Tue Oct 17, 2006 7:32 pm **Posts:** 8

Yeah, ofcourse it works for me, however the UDF does some very CPU intensive calculations and the only reason I went with this whole SQL/UDF approach is to reduce overhead and speed up my APP that was already working correctly. If I call the UDF twice, I'll only make the APP slower defeating the purpose of this entire venture.

I see what you are doing and I see how it is working for you. However, I need the results returned and I also need to sort based on them. It works great with regular SQL query, but Hibernate fails to maintain the proper alias that I assign.

score -> col_1_0_

Thank you for your help. I hope this gets fixed in near future, otherwise I'd have to abandon Hibernate altogether.

natto lover · **Posted:** Sun Jan 28, 2007 6:36 am

Hi.
I ran into a problem that seems exactly the same as this thread.
I'd like to provide some HQL AST logs, that might help diagnose the problem.

I give an alias to an expression in the select clause, try to refer it in the where clause. In SQL, Hibernate replaces the alias in the select clause, but leaves the original text in the where clause. I believe this is a bug (though that wouldn't turn down my applause to hibernate).

My environment is
DB: MySQL5 on Solarisx86
App: Hibernate3.2 on Win2K

This is the HQL:

Code:

select (p.endDate - p.startDate) as [color=red]period[/color], p
from Project as p
where [color=red]period [/color]> :period_length

See above where the alias 'period' appears.

Now, This is the resulting SQL:

Code:

select
 project0_.end_date-project0_.start_date as [color=blue]col_0_0_[/color],
 project0_.id as col_1_0_,
 project0_.id as id0_,
 project0_.name as name0_,
 project0_.start_date as start3_0_,
 project0_.end_date as end4_0_
from PROJECT project0_ where [color=blue]period[/color]>?

Note above the 'period' that was in the select clause is replaced with machine generated "col_0_0_", but that is not applied for the alias in the where clause.

The following are the AST dumps in the hibernate logs.

Here is the AST (is that abstract syntax tree?) of the HQL:

Code:

DEBUG [main] (QueryTranslatorImpl.java:266) - --- HQL AST ---
 \-[QUERY] 'query'
    +-[SELECT_FROM] 'SELECT_FROM'
    |  +-[FROM] 'from'
    |  |  \-[RANGE] 'RANGE'
    |  |     +-[DOT] '.'
    |  |     |  +-[IDENT] 'entities'
    |  |     |  \-[IDENT] 'Project'
    |  |     \-[ALIAS] 'p'
    |  \-[SELECT] 'select'
    |     +-[AS] 'as'
    |     |  +-[MINUS] '-'
    |     |  |  +-[DOT] '.'
    |     |  |  |  +-[IDENT] 'p'
    |     |  |  |  \-[IDENT] 'endDate'
    |     |  |  \-[DOT] '.'
    |     |  |     +-[IDENT] 'p'
    |     |  |     \-[IDENT] 'startDate'
    |     |  \-[IDENT] 'period'
    |     \-[IDENT] 'p'
    \-[WHERE] 'where'
       \-[GT] '>'
          +-[IDENT] 'period'
          \-[COLON] ':'
             \-[IDENT] 'period_length'

Doesn't look bad to me.

And here is the AST of the SQL:

Code:

DEBUG [main] (QueryTranslatorImpl.java:232) - --- SQL AST ---
 \-[SELECT] QueryNode: 'SELECT'  querySpaces (PROJECT)
    +-[SELECT_CLAUSE] SelectClause: '{select clause}'
    |  +-[MINUS] BinaryArithmeticOperatorNode: '-' {dataType=org.hibernate.type.DoubleType@1ffc686}
    |  |  +-[DOT] DotNode: 'project0_.end_date' {propertyName=endDate,dereferenceType=4,propertyPath=endDate,path=p.endDate,tableAlias=project0_,className=entities.Project,classAlias=p}
    |  |  |  +-[ALIAS_REF] IdentNode: 'project0_.id' {alias=p, className=entities.Project, tableAlias=project0_}
    |  |  |  \-[IDENT] IdentNode: 'endDate' {originalText=endDate}
    |  |  \-[DOT] DotNode: 'project0_.start_date' {propertyName=startDate,dereferenceType=4,propertyPath=startDate,path=p.startDate,tableAlias=project0_,className=entities.Project,classAlias=p}
    |  |     +-[ALIAS_REF] IdentNode: 'project0_.id' {alias=p, className=entities.Project, tableAlias=project0_}
    |  |     \-[IDENT] IdentNode: 'startDate' {originalText=startDate}
    |  [color=red]+-[SELECT_COLUMNS] SqlNode: ' as col_0_0_'[/color]
    |  +-[ALIAS_REF] IdentNode: 'project0_.id as col_1_0_' {alias=p, className=entities.Project, tableAlias=project0_}
    |  +-[SQL_TOKEN] SqlFragment: 'project0_.id as id0_'
    |  \-[SQL_TOKEN] SqlFragment: 'project0_.name as name0_, project0_.start_date as start3_0_, project0_.end_date as end4_0_'
    +-[FROM] FromClause: 'from' FromClause{level=1, fromElementCounter=1, fromElements=1, fromElementByClassAlias=[p], fromElementByTableAlias=[project0_], fromElementsByPath=[], collectionJoinFromElementsByPath=[], impliedElements=[]}
    |  \-[FROM_FRAGMENT] FromElement: 'PROJECT project0_' FromElement{explicit,not a collection join,not a fetch join,fetch non-lazy properties,classAlias=p,role=null,tableName=PROJECT,tableAlias=project0_,origin=null,colums={,className=entities.Project}}
    \-[WHERE] SqlNode: 'where'
       \-[GT] BinaryLogicOperatorNode: '>'
          +-[IDENT] IdentNode: 'period' {originalText=period}
          \-[NAMED_PARAM] ParameterNode: '?' {name=period_length, expectedType=null}

The red portion looks erroneous to me.

The above example is a simplified version of my original situation.
My original problem included UDF calls.
I know that there is a workaround by not using aliases in this case, but I do want to use aliases to avoid duplicate calculation.

I want to use this feature in my production site, and I am willing to provide any more info that could help. If requested, I can post the test case program that produced the above output.

Thanks.
natto lover.

toyrunr wrote:

Yeah, ofcourse it works for me, however the UDF does some very CPU intensive calculations and the only reason I went with this whole SQL/UDF approach is to reduce overhead and speed up my APP that was already working correctly. If I call the UDF twice, I'll only make the APP slower defeating the purpose of this entire venture.

I see what you are doing and I see how it is working for you. However, I need the results returned and I also need to sort based on them. It works great with regular SQL query, but Hibernate fails to maintain the proper alias that I assign.

score -> col_1_0_

Thank you for your help. I hope this gets fixed in near future, otherwise I'd have to abandon Hibernate altogether.

Code:

natto lover · **Posted:** Mon Jan 29, 2007 7:26 am

Hi. Some follow up.

I stepped through the HQL-tree to SQL-tree transformation code that starts at QueryTranslatorImpl.compile. I think I found the cause of the problem.

For explanation, I will stick to the HQL example in my previous post:

Code:

select (p.endDate - p.startDate) as period, p 
from Project as p 
where period > :period_length

The reference to the alias 'period' in the last line will be handled in org.hibernate.hql.ast.tree.IdentNode.resolve();

This method has some if-elses that treat alias resolution, but there is no code that handles select clause aliases.

What the method is prepared to handle seems to be follows:
(1) a from clause alias
(2) the names that appear in an "entity_alias.property_name" expression.
(3) a 'naked' property name with the entity name omitted. Allowed only when the from clause has one entry, and there must be no entity aliases defined.
(4) constant aliases.

There is no code that is prepared to handle aliases given to expressions in the select clause.

In my debug session, the method ended without treating the alias 'period'.

I believe this method needs to test if the identifier matches any aliases in the select clause. I might be able to add that code, but I don't know the right thing to do after detecting that. This is my second day stepping through hibernate internals.

I hope this info helps someone appropriate understand and fix the problem.

Thanks,
Natto Lover.

max · **Posted:** Mon Jan 29, 2007 7:57 am

if you can provide a failing testcase and even better a patch then put it to jira and someone will take a look at it.

natto lover · **Posted:** Mon Jan 29, 2007 10:33 am

OK, I posted my test case as HHH-2390.

natto lover · **Posted:** Mon Jan 29, 2007 12:09 pm

Hi.
I learned some more through the debugger.
Tell me if I should provide further info elsewhere.

I found out that the information necessary to replace
the hql alias in the where clause is held in
org.hibernate.hql.ast.tree.SelectClause

I can reach out for that from within IdentNode via the
walker object, but the SelectClause object is not
initialized at that point.

The SelectClause object has an initialization method
named initializeExplicitSelectClause(FromClause)
that gets called to generate all the machine generated
column aliases. This method is called from
HqlSqlWalker.processQuery, which is called quite late.
processQuery is called after traversing through all the
clauses of the select statement.

So it seems non-trivial to make the information
available to IdentNode.

Here are some additional things that I learned:
(1) In MySQL, at least, select clause aliases can not be
referenced within where clauses. It can however
be used within order clauses.
I am not sure if this is the general case for SQL, or
specific to MySQL. (My production site does not use
MySQL.) So I added a test case that references the
alias from the "order by" clause.

(2) I tried to suppress hibernate from using auto generated
aliases for select clause expressions when the user provided
one in HQL. This was an easy code fix, and the SQL was
what I expected. However the HQL query operation failed.
The hibernate code that reads data out of the JDBC result
set was still using the auto generated alias.

Thanks for reading this.

max · **Posted:** Mon Jan 29, 2007 12:17 pm

steve followed up upon the jira case.

It's a planned future feature.

natto lover · **Posted:** Mon Jan 29, 2007 12:20 pm

I got a reply in JIRA that this is not a bug, and HQL does not claim
to be able to do this. However this capability is planned to be added
as a new feature in the future. Sounds great!

Thanks for the reply.

toyrunr · **Joined:** Tue Oct 17, 2006 7:32 pm **Posts:** 8

sweeeeeeeeet.

only took a few months to convince them!!!

Thanks a lot natto lover.