ManyToMany alias not built in Criteria Query with max fetch depth of 0 ---------------------------------------------------------------------- Key: HHH-5067 URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-5067 Project: Hibernate Core Issue Type: Bug Components: query-criteria Affects Versions: 3.5.0-Final Environment: Hib 3.5.0 final, Oracle 10g Reporter: Shawn Clowater Criteria Queries that used to work in 3.3.2 no longer work in 3.5.0. They seem to be limited to queries that involve many-to-many associations and based on the fact that we have our Enviroment.MAX_FETCH_DEPTH set to 0 {code} DetachedCriteria detachedCriteria = DetachedCriteria.forClass(AgmtContractLine.class); detachedCriteria.createAlias("tradingGroups, "Ree"); detachedCriteria.add(Restrictions.eq("Ree.uuid", "333")); {code} Based on the simple Query, joining through the many to many produced the following in 3.3.2 {code} select this_.AGMT_CNTRCT_LINE_ID as AGMT1_24_1_, this_.UUID as UUID24_1_, this_.CREATE_DATE as CREATE3_24_1_, this_.CREATE_USER as CREATE4_24_1_, this_.UPDATE_DATE as UPDATE5_24_1_, this_.UPDATE_USER as UPDATE6_24_1_, this_.AGMT_CNTRCT_ID as AGMT7_24_1_, this_.NOTE_H_ID as NOTE8_24_1_, tradinggro3_.AGMT_CNTRCT_LINE_ID as AGMT1_, ree1_.TRADING_GROUP_ID as TRADING2_, ree1_.TRADING_GROUP_ID as TRADING1_324_0_, ree1_.UUID as UUID324_0_, ree1_.CREATE_DATE as CREATE3_324_0_, ree1_.CREATE_USER as CREATE4_324_0_, ree1_.UPDATE_DATE as UPDATE5_324_0_, ree1_.UPDATE_USER as UPDATE6_324_0_, ree1_.DESCRIPTION as DESCRIPT7_324_0_, ree1_.EXPIRY_DATE as EXPIRY8_324_0_, ree1_.SYSTEM_DEPENDANT_FLG as SYSTEM9_324_0_, ree1_.TRADING_GROUP_CODE as TRADING10_324_0_, ree1_.TRADING_GROUP_TYPE_ID as TRADING11_324_0_ from AGMT_CNTRCT_LINE this_ inner join AGMT_CNTRCT_LINE_GROUP_XREF tradinggro3_ on this_.AGMT_CNTRCT_LINE_ID=tradinggro3_.AGMT_CNTRCT_LINE_ID inner join TRADING_GROUP ree1_ on tradinggro3_.TRADING_GROUP_ID=ree1_.TRADING_GROUP_ID where ree1_.UUID=? {code} The same thing in 3.5.0 produced: {code} select this_.AGMT_CNTRCT_LINE_ID as AGMT1_25_0_, this_.UUID as UUID25_0_, this_.CREATE_DATE as CREATE3_25_0_, this_.CREATE_USER as CREATE4_25_0_, this_.UPDATE_DATE as UPDATE5_25_0_, this_.UPDATE_USER as UPDATE6_25_0_, this_.AGMT_CNTRCT_ID as AGMT7_25_0_, this_.NOTE_H_ID as NOTE8_25_0_, from AGMT_CNTRCT_LINE this_ inner join AGMT_CNTRCT_LINE_GROUP_XREF tradinggro3_ on this_.AGMT_CNTRCT_LINE_ID=tradinggro3_.AGMT_CNTRCT_LINE_ID where ree1_.UUID=? {code} Using the hib core tag I added the following test to org.hibernate.test.annotations.manytomany.ManyToManyTest {code} public void testCanUseCriteriaQuery() throws Exception { Session s; Transaction tx; s = openSession(); tx = s.beginTransaction(); Store fnac = new Store(); fnac.setName("Fnac"); Supplier emi = new Supplier(); emi.setName("Emmanuel"); emi.setSuppStores(new HashSet<Store>()); fnac.setSuppliers(new HashSet<Supplier>()); fnac.getSuppliers().add(emi); emi.getSuppStores().add(fnac); s.persist(fnac); tx.commit(); s.close(); s = openSession(); List result = DetachedCriteria.forClass(Supplier.class) .createAlias("suppStores", "s") .add(Restrictions.eq("s.name", "I don't even need a match")) .getExecutableCriteria(s) .list(); s.close(); assertEquals(0, result.size()); } {code} This will work fine unless the max fetch depth is set to 0 (i flipped it when the session factory is rebuilt in TestCase)at which point the following exception will be thrown. {code} 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.criteria.CriteriaLoader.list(CriteriaLoader.java:119) at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1706) at org.hibernate.impl.CriteriaImpl.list(CriteriaImpl.java:347) at org.hibernate.test.annotations.manytomany.ManyToManyTest.testCanUseCriteriaQuery(ManyToManyTest.java:185) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) at org.hibernate.test.annotations.HibernateTestCase.runTest(HibernateTestCase.java:112) at org.hibernate.test.annotations.HibernateTestCase.runBare(HibernateTestCase.java:100) at com.intellij.junit3.JUnit3IdeaTestRunner.doRun(JUnit3IdeaTestRunner.java:108) at com.intellij.junit3.JUnit3IdeaTestRunner.startRunnerWithArgs(JUnit3IdeaTestRunner.java:42) at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:165) at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:60) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) at com.intellij.rt.execution.application.AppMain.main(AppMain.java:110) Caused by: java.sql.SQLException: Unexpected token: FROM in statement [select this_.id as id8_0_, this_.name as name8_0_, from Supplier this_ inner join StoreSupplier suppstores3_ on this_.id=suppstores3_.supplier where s1_.name=?] at org.hsqldb.jdbc.Util.throwError(Unknown Source) at org.hsqldb.jdbc.jdbcPreparedStatement.<init>(Unknown Source) at org.hsqldb.jdbc.jdbcConnection.prepareStatement(Unknown Source) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534) at org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452) at org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161) at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1596) at org.hibernate.loader.Loader.doQuery(Loader.java:717) at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:270) at org.hibernate.loader.Loader.doList(Loader.java:2294) ... 28 more {code} -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://opensource.atlassian.com/projects/hibernate/secure/Administrators.... - For more information on JIRA, see: http://www.atlassian.com/software/jira