Idea for handling splits, possibly with only minor changes to alias table handling

[Mike Kienenberger <mk...@gmail.com>]

Here's an idea I had for handling splits.

Right now, QueryAsembler.dbRelationshipAdded(..) removes "duplicates".
If instead we temporarily "overrode" aliases when resolving a split
expression, it seems as if this might work without any further
changes.

Going back to my standard example:

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 = ?))))

We start off with an alias table

FEE = t0
FEE_TYPE = t1
FEE_CYLE = t2

Now we process an expression with a split

AUTHORIZATION_DOCUMENT = t3
PERMIT_DOCUMENT = t4

the split expression ends, so we revert these two values.

Next expression with a split gives us

AUTHORIZATION_DOCUMENT = t5
PERMIT_DOCUMENT = t6

And these are again reverted in the alias table when the split expression ends.

The reason I say reverted is that there may be regular inner joins
(non-split) against these same tables.   My example doesn't really
show that off well.

Assume there's another AUTHORIZATION_DOCUMENT.date = yyyy-mm-dd phrase
that is an inner join.   It already exists as an alias of
AUTHORIZATION_DOCUMENT = t2Point5, so we want this to still be back in
the alias table as soon as we are no longer processing the split.

Hope this makes sense.   I don't feel I'm explaining it very well.
It's probably easier to implement than to explain, so I think this
will be my next step.

Re: Idea for handling splits, possibly with only minor changes to alias table handling

[Mike Kienenberger <mk...@gmail.com>]

Looks like I'm on the right track for handling splits.  My baseline
query now generates correctly.   However, there's still a few bugs.
The second sql splits the joins correctly, but doesn't quite assign
the qualifier aliases correctly in the search expressions.   This may
simply be a matter of timing when generating the search expression
sql.

I'm feeling a bit more confident about the whole process at this point.

SELECT * FROM (

SELECT t0.AMOUNT_DUE, t0.REQUIRED_DEPOSIT_AMOUNT_DUE, t0.FEE_ID, t0.FEE_TYPE_ID

FROM ENG_WORK_MGMT.FEE t0,
 ENG_WORK_MGMT.FEE_TYPE t1,
 ENG_WORK_MGMT.FEE_CYCLE t2,
 ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
 ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
 ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5,
 ENG_WORK_MGMT.PERMIT_DOCUMENT t6

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 t3.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
 AND (
   (t1.DESCRIPTION = ?)
   AND (
     (RTRIM(t4.IS_ACTIVE(+)) <> ?) OR (RTRIM(t6.IS_ACTIVE(+)) <> ?)

))) WHERE rownum <= 100 [bind: 'EDMS', 'Y', 'Y']



SELECT * FROM (

SELECT t0.AMOUNT_DUE, t0.REQUIRED_DEPOSIT_AMOUNT_DUE, t0.FEE_ID, t0.FEE_TYPE_ID

FROM
 ENG_WORK_MGMT.FEE t0,
 ENG_WORK_MGMT.FEE_TYPE t1,
 ENG_WORK_MGMT.FEE_CYCLE t2,
 ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t3,
 ENG_WORK_MGMT.PERMIT_DOCUMENT t4,
 ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t5,
 ENG_WORK_MGMT.PERMIT_DOCUMENT t6,
 ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t7,
 ENG_WORK_MGMT.PERMIT_DOCUMENT t8,
 ENG_WORK_MGMT.AUTHORIZATION_DOCUMENT t9,
 ENG_WORK_MGMT.PERMIT_DOCUMENT t10

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 t3.AUTHORIZATION_DOCUMENT_ID = t6.PERMIT_DOCUMENT_ID(+)
 AND t2.FEE_CYCLE_ID = t7.INITIAL_FEE_CYCLE_ID(+)
 AND t3.AUTHORIZATION_DOCUMENT_ID = t8.PERMIT_DOCUMENT_ID(+)
 AND t2.FEE_CYCLE_ID = t9.RECURRING_FEE_CYCLE_ID(+)
 AND t3.AUTHORIZATION_DOCUMENT_ID = t10.PERMIT_DOCUMENT_ID(+)
 AND (

  (t1.DESCRIPTION = ?)
  AND (
    (RTRIM(t4.IS_ACTIVE(+)) <> ?) OR (RTRIM(t6.IS_ACTIVE(+)) <> ?)
  )
  AND (
    (t4.AGENCY_ID = ?) OR (t4.AGENCY_ID = ?)
  ))) WHERE rownum <= 100 [bind: 'EDMS', 'Y', 'Y', 100, 100]


On 8/20/06, Mike Kienenberger <mk...@gmail.com> wrote:
> Yeah, I'm trying to see if I can find a short-term solution rather
> than having to reimplement everything all at once.
>
> I have everything working with the + notation now, except for the
> split and null issues.
> It looks like this will work to handle the split implementation, so
> "+" would mean both split and outer join in my use case.  I will
> hopefully know sometime tomorrow :-)
>
> I think what you proposed is workable, but there's some unanswered
> questions, like how splits would be configured.  I suppose it'd have
> to be done manually.
>
> I also need to convert from Oracle 8 notation to the generic notation.
>   (Oracle 8 doesn't allow outer joins with OR), but I think this will
> be pretty trivial compared to what's been done so far.
>
> If I can get all of this working, then providing the EJB QL-compatible
> sytax down the road shouldn't be too difficult.
>
> On 8/20/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> >
> > On Aug 20, 2006, at 10:32 PM, Mike Kienenberger wrote:
> >
> > > I didn't state how a user would configure it, only how it could be
> > > processed if it was specified.
> >
> > Ah, ok.
> >
> > > Right now, I'm leaning on making
> > > join semantics represent this.    So a query can be (inner|outer) +
> > > (split|nosplit).    Realistically, that's probably only inner, inner
> > > w/split, and outer w/split as the three options.
> >
> > I guess that's JPA join semantics?
> >
> > > Your original idea of "+" and "|" works as part of the path expression
> > > works for me as well.
> >
> > I don't mind this as an alternative. I just thought it would be cool
> > if we find synergy between EJB QL and our current SelectQuery (and I
> > think we came close). If it turns out that we are stretching it too
> > much, we can use this semantics instead.
> >
> > Andrus
> >
> >
>

Re: Idea for handling splits, possibly with only minor changes to alias table handling

[Mike Kienenberger <mk...@gmail.com>]

Yeah, I'm trying to see if I can find a short-term solution rather
than having to reimplement everything all at once.

I have everything working with the + notation now, except for the
split and null issues.
It looks like this will work to handle the split implementation, so
"+" would mean both split and outer join in my use case.  I will
hopefully know sometime tomorrow :-)

I think what you proposed is workable, but there's some unanswered
questions, like how splits would be configured.  I suppose it'd have
to be done manually.

I also need to convert from Oracle 8 notation to the generic notation.
  (Oracle 8 doesn't allow outer joins with OR), but I think this will
be pretty trivial compared to what's been done so far.

If I can get all of this working, then providing the EJB QL-compatible
sytax down the road shouldn't be too difficult.

On 8/20/06, Andrus Adamchik <an...@objectstyle.org> wrote:
>
> On Aug 20, 2006, at 10:32 PM, Mike Kienenberger wrote:
>
> > I didn't state how a user would configure it, only how it could be
> > processed if it was specified.
>
> Ah, ok.
>
> > Right now, I'm leaning on making
> > join semantics represent this.    So a query can be (inner|outer) +
> > (split|nosplit).    Realistically, that's probably only inner, inner
> > w/split, and outer w/split as the three options.
>
> I guess that's JPA join semantics?
>
> > Your original idea of "+" and "|" works as part of the path expression
> > works for me as well.
>
> I don't mind this as an alternative. I just thought it would be cool
> if we find synergy between EJB QL and our current SelectQuery (and I
> think we came close). If it turns out that we are stretching it too
> much, we can use this semantics instead.
>
> Andrus
>
>

Re: Idea for handling splits, possibly with only minor changes to alias table handling

[Andrus Adamchik <an...@objectstyle.org>]

On Aug 20, 2006, at 10:32 PM, Mike Kienenberger wrote:

> I didn't state how a user would configure it, only how it could be
> processed if it was specified.

Ah, ok.

> Right now, I'm leaning on making
> join semantics represent this.    So a query can be (inner|outer) +
> (split|nosplit).    Realistically, that's probably only inner, inner
> w/split, and outer w/split as the three options.

I guess that's JPA join semantics?

> Your original idea of "+" and "|" works as part of the path expression
> works for me as well.

I don't mind this as an alternative. I just thought it would be cool  
if we find synergy between EJB QL and our current SelectQuery (and I  
think we came close). If it turns out that we are stretching it too  
much, we can use this semantics instead.

Andrus

Re: Idea for handling splits, possibly with only minor changes to alias table handling

[Mike Kienenberger <mk...@gmail.com>]

I didn't state how a user would configure it, only how it could be
processed if it was specified.    Right now, I'm leaning on making
join semantics represent this.    So a query can be (inner|outer) +
(split|nosplit).    Realistically, that's probably only inner, inner
w/split, and outer w/split as the three options.

Your original idea of "+" and "|" works as part of the path expression
works for me as well.


On 8/20/06, Andrus Adamchik <an...@objectstyle.org> wrote:
> > Hope this makes sense.   I don't feel I'm explaining it very well.
> > It's probably easier to implement than to explain, so I think this
> > will be my next step.
>
> Indeed, I got lost on how a user would configure split vs. no-split.
> Could you post a sample SelectQuery?
>
> Andrus
>
>
>
>
> On Aug 20, 2006, at 4:03 AM, Mike Kienenberger wrote:
>
> > Here's an idea I had for handling splits.
> >
> > Right now, QueryAsembler.dbRelationshipAdded(..) removes "duplicates".
> > If instead we temporarily "overrode" aliases when resolving a split
> > expression, it seems as if this might work without any further
> > changes.
> >
> > Going back to my standard example:
> >
> > 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 = ?))))
> >
> > We start off with an alias table
> >
> > FEE = t0
> > FEE_TYPE = t1
> > FEE_CYLE = t2
> >
> > Now we process an expression with a split
> >
> > AUTHORIZATION_DOCUMENT = t3
> > PERMIT_DOCUMENT = t4
> >
> > the split expression ends, so we revert these two values.
> >
> > Next expression with a split gives us
> >
> > AUTHORIZATION_DOCUMENT = t5
> > PERMIT_DOCUMENT = t6
> >
> > And these are again reverted in the alias table when the split
> > expression ends.
> >
> > The reason I say reverted is that there may be regular inner joins
> > (non-split) against these same tables.   My example doesn't really
> > show that off well.
> >
> > Assume there's another AUTHORIZATION_DOCUMENT.date = yyyy-mm-dd phrase
> > that is an inner join.   It already exists as an alias of
> > AUTHORIZATION_DOCUMENT = t2Point5, so we want this to still be back in
> > the alias table as soon as we are no longer processing the split.
> >
> > Hope this makes sense.   I don't feel I'm explaining it very well.
> > It's probably easier to implement than to explain, so I think this
> > will be my next step.
> >
>
>

Re: Idea for handling splits, possibly with only minor changes to alias table handling

[Andrus Adamchik <an...@objectstyle.org>]

> Hope this makes sense.   I don't feel I'm explaining it very well.
> It's probably easier to implement than to explain, so I think this
> will be my next step.

Indeed, I got lost on how a user would configure split vs. no-split.  
Could you post a sample SelectQuery?

Andrus




On Aug 20, 2006, at 4:03 AM, Mike Kienenberger wrote:

> Here's an idea I had for handling splits.
>
> Right now, QueryAsembler.dbRelationshipAdded(..) removes "duplicates".
> If instead we temporarily "overrode" aliases when resolving a split
> expression, it seems as if this might work without any further
> changes.
>
> Going back to my standard example:
>
> 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 = ?))))
>
> We start off with an alias table
>
> FEE = t0
> FEE_TYPE = t1
> FEE_CYLE = t2
>
> Now we process an expression with a split
>
> AUTHORIZATION_DOCUMENT = t3
> PERMIT_DOCUMENT = t4
>
> the split expression ends, so we revert these two values.
>
> Next expression with a split gives us
>
> AUTHORIZATION_DOCUMENT = t5
> PERMIT_DOCUMENT = t6
>
> And these are again reverted in the alias table when the split  
> expression ends.
>
> The reason I say reverted is that there may be regular inner joins
> (non-split) against these same tables.   My example doesn't really
> show that off well.
>
> Assume there's another AUTHORIZATION_DOCUMENT.date = yyyy-mm-dd phrase
> that is an inner join.   It already exists as an alias of
> AUTHORIZATION_DOCUMENT = t2Point5, so we want this to still be back in
> the alias table as soon as we are no longer processing the split.
>
> Hope this makes sense.   I don't feel I'm explaining it very well.
> It's probably easier to implement than to explain, so I think this
> will be my next step.
>