org.hibernate.exception.SQLGrammarException: could not extract ResultSet

Hibernate JIRA | Martin Šimka | 3 years ago
  1. 0

    org.hibernate.test.hql.WithClauseTest.testWithClause Stacktrace on db2-10 {noformat} org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61) at org.hibernate.loader.Loader.getResultSet(Loader.java:2040) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816) at org.hibernate.loader.Loader.doQuery(Loader.java:900) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342) at org.hibernate.loader.Loader.doList(Loader.java:2526) at org.hibernate.loader.Loader.doList(Loader.java:2512) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342) at org.hibernate.loader.Loader.list(Loader.java:2337) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1275) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) at org.hibernate.test.hql.WithClauseTest.testWithClause(WithClauseTest.java:129) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) at org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:63) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30) at org.junit.internal.runners.statements.FailOnTimeout$StatementThread.run(FailOnTimeout.java:62) Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127 at com.ibm.db2.jcc.am.id.a(id.java:677) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:127) at com.ibm.db2.jcc.am.no.c(no.java:2653) at com.ibm.db2.jcc.am.no.d(no.java:2641) at com.ibm.db2.jcc.am.no.a(no.java:2090) at com.ibm.db2.jcc.am.oo.a(oo.java:7671) at com.ibm.db2.jcc.t4.cb.h(cb.java:141) at com.ibm.db2.jcc.t4.cb.b(cb.java:41) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.sb.i(sb.java:135) at com.ibm.db2.jcc.am.no.ib(no.java:2059) at com.ibm.db2.jcc.am.oo.sc(oo.java:3559) at com.ibm.db2.jcc.am.oo.b(oo.java:4348) at com.ibm.db2.jcc.am.oo.fc(oo.java:743) at com.ibm.db2.jcc.am.oo.executeQuery(oo.java:713) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56) ... 27 more {noformat} Output on db2-10 {noformat} 12:50:28,641 DEBUG QueryTranslatorImpl:237 - HQL: from org.hibernate.test.hql.Human h inner join h.friends as f with f.nickName like 'bubba' 12:50:28,641 DEBUG QueryTranslatorImpl:238 - SQL: select human0_.mammal as id1_0_0_, human2_.mammal as id1_0_1_, human0_2_.description as descript2_0_0_, human0_2_.body_weight as body_wei3_0_0_, human0_2_.mother_id as mother_i4_0_0_, human0_2_.father_id as father_i5_0_0_, human0_2_.zoo_id as zoo_id6_0_0_, human0_2_.serialNumber as serialNu7_0_0_, human0_1_.pregnant as pregnant2_11_0_, human0_1_.birthdate as birthdat3_11_0_, human0_.name_first as name_fir2_4_0_, human0_.name_initial as name_ini3_4_0_, human0_.name_last as name_las4_4_0_, human0_.nickName as nickName5_4_0_, human0_.height_centimeters / 2.54E0 as height_c6_4_0_, human0_.intValue as intValue7_4_0_, human0_.floatValue as floatVal8_4_0_, human0_.bigDecimalValue as bigDecim9_4_0_, human0_.bigIntegerValue as bigInte10_4_0_, human2_2_.description as descript2_0_1_, human2_2_.body_weight as body_wei3_0_1_, human2_2_.mother_id as mother_i4_0_1_, human2_2_.father_id as father_i5_0_1_, human2_2_.zoo_id as zoo_id6_0_1_, human2_2_.serialNumber as serialNu7_0_1_, human2_1_.pregnant as pregnant2_11_1_, human2_1_.birthdate as birthdat3_11_1_, human2_.name_first as name_fir2_4_1_, human2_.name_initial as name_ini3_4_1_, human2_.name_last as name_las4_4_1_, human2_.nickName as nickName5_4_1_, human2_.height_centimeters / 2.54E0 as height_c6_4_1_, human2_.intValue as intValue7_4_1_, human2_.floatValue as floatVal8_4_1_, human2_.bigDecimalValue as bigDecim9_4_1_, human2_.bigIntegerValue as bigInte10_4_1_ from Human human0_ inner join Mammal human0_1_ on human0_.mammal=human0_1_.animal inner join Animal human0_2_ on human0_.mammal=human0_2_.id inner join Human_friends friends1_ on human0_.mammal=friends1_.human1 and (human2_.nickName like 'bubba') inner join Human human2_ on friends1_.human2=human2_.mammal and (human2_.nickName like 'bubba') inner join Mammal human2_1_ on human2_.mammal=human2_1_.animal inner join Animal human2_2_ on human2_.mammal=human2_2_.id 12:50:28,641 DEBUG ErrorCounter:82 - throwQueryException() : no errors 12:50:28,642 DEBUG SQL:104 - select human0_.mammal as id1_0_0_, human2_.mammal as id1_0_1_, human0_2_.description as descript2_0_0_, human0_2_.body_weight as body_wei3_0_0_, human0_2_.mother_id as mother_i4_0_0_, human0_2_.father_id as father_i5_0_0_, human0_2_.zoo_id as zoo_id6_0_0_, human0_2_.serialNumber as serialNu7_0_0_, human0_1_.pregnant as pregnant2_11_0_, human0_1_.birthdate as birthdat3_11_0_, human0_.name_first as name_fir2_4_0_, human0_.name_initial as name_ini3_4_0_, human0_.name_last as name_las4_4_0_, human0_.nickName as nickName5_4_0_, human0_.height_centimeters / 2.54E0 as height_c6_4_0_, human0_.intValue as intValue7_4_0_, human0_.floatValue as floatVal8_4_0_, human0_.bigDecimalValue as bigDecim9_4_0_, human0_.bigIntegerValue as bigInte10_4_0_, human2_2_.description as descript2_0_1_, human2_2_.body_weight as body_wei3_0_1_, human2_2_.mother_id as mother_i4_0_1_, human2_2_.father_id as father_i5_0_1_, human2_2_.zoo_id as zoo_id6_0_1_, human2_2_.serialNumber as serialNu7_0_1_, human2_1_.pregnant as pregnant2_11_1_, human2_1_.birthdate as birthdat3_11_1_, human2_.name_first as name_fir2_4_1_, human2_.name_initial as name_ini3_4_1_, human2_.name_last as name_las4_4_1_, human2_.nickName as nickName5_4_1_, human2_.height_centimeters / 2.54E0 as height_c6_4_1_, human2_.intValue as intValue7_4_1_, human2_.floatValue as floatVal8_4_1_, human2_.bigDecimalValue as bigDecim9_4_1_, human2_.bigIntegerValue as bigInte10_4_1_ from Human human0_ inner join Mammal human0_1_ on human0_.mammal=human0_1_.animal inner join Animal human0_2_ on human0_.mammal=human0_2_.id inner join Human_friends friends1_ on human0_.mammal=friends1_.human1 and (human2_.nickName like 'bubba') inner join Human human2_ on friends1_.human2=human2_.mammal and (human2_.nickName like 'bubba') inner join Mammal human2_1_ on human2_.mammal=human2_1_.animal inner join Animal human2_2_ on human2_.mammal=human2_2_.id 12:50:28,673 WARN SqlExceptionHelper:145 - SQL Error: -206, SQLState: 42703 12:50:28,673 ERROR SqlExceptionHelper:147 - DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127 12:50:28,673 WARN SqlExceptionHelper:145 - SQL Error: -206, SQLState: 42703 12:50:28,674 ERROR SqlExceptionHelper:147 - DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127 12:50:28,674 WARN SqlExceptionHelper:145 - SQL Error: -727, SQLState: 56098 12:50:28,674 ERROR SqlExceptionHelper:147 - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;HUMAN2_.NICKNAME, DRIVER=4.13.127 {noformat} Generated sql that causes error: {code:sql} select human0_.mammal as id1_0_0_, human2_.mammal as id1_0_1_, human0_2_.description as descript2_0_0_, human0_2_.body_weight as body_wei3_0_0_, human0_2_.mother_id as mother_i4_0_0_, human0_2_.father_id as father_i5_0_0_, human0_2_.zoo_id as zoo_id6_0_0_, human0_2_.serialNumber as serialNu7_0_0_, human0_1_.pregnant as pregnant2_11_0_, human0_1_.birthdate as birthdat3_11_0_, human0_.name_first as name_fir2_4_0_, human0_.name_initial as name_ini3_4_0_, human0_.name_last as name_las4_4_0_, human0_.nickName as nickName5_4_0_, human0_.height_centimeters / 2.54E0 as height_c6_4_0_, human0_.intValue as intValue7_4_0_, human0_.floatValue as floatVal8_4_0_, human0_.bigDecimalValue as bigDecim9_4_0_, human0_.bigIntegerValue as bigInte10_4_0_, human2_2_.description as descript2_0_1_, human2_2_.body_weight as body_wei3_0_1_, human2_2_.mother_id as mother_i4_0_1_, human2_2_.father_id as father_i5_0_1_, human2_2_.zoo_id as zoo_id6_0_1_, human2_2_.serialNumber as serialNu7_0_1_, human2_1_.pregnant as pregnant2_11_1_, human2_1_.birthdate as birthdat3_11_1_, human2_.name_first as name_fir2_4_1_, human2_.name_initial as name_ini3_4_1_, human2_.name_last as name_las4_4_1_, human2_.nickName as nickName5_4_1_, human2_.height_centimeters / 2.54E0 as height_c6_4_1_, human2_.intValue as intValue7_4_1_, human2_.floatValue as floatVal8_4_1_, human2_.bigDecimalValue as bigDecim9_4_1_, human2_.bigIntegerValue as bigInte10_4_1_ from Human human0_ inner join Mammal human0_1_ on human0_.mammal=human0_1_.animal inner join Animal human0_2_ on human0_.mammal=human0_2_.id inner join Human_friends friends1_ on human0_.mammal=friends1_.human1 and human2_.nickName like 'bubba' -- !!! there is problem inner join Human human2_ on friends1_.human2=human2_.mammal and human2_.nickName like 'bubba' inner join Mammal human2_1_ on human2_.mammal=human2_1_.animal inner join Animal human2_2_ on human2_.mammal=human2_2_.id {code} If I comment "_and human2_.nickName like 'bubba'_" sql query works.

    Hibernate JIRA | 3 years ago | Martin Šimka
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  2. 0

    org.hibernate.test.hql.WithClauseTest.testWithClause Stacktrace on db2-10 {noformat} org.hibernate.exception.SQLGrammarException: could not extract ResultSet at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:82) at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:49) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125) at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:61) at org.hibernate.loader.Loader.getResultSet(Loader.java:2040) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837) at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816) at org.hibernate.loader.Loader.doQuery(Loader.java:900) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342) at org.hibernate.loader.Loader.doList(Loader.java:2526) at org.hibernate.loader.Loader.doList(Loader.java:2512) at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342) at org.hibernate.loader.Loader.list(Loader.java:2337) at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495) at org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357) at org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195) at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1275) at org.hibernate.internal.QueryImpl.list(QueryImpl.java:101) at org.hibernate.test.hql.WithClauseTest.testWithClause(WithClauseTest.java:129) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:606) at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45) at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15) at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42) at org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:63) at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20) at org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28) at org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30) at org.junit.internal.runners.statements.FailOnTimeout$StatementThread.run(FailOnTimeout.java:62) Caused by: com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127 at com.ibm.db2.jcc.am.id.a(id.java:677) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:127) at com.ibm.db2.jcc.am.no.c(no.java:2653) at com.ibm.db2.jcc.am.no.d(no.java:2641) at com.ibm.db2.jcc.am.no.a(no.java:2090) at com.ibm.db2.jcc.am.oo.a(oo.java:7671) at com.ibm.db2.jcc.t4.cb.h(cb.java:141) at com.ibm.db2.jcc.t4.cb.b(cb.java:41) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.sb.i(sb.java:135) at com.ibm.db2.jcc.am.no.ib(no.java:2059) at com.ibm.db2.jcc.am.oo.sc(oo.java:3559) at com.ibm.db2.jcc.am.oo.b(oo.java:4348) at com.ibm.db2.jcc.am.oo.fc(oo.java:743) at com.ibm.db2.jcc.am.oo.executeQuery(oo.java:713) at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56) ... 27 more {noformat} Output on db2-10 {noformat} 12:50:28,641 DEBUG QueryTranslatorImpl:237 - HQL: from org.hibernate.test.hql.Human h inner join h.friends as f with f.nickName like 'bubba' 12:50:28,641 DEBUG QueryTranslatorImpl:238 - SQL: select human0_.mammal as id1_0_0_, human2_.mammal as id1_0_1_, human0_2_.description as descript2_0_0_, human0_2_.body_weight as body_wei3_0_0_, human0_2_.mother_id as mother_i4_0_0_, human0_2_.father_id as father_i5_0_0_, human0_2_.zoo_id as zoo_id6_0_0_, human0_2_.serialNumber as serialNu7_0_0_, human0_1_.pregnant as pregnant2_11_0_, human0_1_.birthdate as birthdat3_11_0_, human0_.name_first as name_fir2_4_0_, human0_.name_initial as name_ini3_4_0_, human0_.name_last as name_las4_4_0_, human0_.nickName as nickName5_4_0_, human0_.height_centimeters / 2.54E0 as height_c6_4_0_, human0_.intValue as intValue7_4_0_, human0_.floatValue as floatVal8_4_0_, human0_.bigDecimalValue as bigDecim9_4_0_, human0_.bigIntegerValue as bigInte10_4_0_, human2_2_.description as descript2_0_1_, human2_2_.body_weight as body_wei3_0_1_, human2_2_.mother_id as mother_i4_0_1_, human2_2_.father_id as father_i5_0_1_, human2_2_.zoo_id as zoo_id6_0_1_, human2_2_.serialNumber as serialNu7_0_1_, human2_1_.pregnant as pregnant2_11_1_, human2_1_.birthdate as birthdat3_11_1_, human2_.name_first as name_fir2_4_1_, human2_.name_initial as name_ini3_4_1_, human2_.name_last as name_las4_4_1_, human2_.nickName as nickName5_4_1_, human2_.height_centimeters / 2.54E0 as height_c6_4_1_, human2_.intValue as intValue7_4_1_, human2_.floatValue as floatVal8_4_1_, human2_.bigDecimalValue as bigDecim9_4_1_, human2_.bigIntegerValue as bigInte10_4_1_ from Human human0_ inner join Mammal human0_1_ on human0_.mammal=human0_1_.animal inner join Animal human0_2_ on human0_.mammal=human0_2_.id inner join Human_friends friends1_ on human0_.mammal=friends1_.human1 and (human2_.nickName like 'bubba') inner join Human human2_ on friends1_.human2=human2_.mammal and (human2_.nickName like 'bubba') inner join Mammal human2_1_ on human2_.mammal=human2_1_.animal inner join Animal human2_2_ on human2_.mammal=human2_2_.id 12:50:28,641 DEBUG ErrorCounter:82 - throwQueryException() : no errors 12:50:28,642 DEBUG SQL:104 - select human0_.mammal as id1_0_0_, human2_.mammal as id1_0_1_, human0_2_.description as descript2_0_0_, human0_2_.body_weight as body_wei3_0_0_, human0_2_.mother_id as mother_i4_0_0_, human0_2_.father_id as father_i5_0_0_, human0_2_.zoo_id as zoo_id6_0_0_, human0_2_.serialNumber as serialNu7_0_0_, human0_1_.pregnant as pregnant2_11_0_, human0_1_.birthdate as birthdat3_11_0_, human0_.name_first as name_fir2_4_0_, human0_.name_initial as name_ini3_4_0_, human0_.name_last as name_las4_4_0_, human0_.nickName as nickName5_4_0_, human0_.height_centimeters / 2.54E0 as height_c6_4_0_, human0_.intValue as intValue7_4_0_, human0_.floatValue as floatVal8_4_0_, human0_.bigDecimalValue as bigDecim9_4_0_, human0_.bigIntegerValue as bigInte10_4_0_, human2_2_.description as descript2_0_1_, human2_2_.body_weight as body_wei3_0_1_, human2_2_.mother_id as mother_i4_0_1_, human2_2_.father_id as father_i5_0_1_, human2_2_.zoo_id as zoo_id6_0_1_, human2_2_.serialNumber as serialNu7_0_1_, human2_1_.pregnant as pregnant2_11_1_, human2_1_.birthdate as birthdat3_11_1_, human2_.name_first as name_fir2_4_1_, human2_.name_initial as name_ini3_4_1_, human2_.name_last as name_las4_4_1_, human2_.nickName as nickName5_4_1_, human2_.height_centimeters / 2.54E0 as height_c6_4_1_, human2_.intValue as intValue7_4_1_, human2_.floatValue as floatVal8_4_1_, human2_.bigDecimalValue as bigDecim9_4_1_, human2_.bigIntegerValue as bigInte10_4_1_ from Human human0_ inner join Mammal human0_1_ on human0_.mammal=human0_1_.animal inner join Animal human0_2_ on human0_.mammal=human0_2_.id inner join Human_friends friends1_ on human0_.mammal=friends1_.human1 and (human2_.nickName like 'bubba') inner join Human human2_ on friends1_.human2=human2_.mammal and (human2_.nickName like 'bubba') inner join Mammal human2_1_ on human2_.mammal=human2_1_.animal inner join Animal human2_2_ on human2_.mammal=human2_2_.id 12:50:28,673 WARN SqlExceptionHelper:145 - SQL Error: -206, SQLState: 42703 12:50:28,673 ERROR SqlExceptionHelper:147 - DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127 12:50:28,673 WARN SqlExceptionHelper:145 - SQL Error: -206, SQLState: 42703 12:50:28,674 ERROR SqlExceptionHelper:147 - DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127 12:50:28,674 WARN SqlExceptionHelper:145 - SQL Error: -727, SQLState: 56098 12:50:28,674 ERROR SqlExceptionHelper:147 - DB2 SQL Error: SQLCODE=-727, SQLSTATE=56098, SQLERRMC=2;-206;42703;HUMAN2_.NICKNAME, DRIVER=4.13.127 {noformat} Generated sql that causes error: {code:sql} select human0_.mammal as id1_0_0_, human2_.mammal as id1_0_1_, human0_2_.description as descript2_0_0_, human0_2_.body_weight as body_wei3_0_0_, human0_2_.mother_id as mother_i4_0_0_, human0_2_.father_id as father_i5_0_0_, human0_2_.zoo_id as zoo_id6_0_0_, human0_2_.serialNumber as serialNu7_0_0_, human0_1_.pregnant as pregnant2_11_0_, human0_1_.birthdate as birthdat3_11_0_, human0_.name_first as name_fir2_4_0_, human0_.name_initial as name_ini3_4_0_, human0_.name_last as name_las4_4_0_, human0_.nickName as nickName5_4_0_, human0_.height_centimeters / 2.54E0 as height_c6_4_0_, human0_.intValue as intValue7_4_0_, human0_.floatValue as floatVal8_4_0_, human0_.bigDecimalValue as bigDecim9_4_0_, human0_.bigIntegerValue as bigInte10_4_0_, human2_2_.description as descript2_0_1_, human2_2_.body_weight as body_wei3_0_1_, human2_2_.mother_id as mother_i4_0_1_, human2_2_.father_id as father_i5_0_1_, human2_2_.zoo_id as zoo_id6_0_1_, human2_2_.serialNumber as serialNu7_0_1_, human2_1_.pregnant as pregnant2_11_1_, human2_1_.birthdate as birthdat3_11_1_, human2_.name_first as name_fir2_4_1_, human2_.name_initial as name_ini3_4_1_, human2_.name_last as name_las4_4_1_, human2_.nickName as nickName5_4_1_, human2_.height_centimeters / 2.54E0 as height_c6_4_1_, human2_.intValue as intValue7_4_1_, human2_.floatValue as floatVal8_4_1_, human2_.bigDecimalValue as bigDecim9_4_1_, human2_.bigIntegerValue as bigInte10_4_1_ from Human human0_ inner join Mammal human0_1_ on human0_.mammal=human0_1_.animal inner join Animal human0_2_ on human0_.mammal=human0_2_.id inner join Human_friends friends1_ on human0_.mammal=friends1_.human1 and human2_.nickName like 'bubba' -- !!! there is problem inner join Human human2_ on friends1_.human2=human2_.mammal and human2_.nickName like 'bubba' inner join Mammal human2_1_ on human2_.mammal=human2_1_.animal inner join Animal human2_2_ on human2_.mammal=human2_2_.id {code} If I comment "_and human2_.nickName like 'bubba'_" sql query works.

    Hibernate JIRA | 3 years ago | Martin Šimka
    org.hibernate.exception.SQLGrammarException: could not extract ResultSet
  3. 0

    com.ibm.db2.jcc.am.SqlSyntaxErrorException when using PESSIMISTIC_READ with first/max results

    Eclipse Bugzilla | 2 years ago | curtisr7
    com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=READ ONLY WITH RS USE AND KEEP UPDATE LOCK;FROM LOCKINGDOG FOR;<space>, DRIVER=4.13.127 Error Code: -104 Call: SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT ID AS a1, NAME AS a2, VERSION AS a3 FROM LOCKINGDOG FOR READ ONLY WITH RS USE AND KEEP UPDATE LOCKS) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ? bind => [2, 1] Query: ReadAllQuery(name="find.lockingdogs" referenceClass=LockingDog sql="SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT ID AS a1, NAME AS a2, VERSION AS a3 FROM LOCKINGDOG FOR READ ONLY WITH RS USE AND KEEP UPDATE LOCKS) AS EL_TEMP) AS EL_TEMP2 WHERE EL_ROWNM <= ?) AS EL_TEMP3 WHERE EL_ROWNM > ?")
  4. Speed up your debug routine!

    Automated exception search integrated into your IDE

  5. 0

    To reproduce this issue: 1. Create more than 20 job executions in the job repository. 2. From spring batch admin web application, select Executions. 3. Click Next page. An error screen will show up. Root cause: The SQL generated by the Db2PagingQueryProvider (SqlWindowingPagingQueryProvider) is: SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC The sortKey for this paginate query is "E.JOB_EXECUTION_ID". And the SQL seems to have problem on the field name since the table alias is only applicable for the subquery instead of the outer main query. Correct SQL should be: SELECT TMP_SUB.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY TMP_SUB.JOB_EXECUTION_ID DESC Attached stack trace for the exception: 17:55:05,750 DEBUG http-bio-8080-exec-7 support.SQLErrorCodesFactory:175 - SQL error codes for 'DB2/LINUXX8664' found 17:55:05,760 DEBUG http-bio-8080-exec-7 support.SQLErrorCodeSQLExceptionTranslator:403 - Translating SQLException with SQL state '42703', error code '-206', message [DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80]; SQL was [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC] for task [StatementCallback] Aug 27, 2013 5:55:05 PM org.apache.catalina.core.StandardWrapperValve invoke SEVERE: Servlet.service() for servlet [Batch Servlet] in context with path [/batchsampleweb] threw exception [Request processing failed; nested exception is org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [SELECT E.JOB_EXECUTION_ID FROM ( SELECT E.JOB_EXECUTION_ID, ROW_NUMBER() OVER ( ORDER BY E.JOB_EXECUTION_ID DESC) AS ROW_NUMBER FROM SPSSAMPLE.BATCH_JOB_EXECUTION E, SPSSAMPLE.BATCH_JOB_INSTANCE I WHERE E.JOB_INSTANCE_ID=I.JOB_INSTANCE_ID) AS TMP_SUB WHERE TMP_SUB.ROW_NUMBER = 20 ORDER BY E.JOB_EXECUTION_ID DESC]; nested exception is com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80] with root cause com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80 at com.ibm.db2.jcc.am.id.a(id.java:677) at com.ibm.db2.jcc.am.id.a(id.java:60) at com.ibm.db2.jcc.am.id.a(id.java:127) at com.ibm.db2.jcc.am.fo.c(fo.java:2653) at com.ibm.db2.jcc.am.fo.d(fo.java:2641) at com.ibm.db2.jcc.am.fo.a(fo.java:2090) at com.ibm.db2.jcc.t4.cb.h(cb.java:141) at com.ibm.db2.jcc.t4.cb.b(cb.java:41) at com.ibm.db2.jcc.t4.q.a(q.java:32) at com.ibm.db2.jcc.t4.sb.i(sb.java:135) at com.ibm.db2.jcc.am.fo.ib(fo.java:2059) at com.ibm.db2.jcc.am.fo.a(fo.java:3130) at com.ibm.db2.jcc.am.fo.a(fo.java:688) at com.ibm.db2.jcc.am.fo.executeQuery(fo.java:672) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.apache.tomcat.dbcp.dbcp.DelegatingStatement.executeQuery(DelegatingStatement.java:208) at org.springframework.jdbc.core.JdbcTemplate$1QueryStatementCallback.doInStatement(JdbcTemplate.java:441) at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:396) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:456) at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:464) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:472) at org.springframework.jdbc.core.JdbcTemplate.queryForObject(JdbcTemplate.java:477) at org.springframework.batch.admin.service.JdbcSearchableJobExecutionDao.getJobExecutions(JdbcSearchableJobExecutionDao.java:191) at org.springframework.batch.admin.service.SimpleJobService.listJobExecutions(SimpleJobService.java:234) at org.springframework.batch.admin.web.JobExecutionController.list(JobExecutionController.java:161) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) at java.lang.reflect.Method.invoke(Method.java:601) at org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:219) at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:132) at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:104) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandleMethod(RequestMappingHandlerAdapter.java:745) at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:686) at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80) at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:925) at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:856) at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:936) at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:827) at javax.servlet.http.HttpServlet.service(HttpServlet.java:621) at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:812) at javax.servlet.http.HttpServlet.service(HttpServlet.java:728) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:305) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:77) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.springframework.web.filter.ShallowEtagHeaderFilter.doFilterInternal(ShallowEtagHeaderFilter.java:73) at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:243) at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:210) at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:222) at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:123) at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:472) at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:171) at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:99) at org.apache.catalina.valves.AccessLogValve.invoke(AccessLogValve.java:953) at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:118) at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:408) at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1008) at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:589) at org.apache.tomcat.util.net.JIoEndpoint$SocketProcessor.run(JIoEndpoint.java:312) at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145) at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615) at java.lang.Thread.run(Thread.java:722)

    Spring JIRA | 3 years ago | Miles Huang
    com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=E.JOB_EXECUTION_ID, DRIVER=4.13.80
  6. 0

    DB2: don't display UDF source : SQL0206N "SCHEMA" is not valid in the context where it is used.

    squirrel-sql | 3 years ago | georg-h
    com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=SCHEMA, DRIVER=3.65.77

    1 unregistered visitors
    Not finding the right solution?
    Take a tour to get the most out of Samebug.

    Tired of useless tips?

    Automated exception search integrated into your IDE

    Root Cause Analysis

    1. com.ibm.db2.jcc.am.SqlSyntaxErrorException

      DB2 SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=HUMAN2_.NICKNAME, DRIVER=4.13.127

      at com.ibm.db2.jcc.am.id.a()
    2. IBM DB2
      oo.executeQuery
      1. com.ibm.db2.jcc.am.id.a(id.java:677)
      2. com.ibm.db2.jcc.am.id.a(id.java:60)
      3. com.ibm.db2.jcc.am.id.a(id.java:127)
      4. com.ibm.db2.jcc.am.no.c(no.java:2653)
      5. com.ibm.db2.jcc.am.no.d(no.java:2641)
      6. com.ibm.db2.jcc.am.no.a(no.java:2090)
      7. com.ibm.db2.jcc.am.oo.a(oo.java:7671)
      8. com.ibm.db2.jcc.t4.cb.h(cb.java:141)
      9. com.ibm.db2.jcc.t4.cb.b(cb.java:41)
      10. com.ibm.db2.jcc.t4.q.a(q.java:32)
      11. com.ibm.db2.jcc.t4.sb.i(sb.java:135)
      12. com.ibm.db2.jcc.am.no.ib(no.java:2059)
      13. com.ibm.db2.jcc.am.oo.sc(oo.java:3559)
      14. com.ibm.db2.jcc.am.oo.b(oo.java:4348)
      15. com.ibm.db2.jcc.am.oo.fc(oo.java:743)
      16. com.ibm.db2.jcc.am.oo.executeQuery(oo.java:713)
      16 frames
    3. Hibernate
      QueryImpl.list
      1. org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:56)
      2. org.hibernate.loader.Loader.getResultSet(Loader.java:2040)
      3. org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1837)
      4. org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1816)
      5. org.hibernate.loader.Loader.doQuery(Loader.java:900)
      6. org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:342)
      7. org.hibernate.loader.Loader.doList(Loader.java:2526)
      8. org.hibernate.loader.Loader.doList(Loader.java:2512)
      9. org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2342)
      10. org.hibernate.loader.Loader.list(Loader.java:2337)
      11. org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:495)
      12. org.hibernate.hql.internal.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:357)
      13. org.hibernate.engine.query.spi.HQLQueryPlan.performList(HQLQueryPlan.java:195)
      14. org.hibernate.internal.SessionImpl.list(SessionImpl.java:1275)
      15. org.hibernate.internal.QueryImpl.list(QueryImpl.java:101)
      15 frames
    4. org.hibernate.test
      WithClauseTest.testWithClause
      1. org.hibernate.test.hql.WithClauseTest.testWithClause(WithClauseTest.java:129)
      1 frame
    5. Java RT
      Method.invoke
      1. sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      2. sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:57)
      3. sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
      4. java.lang.reflect.Method.invoke(Method.java:606)
      4 frames
    6. JUnit
      FrameworkMethod.invokeExplosively
      1. org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:45)
      2. org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:15)
      3. org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:42)
      3 frames
    7. A Hibernate O/RM Module
      ExtendedFrameworkMethod.invokeExplosively
      1. org.hibernate.testing.junit4.ExtendedFrameworkMethod.invokeExplosively(ExtendedFrameworkMethod.java:63)
      1 frame
    8. JUnit
      FailOnTimeout$StatementThread.run
      1. org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:20)
      2. org.junit.internal.runners.statements.RunBefores.evaluate(RunBefores.java:28)
      3. org.junit.internal.runners.statements.RunAfters.evaluate(RunAfters.java:30)
      4. org.junit.internal.runners.statements.FailOnTimeout$StatementThread.run(FailOnTimeout.java:62)
      4 frames