We're updating the issue view to help you get more done. 

WithClauseTest.testWithClause fails on all QA databases

Description

org.hibernate.test.hql.WithClauseTest.testWithClause

Stacktrace on db2-10

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 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

Output on db2-10

1 2 3 4 5 6 7 8 9 10 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

Generated sql that causes error:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 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

If I comment "and human2.nickName like 'bubba'_" sql query works.

Environment

None

Status

Assignee

Strong Liu

Reporter

Martin Šimka

Fix versions

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

4.2.7.SP1

Priority

Major