----------------------------------------------------------------------------------------------
                      [BUG/PRB.] VFP 9.0 FIX - FIX THE EVALUATE() FUNCTION FOR RUSHMORE QUERY OPTIMIZATION
                                  July 2024
                 ----------------------------------------------------------------------------------------------
                                     CCB



1. BUG:

     In vfp9 (and vfp6, vfp7, vfp8), if the expression includes field names (in the current work area) or UDFs (user-defined functions) in the EVALUATE() function,
     it is an optimizable expression, sometimes it returns incorrect data.

     The bug occurs in the FOR clause when SET("OPTIMIZE")="ON", it does not occur in the FOR clause when SET("OPTIMIZE")="OFF".
     The bug occurs in the WHERE clause when SET("OPTIMIZE")="ON" or SET("OPTIMIZE")="OFF".

     The bug occurs in the following commands (in the FOR clause):
     AVERAGE command
     BLANK command
     BROWSE command
     CALCULATE command
     CHANGE command
     COPY TO command
     COPY TO ARRAY command
     COUNT command
     DELETE command
     DISPLAY command
     EDIT command
     EXPORT command
     INDEX command
     LABEL command
     LIST command
     LOCATE command
     RECALL command
     REPLACE command
     REPLACE FROM ARRAY command
     REPORT command
     SCAN command
     SET FILTER command
     SORT command
     SUM command
     TOTAL command

     The bug also occurs in the following commands (in the WHERE clause):
     DELETE - SQL command
     SELECT - SQL command
     UPDATE - SQL command

     SELECT - SQL Command - WHERE Clause (from dv_foxhelp9.chm).
     Including the EVALUATE() function (or the EXECSCRIPT() function) in the WHERE clause of a SQL query can return incorrect data.
     Now the bug has been fixed in VFP Advanced.

     The bug occurs when the expression includes field names (in the current work area) or UDFs (user-defined functions) in the EVALUATE() function
     in the FOR clause, for example,
     INDEX ON fld1 TAG fld1
     BROWSE FOR fld1=fld2 && it returns correct data.
     BROWSE FOR fld1=EVALUATE("fld2") && it returns incorrect data!
     BROWSE FOR fld1=test_subproc("aaa") && it returns correct data.
     BROWSE FOR fld1=EVALUATE('test_subproc("aaa")') && it causes the error "Invalid subscript reference (Error 31)"!

     The bug occurs when the expression includes field names (in the current work area) or UDFs (user-defined functions) in the EVALUATE() function
     in the WHERE clause, for example,
     INDEX ON fld1 TAG fld1
     SELECT * FROM tmp1 WHERE fld1=fld2 INTO CURSOR tmp2 nofilter && it returns correct data.
     SELECT * FROM tmp1 WHERE fld1=EVALUATE("fld2") INTO CURSOR tmp2 nofilter && it returns incorrect data!
     SELECT * FROM tmp1 WHERE fld1=test_subproc("aaa") INTO CURSOR tmp2 nofilter && it returns incorrect data!
     SELECT * FROM tmp1 WHERE fld1=EVALUATE('test_subproc("aaa")') INTO CURSOR tmp2 nofilter && it returns incorrect data!


2. CAUSE:

     There are some BUGs in the following code.


3. RESOLUTION:

     We can write some code to fix the BUG.

     Fun4d2753 :: ; proc near
             sub esp , 01Ch                                                  ;0x004d2753 :        83ec1c
             push ebp                                                        ;0x004d2756 :        55
             mov  ebp , dword ptr [ Data9393ac ]                             ;0x004d2757 :        8b2dac939300
             push esi                                                        ;0x004d275d :        56
             push edi                                                        ;0x004d275e :        57
             mov esi , eax                                                   ;0x004d275f :        8bf0
             mov edi , edi                                                   ;0x004d2761 :        8bff
             jmp Label4d276e                                                 ;0x004d2763 :        eb09

     Label4d2765 ::
             mov eax , esi                                                   ;0x004d2765 :        8bc6
             call Fun4360e0                                                  ;0x004d2767 :        e87439f6ff
             mov esi , eax                                                   ;0x004d276c :        8bf0

     Label4d276e ::
             mov  al , byte ptr [esi]                                        ;0x004d276e :        8a06


     ;
     ;                 -----------------------------------------------------------------------------------
     ;                      VFP 9.0 FIX - FIX THE EVALUATE() FUNCTION FOR RUSHMORE QUERY OPTIMIZATION
     ;                                  July 2024
     ;                 -----------------------------------------------------------------------------------
     ;                                     CCB
     ;
     ; In vfp9 (and vfp6, vfp7, vfp8), if the expression includes field names (in the current work area) or UDFs (user-defined functions) in the EVALUATE() function,
     ; it is an optimizable expression, sometimes it returns incorrect data.
     ; In Visual FoxPro Advanced, if the expression includes field names (in the current work area) or UDFs (user-defined functions) in the EVALUATE() function,
     ; it is not an optimizable expression, it returns correct data.
     ;
     ; 2024/7/9, by ccb
     ;

     Label4d276e_start ::
             cmp dword ptr vfpa_sys9159_data,00h
             je Label4d276e_end
             cmp al , 0CEh
             jne Label4d276e_end
             jmp Label4d3a92
     Label4d276e_end ::


     Label4d2770 ::
             movzx ecx , al                                                  ;0x004d2770 :        0fb6c8
             add ecx , 0FFFFFFE5h                                            ;0x004d2773 :        83c1e5
             cmp ecx , 0E2h                                                  ;0x004d2776 :        81f9e2000000
             ja Label4d2765                                                  ;0x004d277c :        77e7
             movzx ecx ,byte ptr [ ecx + offset Data4d2670 ]                          ;0x004d277e :        0fb68970264d00
             jmp  dword ptr [ 4 * ecx + offset DataPtr4d279c ]                           ;0x004d2785 :        ff248d9c274d00


4. APPLIES TO:

     VFP 6.0.8167.0
     VFP 6.0.8961.0 (SP5)

     VFP 7.0.0.9262
     VFP 7.0.0.9465 (SP1)

     VFP 8.0.0.2521
     VFP 8.0.0.3117 (SP1)

     VFP 9.0.0.2412
     VFP 9.0.0.3504 (SP1)
     VFP 9.0.0.4611 (SP2)
     VFP 9.0.0.5015 (SP2)
     VFP 9.0.0.5411 (SP2)
     VFP 9.0.0.5721 (SP2)
     VFP 9.0.0.5815 (SP2)
     VFP 9.0.0.6303 (SP2)
     VFP 9.0.0.6602 (SP2)
     VFP 9.0.0.7423 (SP2)

     The bug has been fixed in VFP Advanced.

     IMPORTANT NOTE:
     In VFP Advanced and vfp9 (and vfp6, vfp7, vfp8), if the expression includes UDFs (user-defined functions) in the WHERE clause,
     or the expression includes some functions which support an optional alias or work area in the WHERE clause:
     ALIAS() function
     BOF() function
     DELETED() function
     EOF() function
     FCOUNT() function
     FIELD() function
     FLOCK() function
     FOUND() function
     LUPDATE() function
     NDX() function
     RECCOUNT() function
     RECNO() function
     RECSIZE() function
     RLOCK() function
     ORDER() function
     FILTER() function
     HEADER() function
     USED() function
     ISBLANK() function
     GETFLDSTATE() function
     it is an optimizable expression, sometimes it returns incorrect data.

     For example,
     SELECT * FROM tmp1 WHERE fld1=test_subproc("aaa") INTO CURSOR tmp2 nofilter && it returns incorrect data!
     SELECT * FROM tmp1 WHERE fld1=REPL(CHR(97+RECN()-1),3) INTO CURSOR tmp2 nofilter && it returns incorrect data!

     In VFP Advanced, we can use the EVALUATE() function to fix the bug:
     SELECT * FROM tmp1 WHERE fld1=EVALUATE('test_subproc("aaa")') INTO CURSOR tmp2 nofilter && it returns correct data.
     SELECT * FROM tmp1 WHERE fld1=EVALUATE('REPL(CHR(97+RECN()-1),3)') INTO CURSOR tmp2 nofilter && it returns correct data.


5. REFERENCE WEBSITES:

     1, baiyujia.com:
     http://www.baiyujia.com
     http://www.baiyujia.com/vfpdocuments/f_vfp9fix328.asp
     http://www.baiyujia.com/vfpdocuments/f_vfp9fix326.asp
     http://www.baiyujia.com/vfpdocuments/f_vfp9fix327.asp
     http://www.baiyujia.com/vfpdocuments/f_vfp9fix330.asp
     http://www.baiyujia.com/vfpdocuments/f_vfp9fix331.asp


6. OTHER:

     For reference only, there is no guarantees.

     Any questions or suggestions, please send me an email at ccb2000@163.com.