You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@cayenne.apache.org by Andrus Adamchik <an...@objectstyle.org> on 2007/06/12 17:53:01 UTC
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
[I am taking this to the list.]
Expressions (or SelectQueries for that matter) are most certainly NOT
going away. IMO they should be preserved along the lines of the
original design. Whether they are going to evolve to incorporate
features like outer joins is another question, and this depends on
whether there are volunteers to do that.
I am personally happy with the complimentary approach: "select
queries/expressions for 80% of the ORM needs; EJBQL for the advanced
20%". So Mike, do you have bandwidth to port your patch to 3.0 and
write minimal docs and unit tests, so that we could make it official?
Another related issue is CAY-514 (split expressions). I think EJBQL
is going to solve that as well. So we need to make a similar decision.
One las question - a CAY-560 comment says "[provides] Outer Join
support as well as inequality expression null handling." Could you
remind what was the deal with inequality expression null handling?
Thanks
Andrus
On Jun 12, 2007, at 6:32 PM, Mike Kienenberger (JIRA) wrote:
>
> [ https://issues.apache.org/cayenne/browse/CAY-560?
> page=com.atlassian.jira.plugin.system.issuetabpanels:comment-
> tabpanel#action_12340 ]
>
> Mike Kienenberger commented on CAY-560:
> ---------------------------------------
>
> If we're going to continue supporting Expressions, I would think
> that would be a good idea. However, I don't know what the future
> of the original Cayenne expression language/query classes will be.
>
> I've been using the above patch for a long time now, and couldn't
> move to something that didn't support outer joins.
>
>> Add support for outer joins
>> ---------------------------
>>
>> Key: CAY-560
>> URL: https://issues.apache.org/cayenne/browse/CAY-560
>> Project: Cayenne
>> Issue Type: New Feature
>> Components: Cayenne Core Library
>> Reporter: Øyvind Harboe
>> Assignee: Mike Kienenberger
>> Fix For: 1.2 [STABLE]
>>
>> Attachments: 1.2-outerjoin-null-2006-09-05-01.txt
>>
>>
>> Currently outer joins are not supported.
>> See thread:
>> http://objectstyle.org/cayenne/lists/cayenne-user/2006/05/0165.html
>> // This Java code will result in the SQL query immediately below...
>> SelectQuery query1=new SelectQuery(ElcRole.class);
>> query1.setQualifier(ExpressionFactory.matchExp
>> (ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY + "." +
>> ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
>> orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY,
>> getSysUser())));
>> List l=context.performQuery(query1);
>> Generates =>
>>
>> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
>> FROM ELC_ROLE t0 LEFT OUTER JOIN
>> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
>> t1.ELCROLE_ID
>> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
>> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
>> BA97-2456E5CD3881')
>> But should(?) have generated =>
>> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
>> FROM ELC_ROLE t0 LEFT OUTER JOIN
>> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
>> t1.ELCROLE_ID
>> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
>> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
>> BA97-2456E5CD3881')
>>
>
> --
> This message is automatically generated by JIRA.
> -
> You can reply to this email to add a comment to the issue online.
>
>
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
Ok, got it. Yeah the DB would evaluate any condition that has a NULL
in it as FALSE. No objections to making Cayenne follow the Java
convention in that respect.
Andrus
On Jun 12, 2007, at 6:57 PM, Mike Kienenberger wrote:
> It was dealing with Oracle (and other Databases) requiring expressions
> in this format:
>
> (a <> ? or a is null)
>
> instead of
>
> (a <> ?)
>
> I'm pretty sure that was the expression, but I can't remember the
> details -- you were already aware of the problem at the time.
>
>
> On 6/12/07, Andrus Adamchik <an...@objectstyle.org> wrote:
>> One las question - a CAY-560 comment says "[provides] Outer Join
>> support as well as inequality expression null handling." Could you
>> remind what was the deal with inequality expression null handling?
>
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
It was dealing with Oracle (and other Databases) requiring expressions
in this format:
(a <> ? or a is null)
instead of
(a <> ?)
I'm pretty sure that was the expression, but I can't remember the
details -- you were already aware of the problem at the time.
On 6/12/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> One las question - a CAY-560 comment says "[provides] Outer Join
> support as well as inequality expression null handling." Could you
> remind what was the deal with inequality expression null handling?
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
I feel embarrassed every time this comes up:
http://objectstyle.org/cayenne/lists/cayenne-user/2007/07/0051.html
We definitely need to figure out a way to commit Mike's patch at some
point.
Andrus
On Jun 12, 2007, at 7:04 PM, Mike Kienenberger wrote:
> Split expressions are going to be critical to implementing useful
> outer joins. I'd say we need to do both or neither. My own
> implementation of split expressions is very weak (I just hacked it
> into the parser).
>
> If I recall, the EJBQL equivalents were less flexible than the
> combination of outer joins + split expressions I had, but perhaps I'm
> misremembering.
>
> As long as 3.0 query translation logic isn't exceedingly divergent
> from 1.2, I should be able to port my patch to it. However, I'm
> unlikely to have time to do so until late July. I had to do a lot of
> cleanup of the query translation logic, and if it's changed
> significantly, it'd be the equivalent of starting over.
>
> On 6/12/07, Andrus Adamchik <an...@objectstyle.org> wrote:
>> [I am taking this to the list.]
>>
>> Expressions (or SelectQueries for that matter) are most certainly NOT
>> going away. IMO they should be preserved along the lines of the
>> original design. Whether they are going to evolve to incorporate
>> features like outer joins is another question, and this depends on
>> whether there are volunteers to do that.
>>
>> I am personally happy with the complimentary approach: "select
>> queries/expressions for 80% of the ORM needs; EJBQL for the advanced
>> 20%". So Mike, do you have bandwidth to port your patch to 3.0 and
>> write minimal docs and unit tests, so that we could make it official?
>>
>> Another related issue is CAY-514 (split expressions). I think EJBQL
>> is going to solve that as well. So we need to make a similar
>> decision.
>>
>> One las question - a CAY-560 comment says "[provides] Outer Join
>> support as well as inequality expression null handling." Could you
>> remind what was the deal with inequality expression null handling?
>>
>> Thanks
>> Andrus
>>
>>
>> On Jun 12, 2007, at 6:32 PM, Mike Kienenberger (JIRA) wrote:
>> >
>> > [ https://issues.apache.org/cayenne/browse/CAY-560?
>> > page=com.atlassian.jira.plugin.system.issuetabpanels:comment-
>> > tabpanel#action_12340 ]
>> >
>> > Mike Kienenberger commented on CAY-560:
>> > ---------------------------------------
>> >
>> > If we're going to continue supporting Expressions, I would think
>> > that would be a good idea. However, I don't know what the future
>> > of the original Cayenne expression language/query classes will be.
>> >
>> > I've been using the above patch for a long time now, and couldn't
>> > move to something that didn't support outer joins.
>> >
>> >> Add support for outer joins
>> >> ---------------------------
>> >>
>> >> Key: CAY-560
>> >> URL: https://issues.apache.org/cayenne/browse/
>> CAY-560
>> >> Project: Cayenne
>> >> Issue Type: New Feature
>> >> Components: Cayenne Core Library
>> >> Reporter: Øyvind Harboe
>> >> Assignee: Mike Kienenberger
>> >> Fix For: 1.2 [STABLE]
>> >>
>> >> Attachments: 1.2-outerjoin-null-2006-09-05-01.txt
>> >>
>> >>
>> >> Currently outer joins are not supported.
>> >> See thread:
>> >> http://objectstyle.org/cayenne/lists/cayenne-user/
>> 2006/05/0165.html
>> >> // This Java code will result in the SQL query immediately
>> below...
>> >> SelectQuery query1=new SelectQuery(ElcRole.class);
>> >> query1.setQualifier(ExpressionFactory.matchExp
>> >> (ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY + "." +
>> >> ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
>> >> orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY,
>> >> getSysUser())));
>> >> List l=context.performQuery(query1);
>> >> Generates =>
>> >>
>> >> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
>> >> FROM ELC_ROLE t0 LEFT OUTER JOIN
>> >> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
>> >> t1.ELCROLE_ID
>> >> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
>> >> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
>> >> BA97-2456E5CD3881')
>> >> But should(?) have generated =>
>> >> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
>> >> FROM ELC_ROLE t0 LEFT OUTER JOIN
>> >> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
>> >> t1.ELCROLE_ID
>> >> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
>> >> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
>> >> BA97-2456E5CD3881')
>> >>
>> >
>> > --
>> > This message is automatically generated by JIRA.
>> > -
>> > You can reply to this email to add a comment to the issue online.
>> >
>> >
>>
>>
>
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
There's no rush as far as the timing. I was asking mainly to see
whether we should close those Jiras. So from this discussion we
should leave them open.
Andrus
On Jun 12, 2007, at 7:04 PM, Mike Kienenberger wrote:
> Split expressions are going to be critical to implementing useful
> outer joins. I'd say we need to do both or neither. My own
> implementation of split expressions is very weak (I just hacked it
> into the parser).
>
> If I recall, the EJBQL equivalents were less flexible than the
> combination of outer joins + split expressions I had, but perhaps I'm
> misremembering.
>
> As long as 3.0 query translation logic isn't exceedingly divergent
> from 1.2, I should be able to port my patch to it. However, I'm
> unlikely to have time to do so until late July. I had to do a lot of
> cleanup of the query translation logic, and if it's changed
> significantly, it'd be the equivalent of starting over.
>
> On 6/12/07, Andrus Adamchik <an...@objectstyle.org> wrote:
>> [I am taking this to the list.]
>>
>> Expressions (or SelectQueries for that matter) are most certainly NOT
>> going away. IMO they should be preserved along the lines of the
>> original design. Whether they are going to evolve to incorporate
>> features like outer joins is another question, and this depends on
>> whether there are volunteers to do that.
>>
>> I am personally happy with the complimentary approach: "select
>> queries/expressions for 80% of the ORM needs; EJBQL for the advanced
>> 20%". So Mike, do you have bandwidth to port your patch to 3.0 and
>> write minimal docs and unit tests, so that we could make it official?
>>
>> Another related issue is CAY-514 (split expressions). I think EJBQL
>> is going to solve that as well. So we need to make a similar
>> decision.
>>
>> One las question - a CAY-560 comment says "[provides] Outer Join
>> support as well as inequality expression null handling." Could you
>> remind what was the deal with inequality expression null handling?
>>
>> Thanks
>> Andrus
>>
>>
>> On Jun 12, 2007, at 6:32 PM, Mike Kienenberger (JIRA) wrote:
>> >
>> > [ https://issues.apache.org/cayenne/browse/CAY-560?
>> > page=com.atlassian.jira.plugin.system.issuetabpanels:comment-
>> > tabpanel#action_12340 ]
>> >
>> > Mike Kienenberger commented on CAY-560:
>> > ---------------------------------------
>> >
>> > If we're going to continue supporting Expressions, I would think
>> > that would be a good idea. However, I don't know what the future
>> > of the original Cayenne expression language/query classes will be.
>> >
>> > I've been using the above patch for a long time now, and couldn't
>> > move to something that didn't support outer joins.
>> >
>> >> Add support for outer joins
>> >> ---------------------------
>> >>
>> >> Key: CAY-560
>> >> URL: https://issues.apache.org/cayenne/browse/
>> CAY-560
>> >> Project: Cayenne
>> >> Issue Type: New Feature
>> >> Components: Cayenne Core Library
>> >> Reporter: Øyvind Harboe
>> >> Assignee: Mike Kienenberger
>> >> Fix For: 1.2 [STABLE]
>> >>
>> >> Attachments: 1.2-outerjoin-null-2006-09-05-01.txt
>> >>
>> >>
>> >> Currently outer joins are not supported.
>> >> See thread:
>> >> http://objectstyle.org/cayenne/lists/cayenne-user/
>> 2006/05/0165.html
>> >> // This Java code will result in the SQL query immediately
>> below...
>> >> SelectQuery query1=new SelectQuery(ElcRole.class);
>> >> query1.setQualifier(ExpressionFactory.matchExp
>> >> (ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY + "." +
>> >> ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
>> >> orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY,
>> >> getSysUser())));
>> >> List l=context.performQuery(query1);
>> >> Generates =>
>> >>
>> >> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
>> >> FROM ELC_ROLE t0 LEFT OUTER JOIN
>> >> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
>> >> t1.ELCROLE_ID
>> >> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
>> >> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
>> >> BA97-2456E5CD3881')
>> >> But should(?) have generated =>
>> >> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
>> >> FROM ELC_ROLE t0 LEFT OUTER JOIN
>> >> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
>> >> t1.ELCROLE_ID
>> >> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
>> >> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
>> >> BA97-2456E5CD3881')
>> >>
>> >
>> > --
>> > This message is automatically generated by JIRA.
>> > -
>> > You can reply to this email to add a comment to the issue online.
>> >
>> >
>>
>>
>
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
Posted by Andrus Adamchik <an...@objectstyle.org>.
On Jun 12, 2007, at 7:04 PM, Mike Kienenberger wrote:
> As long as 3.0 query translation logic isn't exceedingly divergent
> from 1.2, I should be able to port my patch to it.
An algorithm note. While we are not yet rewriting SelectQuery
translator (so that's an FYI), in EJBQL work I find it very helpful
to convert the object query to n intermediate SQLTemplate, instead of
converting it to SQL directly. This allows to define parameters in
the generated SQL and use Velocity directives. Makes the translator
code much cleaner, and results in the PreparedStatement code reuse.
On the downside, that's an extra step in the query pipeline, so there
may be a performance hit.
Andrus
Re: [JIRA] Commented: (CAY-560) Add support for outer joins
Posted by Mike Kienenberger <mk...@gmail.com>.
Split expressions are going to be critical to implementing useful
outer joins. I'd say we need to do both or neither. My own
implementation of split expressions is very weak (I just hacked it
into the parser).
If I recall, the EJBQL equivalents were less flexible than the
combination of outer joins + split expressions I had, but perhaps I'm
misremembering.
As long as 3.0 query translation logic isn't exceedingly divergent
from 1.2, I should be able to port my patch to it. However, I'm
unlikely to have time to do so until late July. I had to do a lot of
cleanup of the query translation logic, and if it's changed
significantly, it'd be the equivalent of starting over.
On 6/12/07, Andrus Adamchik <an...@objectstyle.org> wrote:
> [I am taking this to the list.]
>
> Expressions (or SelectQueries for that matter) are most certainly NOT
> going away. IMO they should be preserved along the lines of the
> original design. Whether they are going to evolve to incorporate
> features like outer joins is another question, and this depends on
> whether there are volunteers to do that.
>
> I am personally happy with the complimentary approach: "select
> queries/expressions for 80% of the ORM needs; EJBQL for the advanced
> 20%". So Mike, do you have bandwidth to port your patch to 3.0 and
> write minimal docs and unit tests, so that we could make it official?
>
> Another related issue is CAY-514 (split expressions). I think EJBQL
> is going to solve that as well. So we need to make a similar decision.
>
> One las question - a CAY-560 comment says "[provides] Outer Join
> support as well as inequality expression null handling." Could you
> remind what was the deal with inequality expression null handling?
>
> Thanks
> Andrus
>
>
> On Jun 12, 2007, at 6:32 PM, Mike Kienenberger (JIRA) wrote:
> >
> > [ https://issues.apache.org/cayenne/browse/CAY-560?
> > page=com.atlassian.jira.plugin.system.issuetabpanels:comment-
> > tabpanel#action_12340 ]
> >
> > Mike Kienenberger commented on CAY-560:
> > ---------------------------------------
> >
> > If we're going to continue supporting Expressions, I would think
> > that would be a good idea. However, I don't know what the future
> > of the original Cayenne expression language/query classes will be.
> >
> > I've been using the above patch for a long time now, and couldn't
> > move to something that didn't support outer joins.
> >
> >> Add support for outer joins
> >> ---------------------------
> >>
> >> Key: CAY-560
> >> URL: https://issues.apache.org/cayenne/browse/CAY-560
> >> Project: Cayenne
> >> Issue Type: New Feature
> >> Components: Cayenne Core Library
> >> Reporter: Øyvind Harboe
> >> Assignee: Mike Kienenberger
> >> Fix For: 1.2 [STABLE]
> >>
> >> Attachments: 1.2-outerjoin-null-2006-09-05-01.txt
> >>
> >>
> >> Currently outer joins are not supported.
> >> See thread:
> >> http://objectstyle.org/cayenne/lists/cayenne-user/2006/05/0165.html
> >> // This Java code will result in the SQL query immediately below...
> >> SelectQuery query1=new SelectQuery(ElcRole.class);
> >> query1.setQualifier(ExpressionFactory.matchExp
> >> (ElcRole.ELC_ROLEMEMBER_ARRAY_PROPERTY + "." +
> >> ElcRolemember.TO_SYS_USER_PROPERTY, getSysUser()).
> >> orExp(ExpressionFactory.matchExp(ElcRole.TO_SYS_USER_PROPERTY,
> >> getSysUser())));
> >> List l=context.performQuery(query1);
> >> Generates =>
> >>
> >> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
> >> FROM ELC_ROLE t0 LEFT OUTER JOIN
> >> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
> >> t1.ELCROLE_ID
> >> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
> >> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
> >> BA97-2456E5CD3881')
> >> But should(?) have generated =>
> >> SELECT DISTINCT t0.ELCROLE_NAME, t0.ELCROLE_ID, t0.SYSUSER_ID
> >> FROM ELC_ROLE t0 LEFT OUTER JOIN
> >> ELC_ROLEMEMBER t1 ON t0.ELCROLE_ID =
> >> t1.ELCROLE_ID
> >> WHERE (t1.SYSUSER_ID = 'BD93F348-8C02-4742-BA97-2456E5CD3881') OR
> >> (t0.SYSUSER_ID = 'BD93F348-8C02-4742-
> >> BA97-2456E5CD3881')
> >>
> >
> > --
> > This message is automatically generated by JIRA.
> > -
> > You can reply to this email to add a comment to the issue online.
> >
> >
>
>