[ http://issues.apache.org/jira/browse/OJB-50?page=comments#action_12357492 ]
Jakob Braeuchi commented on OJB-50: ----------------------------------- the problem is caused by the key used to identify the table alias. ojb used the path only and therefore could not distinguish between 'qualifiers' for Topic.class and 'qualifiers' for Category.class. as a result there's only one alias for 'qualifiers'. when adding the path class hints to the key the generated sql is as follows: SELECT DISTINCT A0.ID,A0.HEADLINE FROM NEWS A0 LEFT OUTER JOIN CONTENT_QUALIFIER A1 ON A0.ID=A1.CONTENT_ID LEFT OUTER JOIN TOPIC A2 ON A1.QUALIFIER_ID=A2.ID LEFT OUTER JOIN CATEGORY A3 ON A1.QUALIFIER_ID=A3.ID WHERE A2.IMPORTANCE = ? OR (A3.DESCRIPTION IS NOT NULL ) > Wrong SQL is generated for sub-criterias using the same path but with > different path classes > -------------------------------------------------------------------------------------------- > > Key: OJB-50 > URL: http://issues.apache.org/jira/browse/OJB-50 > Project: OJB > Type: Bug > Components: PB-API > Versions: 1.0.x CVS > Reporter: Thomas Dudziak > Assignee: Jakob Braeuchi > Fix For: 1.0.x CVS > > A complex criteria that is constructed using sub-criterias which reference > the same path but with different path classes, generates wrong SQL for the > JOIN. I've added a unit test for this scenario > (testComplexCriteriaWithPathClasses in ExtentAwarePathExpressionsTest) with > adjusted Category and Topic classes: > public void testComplexCriteriaWithPathClasses() > { > Criteria criteria = new Criteria(); > Criteria categoryCriteria = new Criteria(); > Criteria topicCriteria = new Criteria(); > topicCriteria.addEqualTo("qualifiers.importance", "important"); > topicCriteria.addPathClass("qualifiers", Topic.class); > criteria.addOrCriteria(topicCriteria); > categoryCriteria.addNotNull("qualifiers.description"); > categoryCriteria.addPathClass("qualifiers", Category.class); > criteria.addOrCriteria(categoryCriteria); > QueryByCriteria query = new QueryByCriteria(BaseContentImpl.class, > criteria, true); > List content = (List)broker.getCollectionByQuery(query); > assertEquals(1, content.size()); > assertEquals(2, ((News)content.get(0)).getId()); > } > The SQL exception is : > java.sql.SQLException: Column not found: DESCRIPTION in statement [SELECT > DISTINCT A0.ID,A0.HEADLINE FROM NEWS A0 LEFT OUTER JOIN CONTENT_QUALIFIER A1 > ON A0.ID=A1.CONTENT_ID LEFT OUTER JOIN TOPIC A2 ON A1.QUALIFIER_ID=A2.ID > WHERE A2.IMPORTANCE = 'important' OR (description IS NOT NULL )] > at org.hsqldb.Trace.getError(Unknown Source) > at org.hsqldb.jdbcResultSet.<init>(Unknown Source) > at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source) > at org.hsqldb.jdbcConnection.execute(Unknown Source) > at org.hsqldb.jdbcStatement.fetchResult(Unknown Source) > at org.hsqldb.jdbcStatement.executeQuery(Unknown Source) > at org.hsqldb.jdbcPreparedStatement.executeQuery(Unknown Source) > at > org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:345) > at > org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72) > at > org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:185) > at > org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIteratorFactoryImpl.java:58) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2064) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(PersistenceBrokerImpl.java:1592) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:116) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:255) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:275) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1448) > at > org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:366) > at > org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:366) > at > org.apache.ojb.broker.ExtentAwarePathExpressionsTest.testComplexCriteriaWithPathClasses(ExtentAwarePathExpressionsTest.java:227) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:585) > at junit.framework.TestCase.runTest(TestCase.java:154) > at junit.framework.TestCase.runBare(TestCase.java:127) > at junit.framework.TestResult$1.protect(TestResult.java:106) > at junit.framework.TestResult.runProtected(TestResult.java:124) > at junit.framework.TestResult.run(TestResult.java:109) > at junit.framework.TestCase.run(TestCase.java:118) > at junit.framework.TestSuite.runTest(TestSuite.java:208) > at junit.framework.TestSuite.run(TestSuite.java:203) > at junit.framework.TestSuite.runTest(TestSuite.java:208) > at junit.framework.TestSuite.run(TestSuite.java:203) > at > org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:289) > at > org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:656) > at > org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:558) > org.apache.ojb.broker.PersistenceBrokerSQLException: SQLException during the > execution of the query (for org.apache.ojb.broker.News): Column not found: > DESCRIPTION in statement [SELECT DISTINCT A0.ID,A0.HEADLINE FROM NEWS A0 LEFT > OUTER JOIN CONTENT_QUALIFIER A1 ON A0.ID=A1.CONTENT_ID LEFT OUTER JOIN TOPIC > A2 ON A1.QUALIFIER_ID=A2.ID WHERE A2.IMPORTANCE = 'important' OR > (description IS NOT NULL )] > at > org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:382) > at > org.apache.ojb.broker.accesslayer.RsQueryObject.performQuery(RsQueryObject.java:72) > at > org.apache.ojb.broker.accesslayer.RsIterator.<init>(RsIterator.java:185) > at > org.apache.ojb.broker.core.RsIteratorFactoryImpl.createRsIterator(RsIteratorFactoryImpl.java:58) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getRsIteratorFromQuery(PersistenceBrokerImpl.java:2064) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getIteratorFromQuery(PersistenceBrokerImpl.java:1592) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:116) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:255) > at > org.apache.ojb.broker.core.QueryReferenceBroker.getCollectionByQuery(QueryReferenceBroker.java:275) > at > org.apache.ojb.broker.core.PersistenceBrokerImpl.getCollectionByQuery(PersistenceBrokerImpl.java:1448) > at > org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:366) > at > org.apache.ojb.broker.core.DelegatingPersistenceBroker.getCollectionByQuery(DelegatingPersistenceBroker.java:366) > at > org.apache.ojb.broker.ExtentAwarePathExpressionsTest.testComplexCriteriaWithPathClasses(ExtentAwarePathExpressionsTest.java:227) > at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) > at > sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39) > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25) > at java.lang.reflect.Method.invoke(Method.java:585) > at junit.framework.TestCase.runTest(TestCase.java:154) > at junit.framework.TestCase.runBare(TestCase.java:127) > at junit.framework.TestResult$1.protect(TestResult.java:106) > at junit.framework.TestResult.runProtected(TestResult.java:124) > at junit.framework.TestResult.run(TestResult.java:109) > at junit.framework.TestCase.run(TestCase.java:118) > at junit.framework.TestSuite.runTest(TestSuite.java:208) > at junit.framework.TestSuite.run(TestSuite.java:203) > at junit.framework.TestSuite.runTest(TestSuite.java:208) > at junit.framework.TestSuite.run(TestSuite.java:203) > at > org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.run(JUnitTestRunner.java:289) > at > org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.launch(JUnitTestRunner.java:656) > at > org.apache.tools.ant.taskdefs.optional.junit.JUnitTestRunner.main(JUnitTestRunner.java:558) > Caused by: java.sql.SQLException: Column not found: DESCRIPTION in statement > [SELECT DISTINCT A0.ID,A0.HEADLINE FROM NEWS A0 LEFT OUTER JOIN > CONTENT_QUALIFIER A1 ON A0.ID=A1.CONTENT_ID LEFT OUTER JOIN TOPIC A2 ON > A1.QUALIFIER_ID=A2.ID WHERE A2.IMPORTANCE = 'important' OR (description IS > NOT NULL )] > at org.hsqldb.Trace.getError(Unknown Source) > at org.hsqldb.jdbcResultSet.<init>(Unknown Source) > at org.hsqldb.jdbcConnection.executeStandalone(Unknown Source) > at org.hsqldb.jdbcConnection.execute(Unknown Source) > at org.hsqldb.jdbcStatement.fetchResult(Unknown Source) > at org.hsqldb.jdbcStatement.executeQuery(Unknown Source) > at org.hsqldb.jdbcPreparedStatement.executeQuery(Unknown Source) > at > org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeQuery(JdbcAccessImpl.java:345) > ... 29 more -- This message is automatically generated by JIRA. - If you think it was sent incorrectly contact one of the administrators: http://issues.apache.org/jira/secure/Administrators.jspa - For more information on JIRA, see: http://www.atlassian.com/software/jira --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
