FWD » Core Development » Database

This task extracts the issue from #8752-3 and discussion from #6667-1054 to #6667-1087.

def temp-table tt field f1 as int index idx f1 desc.

create tt.
tt.f1 = 1.
create tt.
tt.f1 = 2.

open query q2 for each tt by f1.
get first q2.

open query q for each tt by f1.
get next q.
message tt.f1. // 4GL 1 (It is the first record found by q by f1 asc - not affected by q2)

find next tt.
message tt.f1. // 4GL 2 (It is the first record found by the FIND by f1 desc - not affected by q or q2)

open query q3 for each tt by f1.
get first q3.
get next q3.
message tt.f1. // 4GL 2 (It is the second record found by q3 by f1 asc - not affected by previous queries)

find next tt.
message tt.f1. // 4GL 1 (!!!! Important: It is the second record found by FIND by f1 desc)

The issue is that 4GL seems to use a buffer-level cursor to resolve the FIND queries, while FWD is honoring some offEnd and sortIndex properties of the RecordBuffer that can be spoiled by other queries that are run in parallel. Also, the cursor is not stateful in FWD, in the sense that FindQuery is simply honoring the current record loaded in the buffer (which can be loaded by another query).

Alexandru, this may not be a query, but a cursor on the index. For example, you can have FIND statements with different WHERE clauses (which can trigger different indexes, depending on the WHERE clause?).

Also, the BUFFER:FIND- methods do not affect this cursor:

def temp-table tt field f1 as int index idx f1 desc.

create tt.
tt.f1 = 1.
create tt.
tt.f1 = 2.
create tt.
tt.f1 = 3.
create tt.
tt.f1 = 4.
release tt.

find first tt.
message tt.f1. // 4

def var hb as handle.
hb = buffer tt:handle.
hb:find-unique("where tt.f1 = 2").
message tt.f1. // 2
find next tt.
message tt.f1. // 3

And not even release - FIND will still know on which record is positioned:

def temp-table tt field f1 as int index idx f1 desc.

create tt.
tt.f1 = 1.
create tt.
tt.f1 = 2.
create tt.
tt.f1 = 3.
create tt.
tt.f1 = 4.
release tt.

find first tt.
message tt.f1. // 4

release tt.

find next tt.
message tt.f1. // 3

Another issue is the AdaptiveFind, which is a performance improvement in FWD for looped FIND statements. This in turn can create a RandomAccessQuery, which needs to act like a FIND, and not as an internal RandomAccessQuery which can be used for AdaptiveQuery in FOR EACH blocks, for example.

I have some changes from trying to solve this for #9015 (I didn't need them in the end for that task), and my problem in those changes is that I'm using a record as the 'cursor' for the FIND statements. And this record can get deleted or evicted from the cache.

Alexandru, this may not be a query, but a cursor on the index. For example, you can have FIND statements with different WHERE clauses (which can trigger different indexes, depending on the WHERE clause?).

I see your point. Tested:

def temp-table tt field f1 as int field f2 as int index idx1 as unique f1 index idx2 as unique f2.
do transaction:
    create tt.
    tt.f1 = 1.
    tt.f2 = 1.
    create tt.
    tt.f1 = 2.
    tt.f2 = 2.
    create tt.
    tt.f1 = 3.
    tt.f2 = 3.
    release tt.
end.

find first tt where f1 > 0.
message tt.f1. // 1
find next tt where f1 > 0.
message tt.f1. // 2
find next tt where f2 > 0.
message tt.f2. // 3

This shows that the last NEXT is still honoring the current cursor and not creating another one. So this is not a cursor on an index, but neither a query with a fixed WHERE clause.
I am rather thinking this does a "snapshot" of the last loaded record by a FIND and uses that as a reference record.
So this is basically what we have now in the FWD run-time, but instead of using the currently loaded record in the buffer as reference, it should use the "last loaded record by a FIND".

Did we recently make changes that caused this issue to be a problem? Surely this has not always been an issue.

Trunk has a change to invalidate AdaptiveQuery right from the start if there are records in the dirty database. This caused some FOR EACH (which were doing large database SQLs) to work as a sequence of FIND NEXT (doing one-by-one retriaval from the database).

I suspect that this latent problem with offEnd and sortIndex is now more visible, considering that more queries behave like a sequence of FIND NEXT, requiring the offEnd and sortIndex to work properly.

The regression in #9015-29 is from trunk rev 15325, for #8433, where a FIND NEXT without the strong scope needs to stop finding records on the second iteration, like this:

do transaction:
   for each book: delete book. end.
   create book.
   book.book-id = 1.
   book.book-title = "abc".
   book.isbn = "1".
   release book.
   create book.
   book.book-id = 2.
   book.book-title = "abc".
   book.isbn = "2".
   release book.
end.

def buffer bbook for book.
def var i as int.
def var k as int.

do while true:
   // do for bbook while true:
    do while true:
      find next bbook where bbook.book-id > i no-error.
      if not avail bbook then leave.
      message k bbook.book-id.
   end.
   pause.
   k = k + 1.
end.

The strong scope makes the buffer re-open its most outer scope, and this requires its offend to reset (at least).

Otherwise, the findings in #9030-1 is new behavior which was not observed until now - I don't think this ever worked properly in FWD.

Alexandru, I've created task branch 9030a from trunk rev 15372. This is the first attempt to solve the buffer's internal cursor on which FIND statements use as a reference to navigate.

Note that the buffer's internal offend I think needs to be limited only to FIND statements, and not internal RandomAccessQuery's used for i.e. FOR EACH/FIRST loops.

BTW, in this attempt, the buffer's offend still had to be reset on outermost query scope. So it may be that this change is required, but not the complete fix.

Alexandru, I've created task branch 9030a from trunk rev 15372. This is the first attempt to solve the buffer's internal cursor on which FIND statements use as a reference to navigate.

I am deferring some of the discussions on #9241 to this task.

def temp-table tt field f1 as int index idx f1.

do transaction:
    create tt. tt.f1 = 1.
    create tt. tt.f1 = 3.
end.

message "example 1".
do transaction:
    find next tt where tt.f1 = 5 no-error.
    message avail(tt). // no
    find next tt where tt.f1 = 3 no-error. // find after 5
    message avail(tt). // no
end.

message "example 2".
do transaction:
    define buffer buf for tt.
    find next buf where buf.f1 = 2 no-error.
    message avail(buf). // no
    find next buf where buf.f1 = 3 no-error. // find after 2
    message avail(buf) buf.f1. // yes 3
end.

This shows that the reference used by FIND NEXT is not a record. In example 1, the reference is a virtual location in the index that has f1 = 5. In the second example, the virtual location is on f1 = 3.
I am checking out 9030a to review the current implementation. I also have some changes to FqlPreprocessor to detect the indexed properties used in range index queries.
The idea is to store a bit of information on the RecordBuffer reflecting the last FIND NEXT executed on it, so the next FIND NEXT will use it as reference. Instead of using a reference record, it will use a reference virtual record if that makes sense.

• attempt to analyze the where similar to the OE DB planner, identifying the parts used for the index range query and the parts used for filtering.
  • I am starting light: the where should formed out of AND clauses. We are interested only on the flat property matches over indexed fields - these are used for the index range query. Other clauses can be considered part of the filtering.
  • The virtual record will be formed out of the r-values from the property matches that resemble a prefix of the index (first N-1 should be EQUAL, Nth can be either equal or />= comparison for DESC).
  • I am planning to extend collectPropertyMatches to do that.
• after the execution of FIND NEXT, if no record is found, store the virtual record at the Record Buffer level.
• each subsequent FIND NEXT will augment with an active bundle based on this virtual record.
• what triggers the reset of the virtual record? still to be investigated.

Last interesting finding:

def temp-table tt field f1 as int index idx f1.

do transaction:
    create tt. tt.f1 = 1.
    create tt. tt.f1 = 3.
end.

message "example 1".
do transaction:
    def buffer buf for tt.
    find next tt where tt.f1 > 10 no-error.
    message avail(tt). // no
    create buf. buf.f1 = 100. release buf.
    find next tt no-error. // find after 10
    message avail(tt) tt.f1. // yes 100
end.

FIND NEXT stores the virtual record with f1 set on 10 and so the upcoming FIND NEXT will find the new record with f1 = 100.

• If the buffer is sent as parameter, it keeps it state in regard to FIND NEXT.
• If the table is sent as parameter (even input-output), the default buffer won't share the state of the buffer (expected, as the callee has its own buffers defined).
• If the table is by-reference, the buffer keeps its state.
• If the FIND NEXT operations are done in separate siblings do transaction blocks, the buffer still keeps its state.

I was trying to confirm that this is a buffer things and not a scope thing. I was having a wild thought that maybe the FIND NEXT was updating a scoped-state instead of a buffer-state. I think this can be denied by my findings.

Extra findings:

• doing a FIND NEXT on another index doesn't seem to reset the cursor
  

  def temp-table tt field f1 as int index idx f1 index idx2 f2.
  
  do transaction:
      create tt. tt.f1 = 1. tt.f2 = 1.
      create tt. tt.f1 = 5. tt.f2 = 5.
      create tt. tt.f1 = 10. tt.f2 = 10.
  end.
  
  message "example 4".
  do transaction:
     find next tt where tt.f1 = 4 no-error.
     message avail(tt). // no
     find next tt no-error. 
     message avail(tt) tt.f1. // yes 5
     find next tt where tt.f1 > -1 use-index idx2 no-error.
     message avail(tt) tt.f1. // yes 10
  end.

On the last message I would have expected to see yes 1 as the index was reset. I suppose that the first FIND is setting the sort index on the buffer. Changing the where/index doesn't reset that sort index.

FIND FIRST is not resetting the cursor:

def temp-table tt field f1 as int index idx f1 index idx2 f2.

do transaction:
    create tt. tt.f1 = 1. tt.f2 = 3.
    create tt. tt.f1 = 5. tt.f2 = 2.
    create tt. tt.f1 = 10. tt.f2 = 1.
end.

message "example 5".
do transaction:
   find next tt where tt.f1 = 4 no-error.
   message avail(tt). // no
   find next tt no-error. 
   message avail(tt) tt.f1. // yes 5
   find first tt where tt.f1 > -1 use-index idx2 no-error.
   message avail(tt) tt.f1. // yes 10
   find next tt no-error.
   message avail(tt). // no
end.

On last message, if the index was idx2, the FIND NEXT should have gone to f1 = 5, but it doesn't. It still iterates on the idx1.

ATM, I think the FIND NEXT statement freezes the index. Constantin, did you find otherwise?

• reintroduced off-end on RAQ to properly support invalidated AdaptiveQuery to avoid running infinitely.
• use java.util.Date in Record instead of presuming using the sql one.
• fixed unique-index-lookup checker from FQL preprocessor. This was relying on the property matcher that was changed in the meantime - it can return an empty list of matches instead on null.

I fixed this was some problems on a large customer application unit tests. There are still tests failing now and then, but the real problem is that it gets stuck at some point. Continuing investigation.