You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@cayenne.apache.org by "Bryan Lewis (JIRA)" <ji...@apache.org> on 2009/06/29 17:12:47 UTC
[jira] Created: (CAY-1247) Query SQL syntax with join and OR on
Oracle8 (and maybe OpenBase)
Query SQL syntax with join and OR on Oracle8 (and maybe OpenBase)
-----------------------------------------------------------------
Key: CAY-1247
URL: https://issues.apache.org/jira/browse/CAY-1247
Project: Cayenne
Issue Type: Bug
Components: Cayenne Core Library
Affects Versions: 3.0M6
Environment: Oracle 8
Reporter: Bryan Lewis
Priority: Minor
See the thread at http://news.gmane.org/gmane.comp.java.cayenne.user/11127.
The Oracle8 adapter produces legacy syntax for joins. We found that queries that involve both a join and an orExp produce SQL with insufficient parentheses. It appears that Oracle applies the OR to the join clause and fetches the entire table. Cayenne2 supplied an extra pair of parentheses to avoid this. I've fixed the code for our purposes (will append in a moment).
Perhaps this will be an issue on OpenBase too, since they use the same legacy join code. Or maybe Openbase will handle the ungrouped AND's and OR's more intelligently.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (CAY-1247) Query SQL syntax with join and OR on
Oracle8 (and maybe OpenBase)
Posted by "Bryan Lewis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1247?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12725214#action_12725214 ]
Bryan Lewis commented on CAY-1247:
----------------------------------
In Oracle8Adapter I return a new Oracle8QualifierTranslator:
---
@Override
public QualifierTranslator getQualifierTranslator(QueryAssembler queryAssembler)
{
return new Oracle8QualifierTranslator(queryAssembler);
}
---
The new Oracle8QualifierTranslator class:
---
package org.apache.cayenne.dba.oracle;
import java.io.IOException;
import org.apache.cayenne.CayenneRuntimeException;
import org.apache.cayenne.access.trans.QueryAssembler;
import org.apache.cayenne.access.trans.TrimmingQualifierTranslator;
import org.apache.cayenne.exp.Expression;
/**
* This extends the TrimmingQualifierTranslator that Cayenne normally uses
* for Oracle. It overrides doAppendPart() to wrap the qualifierBuffer
* in parentheses if it contains an "OR" expression. This avoids a bug that
* can happen on Oracle8 if the query also contains a join.
*/
public class Oracle8QualifierTranslator extends TrimmingQualifierTranslator {
public Oracle8QualifierTranslator(QueryAssembler queryAssembler) {
super(queryAssembler, OracleAdapter.TRIM_FUNCTION);
}
@Override
protected void doAppendPart() throws IOException {
super.doAppendPart();
if (out instanceof StringBuilder) {
StringBuilder buffer = (StringBuilder) out;
if (buffer.indexOf(" OR ") != -1) {
buffer.insert(0, '(');
buffer.append(')');
}
}
}
}
---
Sorry about that "instanceof", kinda ugly. The Appendable doesn't allow insert(), and for some reason I didn't pursue, I couldn't call setOut() to pass in a new StringBuilder.
> Query SQL syntax with join and OR on Oracle8 (and maybe OpenBase)
> -----------------------------------------------------------------
>
> Key: CAY-1247
> URL: https://issues.apache.org/jira/browse/CAY-1247
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0M6
> Environment: Oracle 8
> Reporter: Bryan Lewis
> Priority: Minor
>
> See the thread at http://news.gmane.org/gmane.comp.java.cayenne.user/11127.
> The Oracle8 adapter produces legacy syntax for joins. We found that queries that involve both a join and an orExp produce SQL with insufficient parentheses. It appears that Oracle applies the OR to the join clause and fetches the entire table. Cayenne2 supplied an extra pair of parentheses to avoid this. I've fixed the code for our purposes (will append in a moment).
> Perhaps this will be an issue on OpenBase too, since they use the same legacy join code. Or maybe Openbase will handle the ungrouped AND's and OR's more intelligently.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Commented: (CAY-1247) Query SQL syntax with join and OR on
Oracle8 (and maybe OpenBase)
Posted by "Bryan Lewis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1247?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12782899#action_12782899 ]
Bryan Lewis commented on CAY-1247:
----------------------------------
Tested. Works. Thanks.
> Query SQL syntax with join and OR on Oracle8 (and maybe OpenBase)
> -----------------------------------------------------------------
>
> Key: CAY-1247
> URL: https://issues.apache.org/jira/browse/CAY-1247
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0M6
> Environment: Oracle 8
> Reporter: Bryan Lewis
> Assignee: Andrus Adamchik
> Fix For: 3.0 beta 2, 3.1M1
>
>
> See the thread at http://news.gmane.org/gmane.comp.java.cayenne.user/11127.
> The Oracle8 adapter produces legacy syntax for joins. We found that queries that involve both a join and an orExp produce SQL with insufficient parentheses. It appears that Oracle applies the OR to the join clause and fetches the entire table. Cayenne2 supplied an extra pair of parentheses to avoid this. I've fixed the code for our purposes (will append in a moment).
> Perhaps this will be an issue on OpenBase too, since they use the same legacy join code. Or maybe Openbase will handle the ungrouped AND's and OR's more intelligently.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Closed: (CAY-1247) Query SQL syntax with join and OR on
Oracle8 (and maybe OpenBase)
Posted by "Andrus Adamchik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1247?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrus Adamchik closed CAY-1247.
--------------------------------
Resolution: Fixed
> Query SQL syntax with join and OR on Oracle8 (and maybe OpenBase)
> -----------------------------------------------------------------
>
> Key: CAY-1247
> URL: https://issues.apache.org/jira/browse/CAY-1247
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0M6
> Environment: Oracle 8
> Reporter: Bryan Lewis
> Assignee: Andrus Adamchik
> Fix For: 3.0 beta 2, 3.1
>
>
> See the thread at http://news.gmane.org/gmane.comp.java.cayenne.user/11127.
> The Oracle8 adapter produces legacy syntax for joins. We found that queries that involve both a join and an orExp produce SQL with insufficient parentheses. It appears that Oracle applies the OR to the join clause and fetches the entire table. Cayenne2 supplied an extra pair of parentheses to avoid this. I've fixed the code for our purposes (will append in a moment).
> Perhaps this will be an issue on OpenBase too, since they use the same legacy join code. Or maybe Openbase will handle the ungrouped AND's and OR's more intelligently.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (CAY-1247) Query SQL syntax with join and OR on
Oracle8 (and maybe OpenBase)
Posted by "Andrus Adamchik (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1247?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Andrus Adamchik updated CAY-1247:
---------------------------------
Fix Version/s: 3.1
3.0 beta 2
Assignee: Andrus Adamchik
I'll review the proposed fix and commit it to 3.0
> Query SQL syntax with join and OR on Oracle8 (and maybe OpenBase)
> -----------------------------------------------------------------
>
> Key: CAY-1247
> URL: https://issues.apache.org/jira/browse/CAY-1247
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0M6
> Environment: Oracle 8
> Reporter: Bryan Lewis
> Assignee: Andrus Adamchik
> Fix For: 3.0 beta 2, 3.1
>
>
> See the thread at http://news.gmane.org/gmane.comp.java.cayenne.user/11127.
> The Oracle8 adapter produces legacy syntax for joins. We found that queries that involve both a join and an orExp produce SQL with insufficient parentheses. It appears that Oracle applies the OR to the join clause and fetches the entire table. Cayenne2 supplied an extra pair of parentheses to avoid this. I've fixed the code for our purposes (will append in a moment).
> Perhaps this will be an issue on OpenBase too, since they use the same legacy join code. Or maybe Openbase will handle the ungrouped AND's and OR's more intelligently.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.
[jira] Updated: (CAY-1247) Query SQL syntax with join and OR on
Oracle8 (and maybe OpenBase)
Posted by "Bryan Lewis (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/CAY-1247?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Bryan Lewis updated CAY-1247:
-----------------------------
Priority: Major (was: Minor)
I boosted the Priority from Minor to Major, hope you don't mind. This is the only remaining bug that prevents us from using the standard cayenne jar on Oracle8. If you don't get to it, that's okay... I can fix it locally if we're the last Oracle8 users in the world.
> Query SQL syntax with join and OR on Oracle8 (and maybe OpenBase)
> -----------------------------------------------------------------
>
> Key: CAY-1247
> URL: https://issues.apache.org/jira/browse/CAY-1247
> Project: Cayenne
> Issue Type: Bug
> Components: Cayenne Core Library
> Affects Versions: 3.0M6
> Environment: Oracle 8
> Reporter: Bryan Lewis
>
> See the thread at http://news.gmane.org/gmane.comp.java.cayenne.user/11127.
> The Oracle8 adapter produces legacy syntax for joins. We found that queries that involve both a join and an orExp produce SQL with insufficient parentheses. It appears that Oracle applies the OR to the join clause and fetches the entire table. Cayenne2 supplied an extra pair of parentheses to avoid this. I've fixed the code for our purposes (will append in a moment).
> Perhaps this will be an issue on OpenBase too, since they use the same legacy join code. Or maybe Openbase will handle the ungrouped AND's and OR's more intelligently.
--
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.