FWD » Core Development » Database

This issue stems from note 127 in #2068.

The following code:

DEFINE TEMP-TABLE tt-pa1 LIKE pers-addr.
DEFINE TEMP-TABLE tt-pa2 LIKE pers-addr.

DEFINE BUFFER b-pers-addr FOR pers-addr.
DEFINE BUFFER b-tt-pa1    FOR tt-pa1.

CREATE tt-pa1.
FIND FIRST person NO-LOCK.
FIND FIRST pers-addr NO-LOCK.

FOR EACH b-pers-addr WHERE b-pers-addr.site-id = person.site-id
                       AND b-pers-addr.emp-num = pers-addr.emp-num,
    EACH b-tt-pa1    WHERE b-tt-pa1.site-id = person.site-id
                       AND (CAN-FIND(FIRST tt-pa2 WHERE tt-pa2.site-id = tt-pa1.site-id
                                                    AND tt-pa2.emp-num = b-pers-addr.emp-num
                                                    AND tt-pa2.active)
                            OR CAN-FIND(address   WHERE address.addr-id = b-tt-pa1.emp-num
                                                    AND address.addr-id = b-pers-addr.emp-num)):
   MESSAGE("whatever").
END.

...converts to this (portions relevant to FOR EACH only):

WhereExpression whereExpr0 = new WhereExpression()
{
   public Object[] getSubstitutions()
   {
      return new Object[]
      {
         new LogicalExpression()
         {
                           public logical execute()
            {
               return new RandomAccessQuery(ttPa2, "ttPa2.siteId = ? and ttPa2.empNum = bPersAddr.empNum and ttPa2.active = true", null, (String) null, LockType.NONE).hasAny();
            }
         }
      };
   }

   public logical evaluate(final BaseDataType[] args)
   {
      return or((logical) args[0], new LogicalExpression()
      {
         public logical execute()
         {
            return new FindQuery(address, "address.addrId = ? and address.addrId = ?", null, (String) null, new Object[]
            {
               new FieldReference(bTtPa1, "empNum"),
               new FieldReference(bPersAddr, "empNum")
            }, LockType.NONE).hasOne();
         }
      });
   }
};

...

RecordBuffer.openScope(bTtPa1, bPersAddr);
query0 = new CompoundQuery(false, false);
RecordBuffer.prepare(bPersAddr);
query0.addComponent(new AdaptiveQuery(bPersAddr, "bPersAddr.siteId = ? and bPersAddr.empNum = ?", null, "bPersAddr.siteId asc, bPersAddr.empNum asc", new Object[]
{
   person.getSiteId(),
   persAddr.getEmpNum()
}));
RecordBuffer.prepare(bTtPa1);
query0.addComponent(new AdaptiveQuery(bTtPa1, "bTtPa1.siteId = ?exists(from TempRecord2 as ttPa2 where ttPa2.siteId = ? and ttPa2.empNum = bPersAddr.empNum and ttPa2.active = true)", whereExpr0, "bTtPa1.siteId asc, bTtPa1.empNum asc", new Object[]
{
   person.getSiteId(),
   ttPa1.getSiteId()
}));

The first component of the CompoundQuery is OK, but the second is just all wrong.

I think the WhereExpression.execute method should return an or expression with 2 FindQuerys, and the RandomAccessQuery in the getSubstitutions method shouldn't be there.

I'm working toward what I think the correct conversion would be:

WhereExpression whereExpr0 = new WhereExpression()
{
   public logical evaluate(final BaseDataType[] args)
   {
      return or(new FindQuery(ttPa2, "ttPa2.siteId = ? and ttPa2.empNum = ? and ttPa2.active = true", null, (String) null, new Object[]
      {
         new FieldReference(ttPa1, "siteId"),
         new FieldReference(bPersAddr, "empNum")
      }, LockType.NONE).hasAny(), new LogicalExpression()
      {
         public logical execute()
         {
            return new FindQuery(address, "address.addrId = ? and address.addrId = ?", null, (String) null, new Object[]
            {
               new FieldReference(bTtPa1, "empNum"),
               new FieldReference(bPersAddr, "empNum")
            }, LockType.NONE).hasOne();
         }
      });
   }
};

...

RecordBuffer.openScope(bTtPa1, bPersAddr);
query0 = new CompoundQuery(false, false);
RecordBuffer.prepare(bPersAddr);
query0.addComponent(new AdaptiveQuery(bPersAddr, "bPersAddr.siteId = ? and bPersAddr.empNum = ?", null, "bPersAddr.siteId asc, bPersAddr.empNum asc", new Object[]
{
   person.getSiteId(),
   persAddr.getEmpNum()
}));
RecordBuffer.prepare(bTtPa1);
query0.addComponent(new AdaptiveQuery(bTtPa1, "bTtPa1.siteId = ?", whereExpr0, "bTtPa1.siteId asc, bTtPa1.empNum asc", new Object[]
{
   person.getSiteId()
}));

A bit ugly, but I think it represents the logically correct conversion.

This update regresses many files in Majic; cannot check it in.

While there are a number of expected differences, there are also still some regressions, though far fewer than the last pass. This update cannot be checked in.

I have isolated the #2068 note 272 regression like this:

def temp-table w-person like person.

/* this one works */
for each person,
    each w-person where w-person.emp-num = ? or w-person.emp-num <= person.emp-num:
end.

/* this one doesn't work, as client side where expression is emitted and CompareOps.isLessThanOrEqual(BDT, WrappedResource) doesn't exist */
for each person,
    each w-person where person.emp-num = ? or w-person.emp-num <= person.emp-num:
end.

LE: how the Java code looks:

   WhereExpression whereExpr0 = new WhereExpression()
   {
      public logical evaluate(final BaseDataType[] args)
      {
         return or(isUnknown(person.getEmpNum()), new LogicalExpression()
         {
            public logical execute()
            {
               return isLessThanOrEqual(wPerson.getEmpNum(), new FieldReference(person, "empNum"));
            }
         });
      }
   };
...
            forEach("loopLabel0", new Block()
            {
               CompoundQuery query0 = null;

               public void init()
               {
                  RecordBuffer.openScope(person);
                  query0 = new CompoundQuery(false, false);
                  query0.addComponent(new AdaptiveQuery(person, (String) null, null, "person.siteId asc, person.empNum asc"));
                  RecordBuffer.prepare(wPerson);
                  query0.addComponent(new AdaptiveQuery(wPerson, "wPerson.empNum is null or wPerson.empNum <= ?", null, "wPerson.siteId asc, wPerson.empNum asc", new Object[]
                  {
                     new FieldReference(person, "empNum")
                  }));
               }

               public void body()
               {
                  query0.iterate();
               }
            });

            forEach("loopLabel1", new Block()
            {
               CompoundQuery query1 = null;

               public void init()
               {
                  RecordBuffer.openScope(person);
                  query1 = new CompoundQuery(false, false);
                  query1.addComponent(new AdaptiveQuery(person, (String) null, null, "person.siteId asc, person.empNum asc"));
                  RecordBuffer.prepare(wPerson);
                  query1.addComponent(new AdaptiveQuery(wPerson, (String) null, whereExpr0, "wPerson.siteId asc, wPerson.empNum asc", new Object[]
                  {
                     new FieldReference(person, "empNum")
                  }));
               }

               public void body()
               {
                  query1.iterate();
               }
            });

The reason why the where clause filtered to client-side is probably the where_clause_prep2.rules. You can read in its header:

This rule set continues the work of where_clause_prep by further
analyzing WHERE clauses for the purpose of detecting client-side where
clauses and generating partial HQL statements downstream.  Partial HQL
will be derived from any components of a where clause which can be
isolated to reduce the result set which must be processed on the client.
This type of partial restriction of results is only possible if the
where clause is a conjunction expression (using AND) at the root of the
expression tree.  *Disjunction expressions (using OR) must execute
entirely on the client.*

This is the case, indeed the disjunction is the root of the expression but I also think (looking at the conditionals forms) that both where clauses could be executed on server-side. In fact, if both sub-trees can be executed server-side then I believe that we can execute both the conjuction and their disjunction on server-side.

Ovidiu Maxiniuc wrote:

The reason why the where clause filtered to client-side is probably the where_clause_prep2.rules. You can read in its header:
[...]

This is the case, indeed the disjunction is the root of the expression but I also think (looking at the conditionals forms) that both where clauses could be executed on server-side. In fact, if both sub-trees can be executed server-side then I believe that we can execute both the conjuction and their disjunction on server-side.

Sorry, my wording in the header was poor. The idea I meant to convey was that if any part of the expression was deemed to require client-side processing, the fact that that part existed inside an OR expression would pull in the rest of the OR expression to client-side processing as well. In the case of nested OR expressions, it pulls in the top-most OR expression. Even now, my wording on this is not great, but hopefully you get the idea.

I understand. The algorithm is working in fact if you analyze the first clause that works.
And starting from here, I investigated the differences between the two forms.
IIRC, after parsing, the basic node looks something like this:

for
  each
    person
  each
    w-person
    where
      or
      ...

Ovidiu Maxiniuc wrote:

...but I wonder if we shouldn't treat the first each person the same like each w-person and other each buffer from the same level?

No, these buffers come from different databases (person from p2j_test and w-person from _temp, so we have to be very careful when composing HQL to not assume they are similar in that way. We use the current_buffer and related_buffer annotations (among other things) to help us make the determination of how to convert.

In this case, this construct will convert to a CompoundQuery, which joins records on the "client" (i.e., the P2J application server). The reference to person in the inner where clause really should be part of a query substitution parameter, but one that gets re-evaluated at each pass of the outer loop. But the comparison with the unknown literal (person.emp-num = ?) prevents this from converting that way, so it determines that this clause has to be refactored into a client where expression.

Another part of the problem is that person.emp-num is still being treated as a regular, query substitution parameter, so it is emitting as new FieldReference(person, "empNum") for the inner EACH, even though there is no where clause into which to substitute it (because it was moved to a client where expression).

The update has passed conversion regression testing and is committed to bzr rev. 10333.