Column aliases change from HQL to SQL automatically. help

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

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate Version:
3.2.0
Mapping documents:

Code between sessionFactory.openSession() and session.close():

Full stack trace of any exception that occurs:
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'score' in 'order clause'
[tf:BPMController] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
[tf:BPMController] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2822)
[tf:BPMController] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1536)
[tf:BPMController] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1626)
[tf:BPMController] at com.mysql.jdbc.Connection.execSQL(Connection.java:3031)
[tf:BPMController] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:943)
[tf:BPMController] at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1049)
[tf:BPMController] at com.mchange.v2.c3p0.impl.NewProxyPreparedStatement.executeQuery(NewProxyPreparedStatement.java:76)
[tf:BPMController] at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:186)
[tf:BPMController] at org.hibernate.loader.Loader.getResultSet(Loader.java:1668)
[tf:BPMController] at org.hibernate.loader.Loader.doQuery(Loader.java:662)
[tf:BPMController] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
[tf:BPMController] at org.hibernate.loader.Loader.doList(Loader.java:2144)
[tf:BPMController] ... 11 more
MyHql:
select new com.printrak.bpm.transaction.Respondent(file.Case.id, length(:data, file.data) as length) from DData as file order by length desc

MySql:
select fingerfdpd0_1_.djid as col_0_0_, omm(?, fingerfdpd0_.data) as col_1_0_ from DData fingerfdpd0_ inner join BData fingerfdpd0_1_ on fingerfdpd0_.id=fingerfdpd0_1_.id order by length desc
(show_sql=true):

t:

Hql replaces length in "oder clause" with "col_1_0_"

How can I prevent hibernate from converting col name from length to col_1_0_. I don't want to have to call length() once again in the order by clause. I don't think this is a bug, I am probably missing something. I bought hibernate book and couldn't find answers there either. Thanks in a dvance.

mseritan · **Posted:** Tue Oct 17, 2006 10:25 pm

Maybe the HQL parser is thrown off by 'length' as being a possible function. It seems that this is an arbitary alias name, can you change the name and see if the parser behaves differently? For example len, _length.

Just a quick suggestion, may not work but easy enough to try...

Marius

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

good suggestion, thanks. I tried it and nothing. I replaced it with "score" which is not reserved or anyhting and it still didn't work. The parser simply ignores the entire tag and aliases it as "col_1_0_" I am so frustrated with it, I do not know what to do. the entire point of the query is to sort on the result of the length function and I can't have it call length 2 times in the select and order by clause, doesn't make sense.

My fix is to aliase the column as "col_1_0_" myself, but common, that's just dirty programming. Besides the user entering queries in my app won't know anything about this notation.

Someone please help !!! Thanks.

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

Can someone please help me?! this is still a problem. Is this a bug? why does Hibernate drop the "as blahblah" from the select clause and rename it into it's own col_1_0_. Do developers look at this forum. Can anyone tell me what to do differently? This is the only problem I have with Hibernate, otherwise it's flawless. Thanks.

mailias · **Joined:** Wed Nov 22, 2006 1:32 pm **Posts:** 8

This really seems to be a bug!
I have a similar problem:

Code:

select p, c, inc, pers.lastName, inc.name, (coalesce(pers.lastName,) || coalesce(pers.firstName,) || coalesce(inc.name,) || coalesce(inc.addendum,)) as sort
 from 
   Participation as p
     left join p.contact as c 
       left join c.person as pers 
         left join c.incorporate as inc 
 where 
   c.id = p.contact.id
   and (pers.id = c.person.id or inc.id = c.incorporate.id)
   and p.event.id = 5
 order by sort

will not work as Hibernate ignores the given alias 'sort' and creates its own 'col_5_0_'. Using

Code:

order by col_5_0_

solves the problem. But this is really ugly and who guarantees that Hibernate will call its colums always this way?
Maybe there is a better solution? Anyone?

The exception thrown is:

Code:

org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:65)
   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:375)
   at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:308)
   at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:153)
   at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1129)
   at org.hibernate.impl.QueryImpl.list(QueryImpl.java:79)
   at net.java.dev.hts.output.result.QueryResultMultiViewElement$3.run(QueryResultMultiViewElement.java:151)
   at org.openide.util.RequestProcessor$Task.run(RequestProcessor.java:493)
   at org.openide.util.RequestProcessor$Processor.run(RequestProcessor.java:926)
Caused by: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column 'sort' in 'order clause'
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2870)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1573)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1665)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3176)
   at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1153)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1266)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:139)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1669)
   at org.hibernate.loader.Loader.doQuery(Loader.java:662)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:224)
   at org.hibernate.loader.Loader.doList(Loader.java:2145)
   ... 10 more

The SQL generated is:

Code:

select participat0_.id as col_0_0_, contact1_.id as col_1_0_, incorporat3_.id as col_2_0_, person2_.lastname as col_3_0_, incorporat3_.name as col_4_0_, concat(coalesce(person2_.lastname, ''), coalesce(person2_.firstname, ''), coalesce(incorporat3_.name, ''), coalesce(incorporat3_.addendum, '')) as col_5_0_ from crm_participation participat0_ left outer join crm_contact contact1_ on participat0_.contactid=contact1_.id left outer join crm_person person2_ on contact1_.personid=person2_.id left outer join crm_incorporate incorporat3_ on contact1_.incorporateid=incorporat3_.id where contact1_.id=participat0_.contactid and (person2_.id=contact1_.personid or incorporat3_.id=contact1_.incorporateid) and participat0_.eventid=5 order by sort

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

i still have this problem. I find it very hard to believe that such a thing has gone by unnoticed. Clearly I am not the only one that is experiencing this problem. Why can't anyone reply to this.

If it's a BUG just say so.

nathanmoon · **Posted:** Fri Dec 15, 2006 3:58 pm

I don't know if it's a bug or not, but have you tried just duplicating your length function (or coalesce or whatever) in the order by to see if it really does affect performance (run using the alias and with just duplicating the function to compare the performance)? I wouldn't be surprised if the database already does the optimization you are trying to do with the alias.

mailias · **Joined:** Wed Nov 22, 2006 1:32 pm **Posts:** 8

If I am allowed to use column aliases which actually have no effect at all as hibernate ignores them this really looks like a bug to me.
Why allow column aliases at all if they are ignored?
And why doesn't Hibernate just use my alias if I specify one?

Maybe there is a reason for this, but I couldn't find anything in the documentation. Neither did I find any clean workaround...

mailias · **Joined:** Wed Nov 22, 2006 1:32 pm **Posts:** 8

http://opensource.atlassian.com/project ... e/HHH-1902

I hope this one will not be rejected.
Some other similar issues have been closed in the past without being fixed.

max · **Posted:** Mon Dec 18, 2006 7:26 am

guys - saying "i hope this one will not be rejected" without giving a single testcase is not a very convincing argument.

We don't close cases that has a testcase, really is a bug and is not a duplicate.

so if you want some response then provide a testcase.

mailias · **Joined:** Wed Nov 22, 2006 1:32 pm **Posts:** 8

Ok, I added some code showing the problem that Hibernate ignores custom aliases.

http://opensource.atlassian.com/project ... e/HHH-1902

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

i used length() as an example, i actually has my own UDF that i call. So I am not sure if a db would optimize that or not. Besides as mailias said above, if aliases are allowed then they should not be ignored. they were invented for a reason.

I hope this gets resolved one way or another. Thanks guys.

mailias · **Joined:** Wed Nov 22, 2006 1:32 pm **Posts:** 8

I found a workaround which works for me as long as this bug (in my opinion) is not fixed in hibernate:

just move the temporary column used for sorting directly into the order by statement:

So instead of

Code:

select p, c, inc, pers.lastName, inc.name, (coalesce(pers.lastName,'') || coalesce(pers.firstName,'') || coalesce(inc.name,'') || coalesce(inc.addendum,'')) as sort
 from
   Participation as p
     left join p.contact as c
       left join c.person as pers
         left join c.incorporate as inc
 where
   c.id = p.contact.id
   and (pers.id = c.person.id or inc.id = c.incorporate.id)
   and p.event.id = 5
 order by sort

I use

Code:

select p, c, inc, pers.lastName, inc.name 
 from
   Participation as p
     left join p.contact as c
       left join c.person as pers
         left join c.incorporate as inc
 where
   c.id = p.contact.id
   and (pers.id = c.person.id or inc.id = c.incorporate.id)
   and p.event.id = 5
 order by (coalesce(pers.lastName,'') || coalesce(pers.firstName,'') || coalesce(inc.name,'') || coalesce(inc.addendum,''))

I don't know, whether this works for you, toyrunr, too?
(If so, please rate...)

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

I have a UDF that returns a value. I want to select a few columns (including the value returned by the UDF) and sort the results by this score.

How are you selecting Sort ? in the 2nd query since you do not list it in the SELECT clause?

This doesn't work for me, unless I'm still missing something.

Thanks for your help dude, I appreciate it.

mailias · **Joined:** Wed Nov 22, 2006 1:32 pm **Posts:** 8

I am not sure whether I understand your problem correctly and I must admit I have no experience with UDF.

However isn't it possible to just call the UDF in the order by clause again. With my sort column I moved it to the order by clause instead of copying it as I do not need the value in my results - I only sort rows by this information. But if you need it both as column and as sort criteria just call the UDF at both places. Does this work for you?