-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 
  Previous topic | Next topic 
Author Message
annih
 Post subject: "Column 'id' not found" but I don't even query for it
PostPosted: Fri Aug 13, 2010 8:25 am 
Newbie

Joined: Tue Sep 15, 2009 5:25 am
Posts: 8
Hi. I made a post a few weeks ago but nobody seems to be able to answer my question. I think it is because of the monstrous query I gave you. I tested around a little bit and noticed that I can heavily reduce the query while maintaining the problem.

Code:
SELECT fbStart.id as idS, fbDest.id as idD FROM FahrtBahnhof AS fbStart JOIN FahrtBahnhof AS fbDest ON fbDest.fahrt_id = fbStart.fahrt_id AND fbDest.id > fbStart.id


This query (created by session.createSQLQuery) will throw an Exception with Hibernate possibly caused by JDBC:
Quote:
7271 [main] INFO org.hibernate.type.IntegerType - could not read column value from result set: id; Column 'id' not found.
7272 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: S0022
7272 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Column 'id' not found.
Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not execute query
at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
at org.hibernate.loader.Loader.doList(Loader.java:2297)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2172)
at org.hibernate.loader.Loader.list(Loader.java:2167)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:316)
at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1832)
at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:179)
at de.taf.easygo.modules.timetable.hafas.Request.getDirectConnections(Request.java:358)
at de.taf.easygo.modules.timetable.hafas.Request.getConnections(Request.java:428)
at de.taf.easygo.modules.timetable.hafas.Request.main(Request.java:163)
Caused by: java.sql.SQLException: Column 'id' not found.
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1145)
at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2814)
at org.hibernate.type.IntegerType.get(IntegerType.java:51)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:186)
at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:212)
at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:501)
at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:447)
at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:344)
at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:647)
at org.hibernate.loader.Loader.doQuery(Loader.java:745)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270)
at org.hibernate.loader.Loader.doList(Loader.java:2294)
... 9 more


But WTF? I don't even ask for any column named ID. The Query runs fine when passed to MySQL directly and returns expected results:
idS, idD
'1', '2'
'1', '3'
'1', '4'
'1', '5'
'1', '6'
'1', '7'

The weirdest thing of all is the behaviour when I give Hibernate/JDBC what they ask for, an ID column (appended a LIMIT and another example column).
Code:
SELECT fbStart.id as idS, fbDest.id as idD, 77 as id, RAND() as notFunny FROM FahrtBahnhof AS fbStart JOIN FahrtBahnhof AS fbDest ON fbDest.fahrt_id = fbStart.fahrt_id AND fbDest.id > fbStart.id LIMIT 6


MySQL still returns the same results as above:
idS, idD, id, notFunny
'1', '2', '77', '0.996662789021305'
'1', '3', '77', '0.832569702372486'
'1', '4', '77', '0.172860175532159'
'1', '5', '77', '0.366591801276982'
'1', '6', '77', '0.314378510522077'
'1', '7', '77', '0.472117179513087'

Hibernate/JDBC return:
id, idS, idD, notFunny
77, 77, 77, 0.91741414267329
77, 77, 77, 0.342891859209996
77, 77, 77, 0.962216898763754
77, 77, 77, 0.78240894692243
77, 77, 77, 0.025394069054531
77, 77, 77, 0.779743535239011

If I fetch other columns of fbStart or fbDest instead of this example RAND() the values are also correct. So now WTF does it request an ID column and even more WTF does it overwrite some of my return values (only the aliased IDs)? (Sorry, but this is just too WTF.)

Thank you very much if finally somebody will answer this extremely confusing question.

Maybe some mod can delete my previous post.
Top
 Profile  
 
bill2004158
 Post subject: Re: "Column 'id' not found" but I don't even query for it
PostPosted: Fri Oct 22, 2010 11:26 am 
Newbie

Joined: Fri Oct 22, 2010 11:05 am
Posts: 2
I have got the same problem:
the query:
Code:
select user.id as userId, user.username as username,
.....


and the stacktrace is:
Code:

java.sql.SQLException: Column 'id' not found.
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
   at com.mysql.jdbc.ResultSetImpl.findColumn(ResultSetImpl.java:1144)
   at com.mysql.jdbc.ResultSetImpl.getInt(ResultSetImpl.java:2813)
   at org.hibernate.type.IntegerType.get(IntegerType.java:51)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:184)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:210)
   at org.hibernate.loader.custom.CustomLoader$ScalarResultColumnProcessor.extract(CustomLoader.java:497)
   at org.hibernate.loader.custom.CustomLoader$ResultRowProcessor.buildResultRow(CustomLoader.java:443)
   at org.hibernate.loader.custom.CustomLoader.getResultColumnOrRow(CustomLoader.java:340)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:629)
   at org.hibernate.loader.Loader.doQuery(Loader.java:724)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:259)
   at org.hibernate.loader.Loader.doList(Loader.java:2232)
   at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2129)
   at org.hibernate.loader.Loader.list(Loader.java:2124)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:312)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1723)
   at org.hibernate.impl.AbstractSessionImpl.list(AbstractSessionImpl.java:165)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:175)


I have debugged, the problem is:
Code:
   public String getColumnName(int column) throws SQLException {
      if (this.useOldAliasBehavior) {
         return getField(column).getName();
   }

      String name = getField(column).getNameNoAliases();
      
      if (name != null && name.length() == 0) {
         return getField(column).getName();
      }
      
      return name;
   }

com.mysql.jdbc.ResultSetMetaData.getColumnName(1) return "id".
it means the first column name(no alias) is "id".

and then get the value with the name "id".
org.hibernate.type.IntegerType.get(ResultSet rs, String name) .

while in my sql is "userId". so the get will be fail, and throw this exception.

although, I have found the reason,
but still not sure how to solve it.
Top
 Profile  
 
bill2004158
 Post subject: Re: "Column 'id' not found" but I don't even query for it
PostPosted: Fri Oct 22, 2010 12:32 pm 
Newbie

Joined: Fri Oct 22, 2010 11:05 am
Posts: 2
ah, It is bug of MySQL JDBC.

http://bugs.mysql.com/bug.php?id=35150
Top
 Profile  
 
pramma.moorthy
 Post subject: Re: "Column 'id' not found" but I don't even query for it
PostPosted: Wed Jun 27, 2012 6:50 am 
Newbie

Joined: Wed Jun 27, 2012 6:47 am
Posts: 1
Hi annih/bill2004158,

I am finding the same issue.
Any of you managed to solve this problem.....

Regards,
Top
 Profile  
 
monikasood
 Post subject: Re: "Column 'id' not found" but I don't even query for it
PostPosted: Tue Jul 31, 2012 11:43 pm 
Newbie

Joined: Tue Jul 31, 2012 11:39 pm
Posts: 2
I had the same issue and found a work around till the bug in MYSQL JDBC is not resolved.

Code:
select u.userid,u.username from (select id userid,name username from user)u


This way the alias we give are the main column names for the selected columns and work fine.
Top
 Profile  
 
cambyze
 Post subject: Re: "Column 'id' not found" but I don't even query for it
PostPosted: Tue Apr 30, 2013 1:16 am 
Newbie

Joined: Sat Nov 26, 2011 4:05 am
Posts: 5
I had the same issue because of a bad addScalar not corresponding to the selected columns.
Then take care...

Example of fixed code :
String query = "select book_id from authorBooks where author_id=" + Long.toString(author.getId());
SQLQuery sqlQuery = sessionFactory.getCurrentSession().createSQLQuery(query).addScalar("book_id", Hibernate.LONG);
Top
 Profile  
 
sanjutoyou
 Post subject: Re: "Column 'id' not found" but I don't even query for it
PostPosted: Wed Jun 05, 2013 10:21 am 
Newbie

Joined: Wed Jun 05, 2013 10:17 am
Posts: 1
Yeah This is a bug. Please keep alias and column name same. As suggested by monikasood
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 7 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.