Information

Title	Querying VST where Id field is zero results in all entries in the VST being returned

URL Name	Querying-VST-where-Id-field-is-zero-results-in-all-entries-in-the-VST-being-returned-000060268

Article Number	000173359

Environment	Product: Progress Version: 9.1D, 9.1E Product: OpenEdge Version: All supported versions OS: All supported platforms Other: VST, Virtual System Table

Question/Problem Description

Querying VST where the Id field is zero (0) results in all entries in the VST being returned.

Querying a Virtual System Table where Id field is zero results in all entries in the VST being returned rather than no records.

Bracketing is not working as expected when querying VSTs where Id field less than or equal to zero (0).

COMPILE with XREF shows that the Query is using the VST index assuming use of the _<VST>-Id field, e.g. _Connect-Id, _Lock-Id, etc., in the WHERE clause).

Behavior is reproducible in earlier versions of Progress / OpenEdge.
Problem only occurs when querying Virtual System Tables; it cannot be reproduced against normal database tables or temp-tables.

Steps to Reproduce

Clarifying Information

Sample code to demonstrate behaviour:

FOR EACH _Connect NO-LOCK WHERE _Connect-Id LE 0:
    DISPLAY _Connect-Id.
END.

FOR EACH _Lock NO-LOCK WHERE _Lock-Id = 0:
    DISPLAY _Lock-Id.
END.

FOR EACH _Trans NO-LOCK WHERE _Trans-Id < 0:
    DISPLAY _Trans-Id.
END.

Examples of Queries returning incorrect results:

FOR EACH _connect WHERE _connect-id >= -5 AND _connect-id < 1:
FOR EACH _connect WHERE _connect-id >= -5 AND _connect-id < 0:
FOR EACH _connect WHERE _connect-id >= -5 AND _connect-id <= 0:
FOR EACH _connect WHERE _connect-id < 0:
FOR EACH _connect WHERE _connect-id <= 0:

When the bracket contains a negative number:

FOR EACH _connect WHERE _connect-id >= -5 AND _connect-id <= 10
causes error:
Client attempting to access recid 0 which is not part of file -16385. (438)

Examples of Queries returning correct results:

FOR EACH _connect WHERE _connect-id < 65535:
FOR EACH _connect WHERE _connect-id < 16776960:

Error Message

Defect Number	Defect PSC00331850

Enhancement Number

Cause

The scanning when a bracket includes "0" either explicitly, implicitly or inclusive/exclusive causes various problems for VST FIND operations in both ASCENDING and DESCENDING queries.

Resolution

Upgrade to OpenEdge 11.6.0 where the following were fixed:

Cases where the high bracket includes 0 as an actual value
Signed/Unsigned comparison error 438 which occurs when the bracket contains a negative number

Workaround

Avoid bracketing with zero when querying VSTs. As a simple example:

DEFINE VARIABLE myVal AS INTEGER INITIAL 0 NO-UNDO.
    
IF myVal > 0 THEN DO:
    FOR EACH _Trans WHERE _Trans-Id <= myVal:
        DISPLAY _Trans-id.
    END.
END.

Notes

Keyword Phrase

Last Modified Date	3/23/2017 12:33 PM

Querying VST where Id field is zero results in all entries in the VST being returned

Information