db-ojb-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Jakob Braeuchi (JIRA)" <j...@apache.org>
Subject [jira] Commented: (OJB-50) Wrong SQL is generated for sub-criterias using the same path but with different path classes
Date Sat, 12 Nov 2005 18:05:22 GMT
    [ 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.&lt;init&gt;(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.&lt;init&gt;(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.&lt;init&gt;(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.&lt;init&gt;(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: ojb-dev-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-dev-help@db.apache.org


Mime
View raw message