LOOKUP in WHERE clause of dynamic query does not filter results with DataServer.

ABL LOOKUP function used in WHERE clause of dynamic query does not cause results to be filtered when executed against DataServer for Oracle or or DataServer for Microsoft SQL Server.

Expectation is that the LOOKUP should reduce the number of records returned but this does not occur.

Results are filtered as expected when running the same query as a FOR EACH or a static query.

SQL query generated is the same regardless of whether a FOR EACH, static or dynamic query is used.

Problem does not affect all queries featuring the LOOKUP function; some queries return the expected number of records.

Sample dynamic query to reproduce against a migrated copy of Sports2000; query should return 57 records but instead returns 72 when executed against DataServer for Oracle:
 

DEFINE VARIABLE i AS INTEGER     NO-UNDO.
DEFINE VARIABLE hq AS HANDLE      NO-UNDO.
DEFINE VARIABLE qstring AS CHARACTER   NO-UNDO.

qstring = "FOR EACH employee FIELDS (empnum firstname lastname deptcode) NO-LOCK 
            WHERE LOOKUP(employee.deptcode, '100,200,300,400') > 0, 
           FIRST department FIELDS (deptcode deptname) NO-LOCK 
            WHERE department.deptcode = employee.deptcode,
           EACH family FIELDS (relativename relation) NO-LOCK 
            WHERE family.empnum = employee.empnum
           /* QUERY-TUNING(NO-JOIN-BY-SQLDB) */
          ".

CREATE QUERY hq.

hq:SET-BUFFERS(BUFFER employee:HANDLE, BUFFER department:HANDLE, BUFFER family:HANDLE).
hq:QUERY-PREPARE (qstring).
hq:QUERY-OPEN().

REPEAT :
    hq:GET-NEXT().
    IF hq:QUERY-OFF-END THEN LEAVE.
    /* DISPLAY employee.empnum firstname lastname employee.deptcode department.deptname family.relativename. */
    i = i + 1.
END.

DISP i.

hq:QUERY-CLOSE().
DELETE OBJECT hq.

The exact cause is not known at this time. 

Upgrade to 11.6.0 or later.
 

Option #1
Consider re-writing the query altogether to eliminate the use of the LOOKUP function. It is generally considered bad practice to use functions on the left side of a WHERE clause. When a function is used on the left side of a query WHERE clause, all records have to be sent to the client in order for the function to be evaluated. This can impact upon the performance of the query.

In the scenario described above, removing the LOOKUP function avoids the issue and can be demonstrated with the following simple tweak:

WHERE employee.deptcode = '100' OR 
      employee.deptcode = '200' OR 
      employee.deptcode = '300' OR 
      employee.deptcode = '400'

Option #2
Use a static query in place of a dynamic query.

Option #3
Add the QUERY-TUNING phrase NO-JOIN-BY-SQLDB to the query to force client-side joins and avoid the problem.
Per the sample code above:
 

qstring = "FOR EACH employee FIELDS (empnum firstname lastname deptcode) NO-LOCK 
            WHERE LOOKUP(employee.deptcode, '100,200,300,400') > 0, 
           FIRST department FIELDS (deptcode deptname) NO-LOCK 
            WHERE department.deptcode = employee.deptcode,
           EACH family FIELDS (relativename relation) NO-LOCK 
            WHERE family.empnum = employee.empnum
           QUERY-TUNING(NO-JOIN-BY-SQLDB)
          ".