You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Øyvind Harboe <oy...@zylin.com> on 2006/08/17 09:24:20 UTC
Cayenne does not throw an exception when an outer join is attempted
Outer joins are not supported, I know.
Meanwhile I miss being *told* when I'm attempting an outer join,
minimally runtime.
One of the reasons I'm using Cayenne is that I don't want to think
about SQL, so I don't. It hurts even thinking about thinking about how
Cayenne Expressions turn into SQL statements :-)
Would it make sense for Cayenne to throw an exception when an outer
join is attempted?
Could I attempt to implement such a detection in Cayenne myself and
submit a patch or is it fiendishly difficult?
--
Øyvind Harboe
http://www.zylin.com
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Øyvind Harboe <oy...@zylin.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> On Aug 17, 2006, at 10:06 AM, Øyvind Harboe wrote:
>
> > I guess I don't understand, because I would say "always". If an outer
> > join was required for an Expression, but Cayenne didn't generate it,
> > then generate an Exception.
> >
> > Isn't it unambigous when an Expression as a Qualifier requires an
> > OUTER JOIN or some other SQL construct that Cayenne does not support?
>
> No it is not unambiguous. In most cases it is up to the user. This is
> why I am asking for examples to be able to identify various cases.
Is there a way to detect runtime that an Expression is ambigous?
Can you give me an example of an ambigous Expression?
I didn't intend any Expression that I wrote to be ambigious.
I normally run into problems with with outer joins when I orExp()
together complex generated Expressions.
E.g. if Foo has e.g. multiple fields referring to
Contact(toContactFrom, toContactTo) and I orExp together match against
toContactFrom & toContactTo as there are Foo objects without either
toContextFrom or toContactTo.
--
Øyvind Harboe
http://www.zylin.com
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 17, 2006, at 10:06 AM, Øyvind Harboe wrote:
> I guess I don't understand, because I would say "always". If an outer
> join was required for an Expression, but Cayenne didn't generate it,
> then generate an Exception.
>
> Isn't it unambigous when an Expression as a Qualifier requires an
> OUTER JOIN or some other SQL construct that Cayenne does not support?
No it is not unambiguous. In most cases it is up to the user. This is
why I am asking for examples to be able to identify various cases.
Andrus
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Øyvind Harboe <oy...@zylin.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > Would it make sense for Cayenne to throw an exception when an outer
> > join is attempted?
>
> Could you be more specific on when it should detect that? IIRC there
> were some discussions on that in the past. You provide no references
> though.
I guess I don't understand, because I would say "always". If an outer
join was required for an Expression, but Cayenne didn't generate it,
then generate an Exception.
Isn't it unambigous when an Expression as a Qualifier requires an
OUTER JOIN or some other SQL construct that Cayenne does not support?
> Also there is an effort underway to support outer joins (we will have
> to do it in 3.0 anyways as the goal is to be compatible with the JPA
> spec that requires them):
>
> http://objectstyle.org/cayenne/lists/cayenne-devel/2006/08/0106.html
Super!
--
Øyvind Harboe
http://www.zylin.com
Re: Outer join syntax
Posted by Mike Kienenberger <mk...@gmail.com>.
Being impatient, here's my dirty hack to make things work for me:
< IDENTIFIER: <LETTER> (<LETTER>|<DIGIT>)* >
to
< IDENTIFIER: <LETTER> (<LETTER>|<DIGIT>)* (["+"])? >
I know that's the wrong solution, but that's probably the best I can do.
On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > What I was thinking we can do
> > to simplify the semantics specification (and also make expressions
> > parsed from strings work the same way as expressions created via
> > ExpressionFactory) is add join type to the object path string. E.g.
> > use a "+" sign like Oracle did in the past:
> >
> > * Inner join: "toArtist"
> > * Left outer join: "toArtist+"
> > * Longer path with mixed joins: "toGallery+.toAddress"
> >
> > This seems like a reasonable non-verbose solution. What do you think?
>
> Ugh. This is getting more difficult each step. Apparently
> ExpressionParser doesn't like + as part of an attribute name. I was
> hoping I could simply slice that off the end of an path component
> string when found by PathIterator.next().
>
> I don't know if I have enough time to learn JJTree & JavaCC,
> particularly since I haven't done anything with compilers in 15 years.
> Any suggestions for me on this one?
>
> Caused by: org.objectstyle.cayenne.exp.parser.TokenMgrError: Lexical
> error at line 1, column 16. Encountered: "p" (112), after : "."
> at org.objectstyle.cayenne.exp.parser.ExpressionParserTokenManager.getNextToken(ExpressionParserTokenManager.java:1500)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.jj_ntk(ExpressionParser.java:1336)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.numericTerm(ExpressionParser.java:1049)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.multiplySubtractExp(ExpressionParser.java:973)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.scalarNumericExpression(ExpressionParser.java:916)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.scalarExpression(ExpressionParser.java:773)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.simpleCondition(ExpressionParser.java:236)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.notCondition(ExpressionParser.java:226)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.andCondition(ExpressionParser.java:134)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.orCondition(ExpressionParser.java:94)
> at org.objectstyle.cayenne.exp.parser.ExpressionParser.expression(ExpressionParser.java:87)
> at org.objectstyle.cayenne.exp.Expression.fromString(Expression.java:260)
> ... 16 more
>
Re: Outer join syntax
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> What I was thinking we can do
> to simplify the semantics specification (and also make expressions
> parsed from strings work the same way as expressions created via
> ExpressionFactory) is add join type to the object path string. E.g.
> use a "+" sign like Oracle did in the past:
>
> * Inner join: "toArtist"
> * Left outer join: "toArtist+"
> * Longer path with mixed joins: "toGallery+.toAddress"
>
> This seems like a reasonable non-verbose solution. What do you think?
Ugh. This is getting more difficult each step. Apparently
ExpressionParser doesn't like + as part of an attribute name. I was
hoping I could simply slice that off the end of an path component
string when found by PathIterator.next().
I don't know if I have enough time to learn JJTree & JavaCC,
particularly since I haven't done anything with compilers in 15 years.
Any suggestions for me on this one?
Caused by: org.objectstyle.cayenne.exp.parser.TokenMgrError: Lexical
error at line 1, column 16. Encountered: "p" (112), after : "."
at org.objectstyle.cayenne.exp.parser.ExpressionParserTokenManager.getNextToken(ExpressionParserTokenManager.java:1500)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.jj_ntk(ExpressionParser.java:1336)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.numericTerm(ExpressionParser.java:1049)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.multiplySubtractExp(ExpressionParser.java:973)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.scalarNumericExpression(ExpressionParser.java:916)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.scalarExpression(ExpressionParser.java:773)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.simpleCondition(ExpressionParser.java:236)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.notCondition(ExpressionParser.java:226)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.andCondition(ExpressionParser.java:134)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.orCondition(ExpressionParser.java:94)
at org.objectstyle.cayenne.exp.parser.ExpressionParser.expression(ExpressionParser.java:87)
at org.objectstyle.cayenne.exp.Expression.fromString(Expression.java:260)
... 16 more
Re: Outer join syntax
Posted by Andrus Adamchik <an...@objectstyle.org>.
I don't know about that ... left outer join has a meaning in a
context of a relationship. Right outer join does not... JPA spec
doesn't have a right or full outer join in it. So if there is a real
use case, lets discuss it.
Andrus
On Aug 17, 2006, at 5:44 PM, Mike Kienenberger wrote:
> Andrus,
>
> If we're going to go this far, should we plan ahead and figure out
> what we're going to use as syntax for "Right Outer Join" and "Full
> Outer Join"?
>
> Implementing these should be trivial once Left Outer Join is done.
>
> If Left is "+", is right "-"? :-)
>
> And is full "+/-"? :-)
>
> Yes, I recently got a DVD burner :-)
>
> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>>
>> On Aug 17, 2006, at 5:12 PM, Andrus Adamchik wrote:
>>
>> > Good point. Looks like we will need to change
>> > 'resolvePathComponents' iterator meaning to return a sequence of
>> > wrapper objects that have both relationship/attribute and the join
>> > semantics:
>> >
>> > class PathComponent {
>> > protected Object metadataObject;
>> > protected (?) joinSematics;
>> > }
>>
>> Or rather instead of "change semantics" create another iterator
>> method with a different name - don't want to break the existing apps.
>>
>> Andrus
>>
>
Re: Outer join syntax
Posted by Mike Kienenberger <mk...@gmail.com>.
Andrus,
If we're going to go this far, should we plan ahead and figure out
what we're going to use as syntax for "Right Outer Join" and "Full
Outer Join"?
Implementing these should be trivial once Left Outer Join is done.
If Left is "+", is right "-"? :-)
And is full "+/-"? :-)
Yes, I recently got a DVD burner :-)
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> On Aug 17, 2006, at 5:12 PM, Andrus Adamchik wrote:
>
> > Good point. Looks like we will need to change
> > 'resolvePathComponents' iterator meaning to return a sequence of
> > wrapper objects that have both relationship/attribute and the join
> > semantics:
> >
> > class PathComponent {
> > protected Object metadataObject;
> > protected (?) joinSematics;
> > }
>
> Or rather instead of "change semantics" create another iterator
> method with a different name - don't want to break the existing apps.
>
> Andrus
>
Re: Outer join syntax
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 17, 2006, at 5:12 PM, Andrus Adamchik wrote:
> Good point. Looks like we will need to change
> 'resolvePathComponents' iterator meaning to return a sequence of
> wrapper objects that have both relationship/attribute and the join
> semantics:
>
> class PathComponent {
> protected Object metadataObject;
> protected (?) joinSematics;
> }
Or rather instead of "change semantics" create another iterator
method with a different name - don't want to break the existing apps.
Andrus
Outer join syntax
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 17, 2006, at 5:04 PM, Mike Kienenberger wrote:
> Seems reasonable. Is this sufficient for many-to-many join tables?
> Ie, if we have a Obj toBList relationship, will "toBList+"
> automatically translate into "where a.a_id = bjointable.a_id(+) and
> bjointable.b_id = b.b_id(+)"?
We have db relationships, so I guess we can make this assumption for
the obj path, but if the user wants more control, they can use DB
path: "db:toA+.toB" or "db:toA.toB+".
> It seems like we now would have one Expression supporting multiple
> join types, depending on the path expression.
>
> resolvePathComponents would have to return not only the
> ObjRelationship/ObjAttributes, but also whether each piece was an
> outer join.
Good point. Looks like we will need to change 'resolvePathComponents'
iterator meaning to return a sequence of wrapper objects that have
both relationship/attribute and the join semantics:
class PathComponent {
protected Object metadataObject;
protected (?) joinSematics;
}
Andrus
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > One thing that is going to be annoying is having to specify an outer
> > join for every expression. I'm thinking we could have an "effective
> > outer join" value which, if not specified on the current Expression,
> > inherits from the parent expression. So an Expression join type by
> > default would be "unspecified" which means ask the parent. Any root
> > expression with unspecified would default to an inner join.
>
> That's where I disagree - when you build a query, every outer join is
> unique and unrelated to anything else. What I was thinking we can do
> to simplify the semantics specification (and also make expressions
> parsed from strings work the same way as expressions created via
> ExpressionFactory) is add join type to the object path string. E.g.
> use a "+" sign like Oracle did in the past:
>
> * Inner join: "toArtist"
> * Left outer join: "toArtist+"
> * Longer path with mixed joins: "toGallery+.toAddress"
>
> This seems like a reasonable non-verbose solution. What do you think?
Seems reasonable. Is this sufficient for many-to-many join tables?
Ie, if we have a Obj toBList relationship, will "toBList+"
automatically translate into "where a.a_id = bjointable.a_id(+) and
bjointable.b_id = b.b_id(+)"?
You'd think I'd know the answer (or be implementing it) since I'm
looking right at QueryAssemblyHelper.appendObjPath(), but I'm not sure
:-)
It seems like we now would have one Expression supporting multiple
join types, depending on the path expression.
resolvePathComponents would have to return not only the
ObjRelationship/ObjAttributes, but also whether each piece was an
outer join.
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Andrus Adamchik <an...@objectstyle.org>.
> It's now looking like QueryAssemblerHelper can also record whether
> something is an outer join when creating the join list at the same
> time it's outputting the column names.
Yes - qualifiers are translated as a separate piece.
> One thing that is going to be annoying is having to specify an outer
> join for every expression. I'm thinking we could have an "effective
> outer join" value which, if not specified on the current Expression,
> inherits from the parent expression. So an Expression join type by
> default would be "unspecified" which means ask the parent. Any root
> expression with unspecified would default to an inner join.
That's where I disagree - when you build a query, every outer join is
unique and unrelated to anything else. What I was thinking we can do
to simplify the semantics specification (and also make expressions
parsed from strings work the same way as expressions created via
ExpressionFactory) is add join type to the object path string. E.g.
use a "+" sign like Oracle did in the past:
* Inner join: "toArtist"
* Left outer join: "toArtist+"
* Longer path with mixed joins: "toGallery+.toAddress"
This seems like a reasonable non-verbose solution. What do you think?
Andrus
On Aug 17, 2006, at 3:34 PM, Mike Kienenberger wrote:
> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> > > simply have a query.setJoinType(JoinType.OUTER) to enable and
>> disable
>> >
>> > I would say this should be done in Expression, not Query, as this
>> > affects individual joins.
>>
>> Technically, I think it's going to be challenging to implement
>> this at
>> the Expression level rather than the query level. By the time the
>> SelectTranslator gets involved, it looks to me like the joins have
>> all
>> already been disassociated with the Expressions. It's unclear to me
>> whether all of the qualifiers have been disassociated with the
>> expressions at this point, but I think this wouldn't be a problem.
>>
>> At least in Oracle, I need to add a (+) after every outer-joined-
>> table
>> column name in the join qualifiers ["db relationship joins"] and
>> after
>> every outer-joined-table column name in the selection qualifiers
>> ["parent qualifier"].
>>
>> I think in my own use cases, like Øyvind's, I'm ok with everything
>> being an outer join if anything is an outer join.
>
>
> Oops. I wasn't quite ready to hit send yet -- I was still working
> through all of this :-)
> It's now looking like QueryAssemblerHelper can also record whether
> something is an outer join when creating the join list at the same
> time it's outputting the column names.
>
> So, perhaps I spoke too soon.
>
> One thing that is going to be annoying is having to specify an outer
> join for every expression. I'm thinking we could have an "effective
> outer join" value which, if not specified on the current Expression,
> inherits from the parent expression. So an Expression join type by
> default would be "unspecified" which means ask the parent. Any root
> expression with unspecified would default to an inner join.
>
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > > simply have a query.setJoinType(JoinType.OUTER) to enable and disable
> >
> > I would say this should be done in Expression, not Query, as this
> > affects individual joins.
>
> Technically, I think it's going to be challenging to implement this at
> the Expression level rather than the query level. By the time the
> SelectTranslator gets involved, it looks to me like the joins have all
> already been disassociated with the Expressions. It's unclear to me
> whether all of the qualifiers have been disassociated with the
> expressions at this point, but I think this wouldn't be a problem.
>
> At least in Oracle, I need to add a (+) after every outer-joined-table
> column name in the join qualifiers ["db relationship joins"] and after
> every outer-joined-table column name in the selection qualifiers
> ["parent qualifier"].
>
> I think in my own use cases, like Øyvind's, I'm ok with everything
> being an outer join if anything is an outer join.
Oops. I wasn't quite ready to hit send yet -- I was still working
through all of this :-)
It's now looking like QueryAssemblerHelper can also record whether
something is an outer join when creating the join list at the same
time it's outputting the column names.
So, perhaps I spoke too soon.
One thing that is going to be annoying is having to specify an outer
join for every expression. I'm thinking we could have an "effective
outer join" value which, if not specified on the current Expression,
inherits from the parent expression. So an Expression join type by
default would be "unspecified" which means ask the parent. Any root
expression with unspecified would default to an inner join.
Re: Translating outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 20, 2006, at 3:44 AM, Mike Kienenberger wrote:
> After thinking about this for awhile, I'm not sure I believe it :-)
[...]
> So our qualifier and joins strings would need to look like this:
>
> q.setJoins("INNER JOIN feeType ft, INNER JOIN feeCycle fc"
> + ",LEFT OUTER JOIN initialDoc id, LEFT OUTER JOIN permitDoc ipd"
> + ",LEFT OUTER JOIN recurringDoc rd, LEFT OUTER JOIN permitDoc rpd")
>
> q.setQualifier("( ft.description = ? ) and ( ( ipd.agency = ? ) or (
> rpd.agency = ? ) )'");
[...]
> The qualifier is no longer relative to the root entity -- each
> fragment is relative to the join alias.
Good point, so this won't be completely transparent, and the
qualifier will have to be written differently when the joins are
used. But it will still work.
Andrus
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/19/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> On Aug 18, 2006, at 11:30 PM, Mike Kienenberger wrote:
> > how do you see the actual usage looking from the cayenne side?
>
> Let me rewrite the example I mentioned before in terms of proposed
> SelectQuery API:
>
> SelectQuery q = new SelectQuery(Painting.class);
> q.setQualifier("x.artistName = null or y.artistName = 'artist6'");
>
> // *** NEW SUGGESTED API
> q.setJoins("LEFT OUTER JOIN toArtist x, LEFT OUTER JOIN toArtist y");
> [...]
> Essentially everything will continue to work the way it does now, and
> only when you need custom splits and/or OUTER joins, "setJoins"
> method will be used with a JPA join string.
After thinking about this for awhile, I'm not sure I believe it :-)
"x.artistName = null or y.artistName = 'artist6'" uses an absolute path.
Current expressions use a relative path.
"x.artistName = null or y.artistName = 'artist6'" in the current query
language would mean relationship x's artist name and relationship y's
artistName.
But this now means Table x's artist name and Table y's artist name.
It's possible I'm still confused since I feel like I haven't slept in a week :-)
Let's consider some of my actual code. For the sql expression
generated earlier, I have:
List andedExpressionList = new ArrayList();
andedExpressionList.add(ExpressionFactory.matchExp(Fee.FEE_TYPE_PROPERTY
+ "." + FeeType.DESCRIPTION_PROPERTY, FeeType.NAME_EDMS));
List oredExpressionList = new ArrayList();
oredExpressionList.add(ExpressionFactory.matchExp(Fee.FEE_CYCLE_PROPERTY
+ "." + FeeCycle.INITIAL_AUTHORIZATION_DOCUMENT_PROPERTY + "+" + "." +
AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY + "+" + "." +
PermitDocument.AGENCY_PROPERTY, agency));
oredExpressionList.add(ExpressionFactory.matchExp(Fee.FEE_CYCLE_PROPERTY
+ "." + FeeCycle.RECURRING_AUTHORIZATION_DOCUMENT_PROPERTY + "+" + "."
+ AuthorizationDocument.DEPENDENT_PERMIT_DOCUMENT_PROPERTY + "+" + "."
+ PermitDocument.AGENCY_PROPERTY, agency));
andedExpressionList.add(constructOredExpression(oredExpressionList));
Expression finalExpression = constructAndedExpression(andedExpressionList)
That's really hard to read, so let me put it into a simplified string format:
"( feeType.description = ? ) and ( (
feeCycle.initialDoc.permitDoc.agency = ? ) or (
feeCycle.recurringDoc.permitDoc.agency = ? ) )"
initialDoc, permitDoc, and recurringDoc could all potentially be null
and need to be outer joins.
So we'd need something like this generated (assuming I understand how
to do multiple joins):
SELECT * FROM
ENG_WORK_MGMT.FEE t0
INNER JOIN ENG_WORK_MGMT.FEE_TYPE t1
ON t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
INNER JOIN ENG_WORK_MGMT.FEE_CYCLE t2,
ON t0.FEE_ID = t2.FEE_CYCLE_ID
LEFT OUTER JOIN ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
ON t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID
LEFT OUTER JOIN ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
ON t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID
LEFT OUTER JOIN JOIN ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5,
ON t2.FEE_CYCLE_ID = t5.INITIAL_FEE_CYCLE_ID
LEFT OUTER JOIN ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
ON t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID
WHERE
t1.DESCRIPTION = ?
AND
t4.AGENCY_ID = ?
AND
t6.AGENCY_ID = ?
So our qualifier and joins strings would need to look like this:
q.setJoins("INNER JOIN feeType ft, INNER JOIN feeCycle fc"
+ ",LEFT OUTER JOIN initialDoc id, LEFT OUTER JOIN permitDoc ipd"
+ ",LEFT OUTER JOIN recurringDoc rd, LEFT OUTER JOIN permitDoc rpd")
q.setQualifier("( ft.description = ? ) and ( ( ipd.agency = ? ) or (
rpd.agency = ? ) )'");
The qualifier is no longer relative to the root entity -- each
fragment is relative to the join alias.
Re: Translating outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 18, 2006, at 11:30 PM, Mike Kienenberger wrote:
> Now I was all ready to keep joins associated with Expressions. Why
> switch them back?
It is hard to design it right from the first shot. But I hope we are
getting closer now.
> It looks like each join in the JPA spec is associated with an
> expression as well.
No, there is a separation. Keep in mind that Cayenne expressions
approximate just the WHERE clause in SQL, while EJBQL is SELECT+FROM
+WHERE+..., i.e. the entire SQL query. Joins are defined in the FROM
clause (that allows to define precise join splits and semantics of
each join).
> If we are going to put the join on the query, (or even if we are not),
> how do you see the actual usage looking from the cayenne side?
Let me rewrite the example I mentioned before in terms of proposed
SelectQuery API:
SelectQuery q = new SelectQuery(Painting.class);
q.setQualifier("x.artistName = null or y.artistName = 'artist6'");
// *** NEW SUGGESTED API
q.setJoins("LEFT OUTER JOIN toArtist x, LEFT OUTER JOIN toArtist y");
> Will expressions now have table alias prefixes which are matched up to
> the aliases specified in the join parameters?
Yes - optionally.
> Or do we want to switch entirely to the JPA format of LEFT OUTER JOIN
> AS x FETCH exp? with the join type defaulting to inner if not
> specified.
Essentially everything will continue to work the way it does now, and
only when you need custom splits and/or OUTER joins, "setJoins"
method will be used with a JPA join string.
> Finally, is there a difference between LEFT OUTER JOIN and LEFT JOIN
> in the JPA specs?
No, they are the same ("OUTER" is an optional keyword - "LEFT"
implies "OUTER").
> It seems like no matter how we approach it, there will be a different
> syntax going forward. If that's the case, maybe we should plan on
> using the JPA syntax where possible.
I think this will happen over time ...
I started on the full EJB QL parser grammar last night (I didn't have
internet access till now, so I couldn't share anything). I guess the
next step would be a JpaQuery (or EJBQLQuery) that includes a spec-
compliant query string and translates to SelectQuery (if it happens
to be a select) during execution. And later we may merge the two if
this turns out to be possible.
Andrus
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Ok. I remember CAY-514 now. I don't think I understood it as well
then, but it's clearer to me now :-)
Now I was all ready to keep joins associated with Expressions. Why
switch them back? It looks like each join in the JPA spec is
associated with an expression as well.
If we are going to put the join on the query, (or even if we are not),
how do you see the actual usage looking from the cayenne side?
Will expressions now have table alias prefixes which are matched up to
the aliases specified in the join parameters?
Or do we want to switch entirely to the JPA format of LEFT OUTER JOIN
AS x FETCH exp? with the join type defaulting to inner if not
specified.
Finally, is there a difference between LEFT OUTER JOIN and LEFT JOIN
in the JPA specs?
It seems like no matter how we approach it, there will be a different
syntax going forward.
If that's the case, maybe we should plan on using the JPA syntax where possible.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Yeah. That limitation plagued us for a while. I don't think the
> separation lies in INNER vs. OUTER join, as both can have "split" or
> "common" joins. Here is an example of a multiple criteria going over
> a common join:
>
> Expression: "toArtist+.artistName = null or toArtist+.artistName =
> 'artist6'"
>
> Corresponding SQL that I tested on Postgres:
>
> select t0.*, t1.artist_name from painting t0 left outer join artist t1
> on t0.artist_id = t1.artist_id
> where t1.artist_name is null or t1.artist_name = 'artist6'
>
> BTW, there is task related to that: http://issues.apache.org/cayenne/
> browse/CAY-514
>
> > Unfortunately, I don't see a backwards-compatible way to pass the
> > join semantics to this public method.
>
> I think changing the translators is a fair game - the API is not
> directly used by the applications, and major version number increase
> means that we do it if we have too (as long as it is clearly
> documented). But let's figure out how we want to address splits first...
>
> Here is an idea:
>
> JPA EJBQL has an explicit clause for joins, but it doesn't fit in the
> Cayenne 1.2 expressions that represent a WHERE clause with implicitly
> inferred joins (EJBQL joins go in the FROM clause). So let's go back
> to your idea of setting join policy on a SelectQuery, separate from
> expression, only in a slightly different and more targeted way that
> would create aliases for expressions, thus allowing controlled "splits":
>
> EXAMPLE 1: "Common" outer join example (join clause is EJBQL
> compatible; root entity is implied; "x" is an alias for the join):
>
> join: LEFT OUTER JOIN toArtist x
> exp: x.artistName = null or x.artistName = 'artist6'
>
> EXAMPLE 2: The same thing with split joins:
>
> join: LEFT OUTER JOIN toArtist x, LEFT OUTER JOIN toArtist y
> exp: x.artistName = null or y.artistName = 'artist6'
>
> With this approach we can avoid using "+" in the path, preserving 1.2
> expressions syntax intact and moving join description outside the
> expression. This should solve CAY-514 as well.
>
> Andrus
>
> P.S. Full BNF of join per JPA spec:
>
> join ::= join_spec join_association_path_expression [AS]
> identification_variable
> fetch_join ::= join_spec FETCH join_association_path_expression
> join_association_path_expression ::=
> join_collection_valued_path_expression |
> join_single_valued_association_path_expression
> join_spec::= [LEFT[OUTER]|INNER] JOIN
>
>
>
>
> On Aug 17, 2006, at 10:13 PM, Mike Kienenberger wrote:
> > Ok. It looks like the problem is this.
> > SelectTranslator.dbRelationshipAdded only creates one table alias
> > (FROM table entry) for a particular relationship's target entity.
> > That works great for INNER joins, but for LEFT OUTER joins, I think we
> > need one per source/target pair. Unfortunately, I don't see a
> > backwards-compatible way to pass the join semantics to this public
> > method. Unless we can set join semantics directly on the
> > relationship itself. Maybe it should be an attribute of DbJoin and
> > we could pull it up that way. It's getting late and I'm not really
> > thinking clearly any more, but I'd appreciate any input for when I
> > start up again on it tomorrow.
> >
> > public void SelectTranslator.dbRelationshipAdded(DbRelationship
> > rel) {
> > [...]
> >
> > String existAlias = (String) aliasLookup.get(rel);
> >
> > if (existAlias == null) {
> > dbRelList.add(rel);
> >
> > // add alias for the destination table of the relationship
> > String newAlias = newAliasForTable((DbEntity)
> > rel.getTargetEntity());
> > aliasLookup.put(rel, newAlias);
> > }
> > }
> >
> > When it's an outer join, then each outer join needs to be given a
> > unique alias entry, but if it's an inner join, we only want one entry.
> >
> >
> > On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> >> SELECT * FROM (SELECT [...] FROM
> >>
> >> ENG_WORK_MGMT.FEE t0,
> >> ENG_WORK_MGMT.FEE_TYPE t1,
> >> ENG_WORK_MGMT.FEE_CYCLE
> >> ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
> >> ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
> >> ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5
> >>
> >> WHERE
> >> t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
> >> AND t0.FEE_ID = t2.FEE_CYCLE_ID
> >> AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
> >> AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
> >> AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)
> >>
> >> AND ((t1.DESCRIPTION = ?)
> >> AND ((t4.AGENCY_ID = ?)
> >> OR (t4.AGENCY_ID = ?))))
> >>
> >>
> >> Ok. I appear to be having some kind of unwanted optimization
> >> occurring.
> >>
> >> There should be a line that says
> >>
> >> AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
> >>
> >> or maybe even
> >>
> >> ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
> >> AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
> >> with (t6.AGENCY_ID = ?)
> >>
> >> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> >> > Ok. I think I have Oracle8 style outer joins working. I'll
> >> take a
> >> > shot at the other ones tomorrow.
> >> >
> >> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> >> > > Yes, actually there was some discussion before to use such
> >> syntax for
> >> > > the inner joins as well. I am all for it (I guess we have to
> >> preserve
> >> > > a backdoor for the old syntax in case some db does not support
> >> such
> >> > > syntax).
> >> > >
> >> > > Andrus
> >> > >
> >> > > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
> >> > >
> >> > > > Even better link
> >> > > >
> >> > > > http://www.devx.com/dbzone/Article/17403/0/page/3
> >> > > >
> >> > > > Looks like we do away with WHERE clause joins altogether (at
> >> least for
> >> > > > Oracle) and explicly join everything with ON statements.
> >> > > >
> >> > > > On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> >> > > >> This is somewhat helpful for the various kinds of joins.
> >> > > >>
> >> > > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
> >> > > >>
> >> > > >> Still looking for complex examples.
> >> > > >>
> >> > > >> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> >> > > >> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> >> > > >> > > It would be nice if we could implement the translator
> >> using
> >> > > >> standard
> >> > > >> > > SQL syntax ("left outer join" instead of "(+)"), as it
> >> will
> >> > > >> work on
> >> > > >> > > most DB's including Oracle (starting from 9i), while
> >> the "(+)"
> >> > > >> syntax
> >> > > >> > > only works on Oracle (and is probably considered legacy
> >> syntax by
> >> > > >> > > Oracle too).
> >> > > >> > >
> >> > > >> > > select
> >> > > >> > > name,
> >> > > >> > > department_name
> >> > > >> > > from
> >> > > >> > > employees e
> >> > > >> > > left outer join
> >> > > >> > > departments d
> >> > > >> > > on
> >> > > >> > > e.department_id = d.department_id;
> >> > > >> > >
> >> > > >> > > It will be somewhat harder to implement, but will solve
> >> the issue
> >> > > >> > > once and for all.
> >> > > >> >
> >> > > >> > Well, sure, now you tell me :-)
> >> > > >> >
> >> > > >> > My Oracle Reference Book is Oracle8, so I didn't realize
> >> we had a
> >> > > >> > better choice :-)
> >> > > >> >
> >> > > >> > I guess I need to see if I can find some documentation on
> >> this
> >> > > >> format.
> >> > > >> >
> >> > > >> > The simple example is obvious, but what does it look like
> >> with more
> >> > > >> > tables involved, some with more outer joins and some
> >> without?
> >> > > >> >
> >> > > >>
> >> > > >
> >> > >
> >> > >
> >> >
> >>
> >
>
>
EJBQL Parser
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 17, 2006, at 11:27 PM, Andrus Adamchik wrote:
> JPA EJBQL has an explicit clause for joins ...
I thought TranQL (an undocumented, but seemingly clean QL parser
project used in Geronimo) might be useful in the implementation of
our own EJB QL parser. Looks like this is not the case [1]. So we'll
have to work on the parser ourselves. I've learned a few things about
JavaCC while working on the Expression parser, so I may look into
that and make sure we can parse either the entire thing or just the
JOIN clause. And then there'll be a visitor interface to walk the
parsed tree.
Andrus
[1] http://marc.theaimsgroup.com/?l=geronimo-dev&m=115587550831619&w=2
Re: Translating outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yeah. That limitation plagued us for a while. I don't think the
separation lies in INNER vs. OUTER join, as both can have "split" or
"common" joins. Here is an example of a multiple criteria going over
a common join:
Expression: "toArtist+.artistName = null or toArtist+.artistName =
'artist6'"
Corresponding SQL that I tested on Postgres:
select t0.*, t1.artist_name from painting t0 left outer join artist t1
on t0.artist_id = t1.artist_id
where t1.artist_name is null or t1.artist_name = 'artist6'
BTW, there is task related to that: http://issues.apache.org/cayenne/
browse/CAY-514
> Unfortunately, I don't see a backwards-compatible way to pass the
> join semantics to this public method.
I think changing the translators is a fair game - the API is not
directly used by the applications, and major version number increase
means that we do it if we have too (as long as it is clearly
documented). But let's figure out how we want to address splits first...
Here is an idea:
JPA EJBQL has an explicit clause for joins, but it doesn't fit in the
Cayenne 1.2 expressions that represent a WHERE clause with implicitly
inferred joins (EJBQL joins go in the FROM clause). So let's go back
to your idea of setting join policy on a SelectQuery, separate from
expression, only in a slightly different and more targeted way that
would create aliases for expressions, thus allowing controlled "splits":
EXAMPLE 1: "Common" outer join example (join clause is EJBQL
compatible; root entity is implied; "x" is an alias for the join):
join: LEFT OUTER JOIN toArtist x
exp: x.artistName = null or x.artistName = 'artist6'
EXAMPLE 2: The same thing with split joins:
join: LEFT OUTER JOIN toArtist x, LEFT OUTER JOIN toArtist y
exp: x.artistName = null or y.artistName = 'artist6'
With this approach we can avoid using "+" in the path, preserving 1.2
expressions syntax intact and moving join description outside the
expression. This should solve CAY-514 as well.
Andrus
P.S. Full BNF of join per JPA spec:
join ::= join_spec join_association_path_expression [AS]
identification_variable
fetch_join ::= join_spec FETCH join_association_path_expression
join_association_path_expression ::=
join_collection_valued_path_expression |
join_single_valued_association_path_expression
join_spec::= [LEFT[OUTER]|INNER] JOIN
On Aug 17, 2006, at 10:13 PM, Mike Kienenberger wrote:
> Ok. It looks like the problem is this.
> SelectTranslator.dbRelationshipAdded only creates one table alias
> (FROM table entry) for a particular relationship's target entity.
> That works great for INNER joins, but for LEFT OUTER joins, I think we
> need one per source/target pair. Unfortunately, I don't see a
> backwards-compatible way to pass the join semantics to this public
> method. Unless we can set join semantics directly on the
> relationship itself. Maybe it should be an attribute of DbJoin and
> we could pull it up that way. It's getting late and I'm not really
> thinking clearly any more, but I'd appreciate any input for when I
> start up again on it tomorrow.
>
> public void SelectTranslator.dbRelationshipAdded(DbRelationship
> rel) {
> [...]
>
> String existAlias = (String) aliasLookup.get(rel);
>
> if (existAlias == null) {
> dbRelList.add(rel);
>
> // add alias for the destination table of the relationship
> String newAlias = newAliasForTable((DbEntity)
> rel.getTargetEntity());
> aliasLookup.put(rel, newAlias);
> }
> }
>
> When it's an outer join, then each outer join needs to be given a
> unique alias entry, but if it's an inner join, we only want one entry.
>
>
> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> SELECT * FROM (SELECT [...] FROM
>>
>> ENG_WORK_MGMT.FEE t0,
>> ENG_WORK_MGMT.FEE_TYPE t1,
>> ENG_WORK_MGMT.FEE_CYCLE
>> ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
>> ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
>> ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5
>>
>> WHERE
>> t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
>> AND t0.FEE_ID = t2.FEE_CYCLE_ID
>> AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
>> AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
>> AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)
>>
>> AND ((t1.DESCRIPTION = ?)
>> AND ((t4.AGENCY_ID = ?)
>> OR (t4.AGENCY_ID = ?))))
>>
>>
>> Ok. I appear to be having some kind of unwanted optimization
>> occurring.
>>
>> There should be a line that says
>>
>> AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
>>
>> or maybe even
>>
>> ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
>> AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
>> with (t6.AGENCY_ID = ?)
>>
>> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> > Ok. I think I have Oracle8 style outer joins working. I'll
>> take a
>> > shot at the other ones tomorrow.
>> >
>> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> > > Yes, actually there was some discussion before to use such
>> syntax for
>> > > the inner joins as well. I am all for it (I guess we have to
>> preserve
>> > > a backdoor for the old syntax in case some db does not support
>> such
>> > > syntax).
>> > >
>> > > Andrus
>> > >
>> > > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
>> > >
>> > > > Even better link
>> > > >
>> > > > http://www.devx.com/dbzone/Article/17403/0/page/3
>> > > >
>> > > > Looks like we do away with WHERE clause joins altogether (at
>> least for
>> > > > Oracle) and explicly join everything with ON statements.
>> > > >
>> > > > On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> > > >> This is somewhat helpful for the various kinds of joins.
>> > > >>
>> > > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
>> > > >>
>> > > >> Still looking for complex examples.
>> > > >>
>> > > >> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> > > >> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> > > >> > > It would be nice if we could implement the translator
>> using
>> > > >> standard
>> > > >> > > SQL syntax ("left outer join" instead of "(+)"), as it
>> will
>> > > >> work on
>> > > >> > > most DB's including Oracle (starting from 9i), while
>> the "(+)"
>> > > >> syntax
>> > > >> > > only works on Oracle (and is probably considered legacy
>> syntax by
>> > > >> > > Oracle too).
>> > > >> > >
>> > > >> > > select
>> > > >> > > name,
>> > > >> > > department_name
>> > > >> > > from
>> > > >> > > employees e
>> > > >> > > left outer join
>> > > >> > > departments d
>> > > >> > > on
>> > > >> > > e.department_id = d.department_id;
>> > > >> > >
>> > > >> > > It will be somewhat harder to implement, but will solve
>> the issue
>> > > >> > > once and for all.
>> > > >> >
>> > > >> > Well, sure, now you tell me :-)
>> > > >> >
>> > > >> > My Oracle Reference Book is Oracle8, so I didn't realize
>> we had a
>> > > >> > better choice :-)
>> > > >> >
>> > > >> > I guess I need to see if I can find some documentation on
>> this
>> > > >> format.
>> > > >> >
>> > > >> > The simple example is obvious, but what does it look like
>> with more
>> > > >> > tables involved, some with more outer joins and some
>> without?
>> > > >> >
>> > > >>
>> > > >
>> > >
>> > >
>> >
>>
>
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Ok. It looks like the problem is this.
SelectTranslator.dbRelationshipAdded only creates one table alias
(FROM table entry) for a particular relationship's target entity.
That works great for INNER joins, but for LEFT OUTER joins, I think we
need one per source/target pair. Unfortunately, I don't see a
backwards-compatible way to pass the join semantics to this public
method. Unless we can set join semantics directly on the
relationship itself. Maybe it should be an attribute of DbJoin and
we could pull it up that way. It's getting late and I'm not really
thinking clearly any more, but I'd appreciate any input for when I
start up again on it tomorrow.
public void SelectTranslator.dbRelationshipAdded(DbRelationship rel) {
[...]
String existAlias = (String) aliasLookup.get(rel);
if (existAlias == null) {
dbRelList.add(rel);
// add alias for the destination table of the relationship
String newAlias = newAliasForTable((DbEntity)
rel.getTargetEntity());
aliasLookup.put(rel, newAlias);
}
}
When it's an outer join, then each outer join needs to be given a
unique alias entry, but if it's an inner join, we only want one entry.
On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> SELECT * FROM (SELECT [...] FROM
>
> ENG_WORK_MGMT.FEE t0,
> ENG_WORK_MGMT.FEE_TYPE t1,
> ENG_WORK_MGMT.FEE_CYCLE
> ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
> ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
> ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5
>
> WHERE
> t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
> AND t0.FEE_ID = t2.FEE_CYCLE_ID
> AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
> AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
> AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)
>
> AND ((t1.DESCRIPTION = ?)
> AND ((t4.AGENCY_ID = ?)
> OR (t4.AGENCY_ID = ?))))
>
>
> Ok. I appear to be having some kind of unwanted optimization occurring.
>
> There should be a line that says
>
> AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
>
> or maybe even
>
> ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
> AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
> with (t6.AGENCY_ID = ?)
>
> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> > Ok. I think I have Oracle8 style outer joins working. I'll take a
> > shot at the other ones tomorrow.
> >
> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > > Yes, actually there was some discussion before to use such syntax for
> > > the inner joins as well. I am all for it (I guess we have to preserve
> > > a backdoor for the old syntax in case some db does not support such
> > > syntax).
> > >
> > > Andrus
> > >
> > > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
> > >
> > > > Even better link
> > > >
> > > > http://www.devx.com/dbzone/Article/17403/0/page/3
> > > >
> > > > Looks like we do away with WHERE clause joins altogether (at least for
> > > > Oracle) and explicly join everything with ON statements.
> > > >
> > > > On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> > > >> This is somewhat helpful for the various kinds of joins.
> > > >>
> > > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
> > > >>
> > > >> Still looking for complex examples.
> > > >>
> > > >> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> > > >> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > > >> > > It would be nice if we could implement the translator using
> > > >> standard
> > > >> > > SQL syntax ("left outer join" instead of "(+)"), as it will
> > > >> work on
> > > >> > > most DB's including Oracle (starting from 9i), while the "(+)"
> > > >> syntax
> > > >> > > only works on Oracle (and is probably considered legacy syntax by
> > > >> > > Oracle too).
> > > >> > >
> > > >> > > select
> > > >> > > name,
> > > >> > > department_name
> > > >> > > from
> > > >> > > employees e
> > > >> > > left outer join
> > > >> > > departments d
> > > >> > > on
> > > >> > > e.department_id = d.department_id;
> > > >> > >
> > > >> > > It will be somewhat harder to implement, but will solve the issue
> > > >> > > once and for all.
> > > >> >
> > > >> > Well, sure, now you tell me :-)
> > > >> >
> > > >> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
> > > >> > better choice :-)
> > > >> >
> > > >> > I guess I need to see if I can find some documentation on this
> > > >> format.
> > > >> >
> > > >> > The simple example is obvious, but what does it look like with more
> > > >> > tables involved, some with more outer joins and some without?
> > > >> >
> > > >>
> > > >
> > >
> > >
> >
>
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
SELECT * FROM (SELECT [...] FROM
ENG_WORK_MGMT.FEE t0,
ENG_WORK_MGMT.FEE_TYPE t1,
ENG_WORK_MGMT.FEE_CYCLE
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5
WHERE
t0.FEE_TYPE_ID = t1.FEE_TYPE_ID
AND t0.FEE_ID = t2.FEE_CYCLE_ID
AND t2.FEE_CYCLE_ID = t3.INITIAL_FEE_CYCLE_ID(+)
AND t3.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
AND t2.FEE_CYCLE_ID = t5.RECURRING_FEE_CYCLE_ID(+)
AND ((t1.DESCRIPTION = ?)
AND ((t4.AGENCY_ID = ?)
OR (t4.AGENCY_ID = ?))))
Ok. I appear to be having some kind of unwanted optimization occurring.
There should be a line that says
AND t5.AUTHORIZATION_DOCUMENT_ID = t4.PERMIT_DOCUMENT_ID(+)
or maybe even
ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
AND t5.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
with (t6.AGENCY_ID = ?)
On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> Ok. I think I have Oracle8 style outer joins working. I'll take a
> shot at the other ones tomorrow.
>
> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > Yes, actually there was some discussion before to use such syntax for
> > the inner joins as well. I am all for it (I guess we have to preserve
> > a backdoor for the old syntax in case some db does not support such
> > syntax).
> >
> > Andrus
> >
> > On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
> >
> > > Even better link
> > >
> > > http://www.devx.com/dbzone/Article/17403/0/page/3
> > >
> > > Looks like we do away with WHERE clause joins altogether (at least for
> > > Oracle) and explicly join everything with ON statements.
> > >
> > > On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> > >> This is somewhat helpful for the various kinds of joins.
> > >>
> > >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
> > >>
> > >> Still looking for complex examples.
> > >>
> > >> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> > >> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > >> > > It would be nice if we could implement the translator using
> > >> standard
> > >> > > SQL syntax ("left outer join" instead of "(+)"), as it will
> > >> work on
> > >> > > most DB's including Oracle (starting from 9i), while the "(+)"
> > >> syntax
> > >> > > only works on Oracle (and is probably considered legacy syntax by
> > >> > > Oracle too).
> > >> > >
> > >> > > select
> > >> > > name,
> > >> > > department_name
> > >> > > from
> > >> > > employees e
> > >> > > left outer join
> > >> > > departments d
> > >> > > on
> > >> > > e.department_id = d.department_id;
> > >> > >
> > >> > > It will be somewhat harder to implement, but will solve the issue
> > >> > > once and for all.
> > >> >
> > >> > Well, sure, now you tell me :-)
> > >> >
> > >> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
> > >> > better choice :-)
> > >> >
> > >> > I guess I need to see if I can find some documentation on this
> > >> format.
> > >> >
> > >> > The simple example is obvious, but what does it look like with more
> > >> > tables involved, some with more outer joins and some without?
> > >> >
> > >>
> > >
> >
> >
>
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Ok. I think I have Oracle8 style outer joins working. I'll take a
shot at the other ones tomorrow.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Yes, actually there was some discussion before to use such syntax for
> the inner joins as well. I am all for it (I guess we have to preserve
> a backdoor for the old syntax in case some db does not support such
> syntax).
>
> Andrus
>
> On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
>
> > Even better link
> >
> > http://www.devx.com/dbzone/Article/17403/0/page/3
> >
> > Looks like we do away with WHERE clause joins altogether (at least for
> > Oracle) and explicly join everything with ON statements.
> >
> > On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> >> This is somewhat helpful for the various kinds of joins.
> >>
> >> http://www.praetoriate.com/oracle_tips_outer_joins.htm
> >>
> >> Still looking for complex examples.
> >>
> >> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> >> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> >> > > It would be nice if we could implement the translator using
> >> standard
> >> > > SQL syntax ("left outer join" instead of "(+)"), as it will
> >> work on
> >> > > most DB's including Oracle (starting from 9i), while the "(+)"
> >> syntax
> >> > > only works on Oracle (and is probably considered legacy syntax by
> >> > > Oracle too).
> >> > >
> >> > > select
> >> > > name,
> >> > > department_name
> >> > > from
> >> > > employees e
> >> > > left outer join
> >> > > departments d
> >> > > on
> >> > > e.department_id = d.department_id;
> >> > >
> >> > > It will be somewhat harder to implement, but will solve the issue
> >> > > once and for all.
> >> >
> >> > Well, sure, now you tell me :-)
> >> >
> >> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
> >> > better choice :-)
> >> >
> >> > I guess I need to see if I can find some documentation on this
> >> format.
> >> >
> >> > The simple example is obvious, but what does it look like with more
> >> > tables involved, some with more outer joins and some without?
> >> >
> >>
> >
>
>
Re: Translating outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
Yes, actually there was some discussion before to use such syntax for
the inner joins as well. I am all for it (I guess we have to preserve
a backdoor for the old syntax in case some db does not support such
syntax).
Andrus
On Aug 17, 2006, at 5:17 PM, Mike Kienenberger wrote:
> Even better link
>
> http://www.devx.com/dbzone/Article/17403/0/page/3
>
> Looks like we do away with WHERE clause joins altogether (at least for
> Oracle) and explicly join everything with ON statements.
>
> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> This is somewhat helpful for the various kinds of joins.
>>
>> http://www.praetoriate.com/oracle_tips_outer_joins.htm
>>
>> Still looking for complex examples.
>>
>> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
>> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> > > It would be nice if we could implement the translator using
>> standard
>> > > SQL syntax ("left outer join" instead of "(+)"), as it will
>> work on
>> > > most DB's including Oracle (starting from 9i), while the "(+)"
>> syntax
>> > > only works on Oracle (and is probably considered legacy syntax by
>> > > Oracle too).
>> > >
>> > > select
>> > > name,
>> > > department_name
>> > > from
>> > > employees e
>> > > left outer join
>> > > departments d
>> > > on
>> > > e.department_id = d.department_id;
>> > >
>> > > It will be somewhat harder to implement, but will solve the issue
>> > > once and for all.
>> >
>> > Well, sure, now you tell me :-)
>> >
>> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
>> > better choice :-)
>> >
>> > I guess I need to see if I can find some documentation on this
>> format.
>> >
>> > The simple example is obvious, but what does it look like with more
>> > tables involved, some with more outer joins and some without?
>> >
>>
>
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Even better link
http://www.devx.com/dbzone/Article/17403/0/page/3
Looks like we do away with WHERE clause joins altogether (at least for
Oracle) and explicly join everything with ON statements.
On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> This is somewhat helpful for the various kinds of joins.
>
> http://www.praetoriate.com/oracle_tips_outer_joins.htm
>
> Still looking for complex examples.
>
> On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> > On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > > It would be nice if we could implement the translator using standard
> > > SQL syntax ("left outer join" instead of "(+)"), as it will work on
> > > most DB's including Oracle (starting from 9i), while the "(+)" syntax
> > > only works on Oracle (and is probably considered legacy syntax by
> > > Oracle too).
> > >
> > > select
> > > name,
> > > department_name
> > > from
> > > employees e
> > > left outer join
> > > departments d
> > > on
> > > e.department_id = d.department_id;
> > >
> > > It will be somewhat harder to implement, but will solve the issue
> > > once and for all.
> >
> > Well, sure, now you tell me :-)
> >
> > My Oracle Reference Book is Oracle8, so I didn't realize we had a
> > better choice :-)
> >
> > I guess I need to see if I can find some documentation on this format.
> >
> > The simple example is obvious, but what does it look like with more
> > tables involved, some with more outer joins and some without?
> >
>
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
This is somewhat helpful for the various kinds of joins.
http://www.praetoriate.com/oracle_tips_outer_joins.htm
Still looking for complex examples.
On 8/17/06, Mike Kienenberger <mk...@gmail.com> wrote:
> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > It would be nice if we could implement the translator using standard
> > SQL syntax ("left outer join" instead of "(+)"), as it will work on
> > most DB's including Oracle (starting from 9i), while the "(+)" syntax
> > only works on Oracle (and is probably considered legacy syntax by
> > Oracle too).
> >
> > select
> > name,
> > department_name
> > from
> > employees e
> > left outer join
> > departments d
> > on
> > e.department_id = d.department_id;
> >
> > It will be somewhat harder to implement, but will solve the issue
> > once and for all.
>
> Well, sure, now you tell me :-)
>
> My Oracle Reference Book is Oracle8, so I didn't realize we had a
> better choice :-)
>
> I guess I need to see if I can find some documentation on this format.
>
> The simple example is obvious, but what does it look like with more
> tables involved, some with more outer joins and some without?
>
Re: Translating outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> It would be nice if we could implement the translator using standard
> SQL syntax ("left outer join" instead of "(+)"), as it will work on
> most DB's including Oracle (starting from 9i), while the "(+)" syntax
> only works on Oracle (and is probably considered legacy syntax by
> Oracle too).
>
> select
> name,
> department_name
> from
> employees e
> left outer join
> departments d
> on
> e.department_id = d.department_id;
>
> It will be somewhat harder to implement, but will solve the issue
> once and for all.
Well, sure, now you tell me :-)
My Oracle Reference Book is Oracle8, so I didn't realize we had a
better choice :-)
I guess I need to see if I can find some documentation on this format.
The simple example is obvious, but what does it look like with more
tables involved, some with more outer joins and some without?
Translating outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Aug 17, 2006, at 3:28 PM, Mike Kienenberger wrote:
> At least in Oracle, I need to add a (+) after every outer-joined-table
> column name in the join qualifiers ["db relationship joins"] and after
> every outer-joined-table column name in the selection qualifiers
> ["parent qualifier"].
It would be nice if we could implement the translator using standard
SQL syntax ("left outer join" instead of "(+)"), as it will work on
most DB's including Oracle (starting from 9i), while the "(+)" syntax
only works on Oracle (and is probably considered legacy syntax by
Oracle too).
select
name,
department_name
from
employees e
left outer join
departments d
on
e.department_id = d.department_id;
It will be somewhat harder to implement, but will solve the issue
once and for all.
Andrus
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > simply have a query.setJoinType(JoinType.OUTER) to enable and disable
>
> I would say this should be done in Expression, not Query, as this
> affects individual joins.
Technically, I think it's going to be challenging to implement this at
the Expression level rather than the query level. By the time the
SelectTranslator gets involved, it looks to me like the joins have all
already been disassociated with the Expressions. It's unclear to me
whether all of the qualifiers have been disassociated with the
expressions at this point, but I think this wouldn't be a problem.
At least in Oracle, I need to add a (+) after every outer-joined-table
column name in the join qualifiers ["db relationship joins"] and after
every outer-joined-table column name in the selection qualifiers
["parent qualifier"].
I think in my own use cases, like Øyvind's, I'm ok with everything
being an outer join if anything is an outer join.
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Andrus Adamchik <an...@objectstyle.org>.
> simply have a query.setJoinType(JoinType.OUTER) to enable and disable
I would say this should be done in Expression, not Query, as this
affects individual joins.
> Can you
> provide me with shortcuts to just that part of JPA or summarize how it
> will impact generic Cayenne outer join support?
It is a part of EJBQL. So you can do queries like "SELECT c FROM
Customer c LEFT JOIN c.orders o WHERE c.status = 1". As we don't have
this piece nailed yet (whether we translate EJB QL to SQLTemplate or
SelectQuery, or something else remains to be seen), I am not entirely
clear what current API's this would affect. Another thing it affects
is prefetching (aka FETCH JOIN).
I guess if we support OUTER joins in expressions (and underlying
query translators), we'll figure out the EJBQL part later. So let's
not worry about bringing the two together at the moment.
Andrus
On Aug 17, 2006, at 11:02 AM, Mike Kienenberger wrote:
> On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>> Also there is an effort underway to support outer joins (we will have
>> to do it in 3.0 anyways as the goal is to be compatible with the JPA
>> spec that requires them):
>
> Andrus, I hadn't realized this was also needed for JPA. Can you
> provide me with shortcuts to just that part of JPA or summarize how it
> will impact generic Cayenne outer join support? My thought was to
> simply have a query.setJoinType(JoinType.OUTER) to enable and disable
> outer joins, but perhaps that's not enough.
>
> I don't really want to learn anything more about JPA than I have to at
> the moment :-)
>
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Mike Kienenberger <mk...@gmail.com>.
On 8/17/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> Also there is an effort underway to support outer joins (we will have
> to do it in 3.0 anyways as the goal is to be compatible with the JPA
> spec that requires them):
Andrus, I hadn't realized this was also needed for JPA. Can you
provide me with shortcuts to just that part of JPA or summarize how it
will impact generic Cayenne outer join support? My thought was to
simply have a query.setJoinType(JoinType.OUTER) to enable and disable
outer joins, but perhaps that's not enough.
I don't really want to learn anything more about JPA than I have to at
the moment :-)
Re: Cayenne does not throw an exception when an outer join is attempted
Posted by Andrus Adamchik <an...@objectstyle.org>.
> Would it make sense for Cayenne to throw an exception when an outer
> join is attempted?
Could you be more specific on when it should detect that? IIRC there
were some discussions on that in the past. You provide no references
though.
Also there is an effort underway to support outer joins (we will have
to do it in 3.0 anyways as the goal is to be compatible with the JPA
spec that requires them):
http://objectstyle.org/cayenne/lists/cayenne-devel/2006/08/0106.html
Andrus
On Aug 17, 2006, at 3:24 AM, Øyvind Harboe wrote:
> Outer joins are not supported, I know.
>
> Meanwhile I miss being *told* when I'm attempting an outer join,
> minimally runtime.
>
> One of the reasons I'm using Cayenne is that I don't want to think
> about SQL, so I don't. It hurts even thinking about thinking about how
> Cayenne Expressions turn into SQL statements :-)
>
> Would it make sense for Cayenne to throw an exception when an outer
> join is attempted?
>
> Could I attempt to implement such a detection in Cayenne myself and
> submit a patch or is it fiendishly difficult?
>
> --
> Øyvind Harboe
> http://www.zylin.com
>