Bug #10026: A multi-table AdaptiveQuery running off-end is still updating the cursor - Database - Golden Code Redmine

Bug #10026

A multi-table AdaptiveQuery running off-end is still updating the cursor

Added by Alexandru Lungu about 1 year ago. Updated 12 months ago.

Status:

WIP

Priority:

Normal

Assignee:

Stefanel Pezamosca

Target version:

Start date:

Due date:

% Done:

billable:

vendor_id:

GCD

case_num:

version_reported:

version_resolved:

reviewer:

production:

env_name:

topics:

10026.patch (1.77 KB) Alexandru Lungu, 05/21/2025 04:34 AM

History

#1 Updated by Alexandru Lungu about 1 year ago

File 10026.patch added
Assignee set to Stefanel Pezamosca

From #10000:

First and foremost, there is Eduard's change in trunk rev. 15701 where buffers from components 2 onward are not released on off-end. This is a correct fix from #9538.

However, this exposes a limitation in AdaptiveQuery where the cursor.addResultFirst(getCurrentIds()); is called unconditionally. It should have checked whether the query actually run off-end and then call cursor.addResultFirst(null);. [...] we need a better synchronization between results and cursor: the cursor is empty when the results are empty and cursor has records when the results actually fetched something.

I am attaching my attempt.

This covers the example in #9538-69 - but scrolling variant. Debug it and check that after running off-end, the row is also added to the cursor instead of null to signal last entry.
With my attempt, FIND CURRENT becomes broken, because not updating the cursor will let is eventually empty. A FIND CURRENT on an example like #10000-34 will yield an error.

Goals:

Extend the FIND CURRENT test-case suite to cover: scrolling, non-scrolling queries + browsed queries + multi-table queries (with optimized components or not).
- run some nexts and run FIND CURRENT.
- run some nexts and then some prevs + run FIND CURRENT (the buffers should have been loaded by loadByValue now).
- etc.
Check if there are other places where the query should decide whether updating the cursor with ids or null is right. There may be other places apart from the ones in my patch.
Fix GET CURRENT to work even if that component didn't find anything. It is possible to have a record loaded using loadByValues, but the results and cursor still be empty (and the query off-end). Maybe calling the CURRENT from CompoundQuery should by-pass such checks. The CURRENT solely relies on buffer contents, so it is irrelevant if the query is off-end.
Extra: please check in OE what happens if buffers are loaded with something else before a CURRENT. Will the CURRENT honor whatever is already loaded or reload the queries with the last entries found?

Mind that some symptoms of this issue were seen in #10000 and fixed properly. However, this issue has the potential to generate logical mismatches which may be hard to understand with the debugger.

#2 Updated by Stefanel Pezamosca about 1 year ago

Status changed from New to WIP

#3 Updated by Stefanel Pezamosca about 1 year ago

Alexandru Lungu wrote:

Extra: please check in OE what happens if buffers are loaded with something else before a CURRENT. Will the CURRENT honor whatever is already loaded or reload the queries with the last entries found?

I have observed that for a scrolling query. GET-CURRENT will load previously found records by the query. For non-scrolling case the CURRENT will honor the already loaded records.

Fix GET CURRENT to work even if that component didn't find anything. It is possible to have a record loaded using loadByValues, but the results and cursor still be empty (and the query off-end). Maybe calling the CURRENT from CompoundQuery should by-pass such checks. The CURRENT solely relies on buffer contents, so it is irrelevant if the query is off-end.

Do you have an example/testcase that shows this? At the moment I don't understand how this case should look like such that GET CURRENT would not work.

#4 Updated by Alexandru Lungu about 1 year ago

Do you have an example/testcase that shows this? At the moment I don't understand how this case should look like such that GET CURRENT would not work.

I get provide some pseudo-code:

Run a compound query with no optimized components EACH/EACH. The results found should be (1, 2), (1, 3), (2, 3) (so a x < y condition should do the trick).
Go through all rows with next till off-end (including off-end).
The first AQ is at record 3; the second AQ has no records
Run get first. This will call loadByValues and run load on both queries with (1) and (2).
- Thus, the first AQ is re-positioned to row 1. Second AQ is not re-positioned .... its buffer is simply loaded with 2.
Run get current. First AQ will reload row 1. Second AQ will reload the buffer; however, a call to getRow from CompoundQuery will end up in AQ.getRow that checks whether it has any results. It doesn't ... so this fails.

I fixed this in #10000 by not calling getRow for scrolling queries (that are prone to loadByValues), but rather take input from buffers directly to bypass the checking whether the query has results. It doesn't ... but it was loaded with loadByValues previously.

#6 Updated by Stefanel Pezamosca about 1 year ago

I made a testcase: See testcase See testcase

DEFINE VARIABLE resMsg AS CHARACTER.
DEFINE TEMP-TABLE tt1
    FIELD f1 AS INTEGER
    INDEX p_idx AS PRIMARY f1.

DEFINE TEMP-TABLE tt2
    FIELD f1 AS INTEGER
    INDEX p_idx AS PRIMARY f1.

DEFINE QUERY qh FOR tt1, tt2.
DEFINE QUERY qh_scroll FOR tt1, tt2 SCROLLING.

PROCEDURE initTest:
    CREATE tt1.
    ASSIGN tt1.f1 = 1.
    CREATE tt1.
    ASSIGN tt1.f1 = 2.
    CREATE tt1.
    ASSIGN tt1.f1 = 3.
    CREATE tt2.
    ASSIGN tt2.f1 = 1.
    CREATE tt2.
    ASSIGN tt2.f1 = 2.
    CREATE tt2.
    ASSIGN tt2.f1 = 3.
    OPEN QUERY qh FOR EACH tt1, EACH tt2 WHERE tt1.f1 < tt2.f1.
    OPEN QUERY qh_scroll FOR EACH tt1, EACH tt2 WHERE tt1.f1 < tt2.f1.
END PROCEDURE.

PROCEDURE logResult:
    DEFINE OUTPUT PARAMETER logMsg AS CHARACTER.
    logMsg = "(" + (IF AVAILABLE tt1 THEN STRING(tt1.f1) ELSE "?") + "," + (IF AVAILABLE tt2 THEN STRING(tt2.f1) ELSE "?") + ")".
    MESSAGE logMsg.
END PROCEDURE.

PROCEDURE testQuery:
    DEFINE INPUT PARAMETER qHandle AS HANDLE.
    DEFINE INPUT PARAMETER isScroll AS LOGICAL.

    DO WHILE NOT qHandle:QUERY-OFF-END:
        qHandle:GET-NEXT().
        RUN logResult(OUTPUT resMsg).
    END.
    qHandle:GET-FIRST().
    RUN logResult(OUTPUT resMsg).
    qHandle:GET-CURRENT().
    RUN logResult(OUTPUT resMsg).
    FIND LAST tt1.
    FIND LAST tt2.
    RUN logResult(OUTPUT resMsg).
    qHandle:GET-CURRENT().
    RUN logResult(OUTPUT resMsg).

    qHandle:QUERY-CLOSE().
END PROCEDURE.

OUTPUT TO queryResults.txt.
RUN initTest.
RUN testQuery(QUERY qh:HANDLE, NO).
MESSAGE "======Scrolling======".
RUN testQuery(QUERY qh_scroll:HANDLE, YES).

The output in OE is:

(1,2)
(1,3)
(2,3)
(?,?)
(1,2)
(1,2)
(3,3)
(3,3)
======Scrolling======
(1,2)
(1,3)
(2,3)
(?,?)
(1,2)
(1,2)
(3,3)
Warning: A buffer in query qh_scroll contains a different record than the current query result list entry. (4108)
Warning: A buffer in query qh_scroll contains a different record than the current query result list entry. (4108)
(1,2)

The output in FWD is this:

(1,2)
(1,3)
(2,3)
(?,?)
(1,2)
(1,2)
(3,3)
(3,3)
======Scrolling======
(1,2)
(1,3)
(2,3)
(?,?)
(1,2)
No query record is available. (4114)
(1,2)
(3,3)
(3,3)

There are clearly some differences that should be fixed.

#7 Updated by Stefanel Pezamosca 12 months ago

I made some more experiments related with Scrolling/Non-Scrolling Queries.
For this testcase: Scrolling & Non-Scrolling Query testcase

define temp-table ttForQuery 
    field indexPosition as integer.

create ttForQuery.
assign ttForQuery.indexPosition = 1.
release ttForQuery.

output to testCurrent.txt.

define query testQueryScroll for ttForQuery scrolling.
message "----- Scrolling: -----".
query testQueryScroll:query-prepare("for each ttForQuery").
query testQueryScroll:query-open().

query testQueryScroll:get-current().
message ttForQuery.indexPosition.

query testQueryScroll:get-first().
message ttForQuery.indexPosition.

find first ttForQuery.
assign ttForQuery.indexPosition = 2.

query testQueryScroll:get-current().
message ttForQuery.indexPosition.

find first ttForQuery.
assign ttForQuery.indexPosition = 1.
release ttForQuery.

query testQueryScroll:get-current().
message ttForQuery.indexPosition.

define query testQuery for ttForQuery.
message "----- Non Scrolling: -----".
query testQuery:query-prepare("for each ttForQuery").
query testQuery:query-open().

query testQuery:get-current().
message ttForQuery.indexPosition.

query testQuery:get-first().
message ttForQuery.indexPosition.

find first ttForQuery.
assign ttForQuery.indexPosition = 2.

query testQuery:get-current().
message ttForQuery.indexPosition.

find first ttForQuery.
assign ttForQuery.indexPosition = 1.
release ttForQuery.

query testQuery:get-current().
message ttForQuery.indexPosition.
output close.

I get the following result: See result ..

In OE:

----- Non Scrolling: -----
No query record is available. (4114)
** No ttForQuery record is available. (91)

1
2
No query record is available. (4114)
** No ttForQuery record is available. (91)

----- Scrolling: -----
No query record is available. (4114)
** No ttForQuery record is available. (91)

1
2
Warning: A buffer in query testQueryScroll contains a different record than the current query result list entry. (4108)
1

In FWD:

----- Non Scrolling: -----
** No ttForQuery record is available. (91)
?
1
2
** No ttForQuery record is available. (91)
?
----- Scrolling: -----
** No ttForQuery record is available. (91)
?
1
2
** No ttForQuery record is available. (91)
?

I also found some relevant tests in xfer/testcases/abl/tests/query/adaptive/ that I could look into, as currently there are some failures. I'm currently investigating and experimenting with some changes.

Also available in: Atom PDF

Project

General

Profile

FWD » Core Development » Database

Issues

Custom queries