---------------------------------------------------------------------------------------------- [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) or some functions which support an optional alias or work area 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 in the FOR clause when the expression includes field names (in the current work area) or UDFs (user-defined functions) or some functions which support an optional alias or work area in the EVALUATE() function, 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)"! BROWSE FOR fld1=REPL(CHR(97+RECN()-1),3) && it returns correct data. BROWSE FOR fld1=EVALUATE('REPL(CHR(97+RECN()-1),3)') && it returns correct data. The bug occurs in the WHERE clause when the expression includes field names (in the current work area) or UDFs (user-defined functions) or some functions which support an optional alias or work area in the EVALUATE() function, 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! SELECT * FROM tmp1 WHERE fld1=REPL(CHR(97+RECN()-1),3) INTO CURSOR tmp2 nofilter && it returns incorrect data! SELECT * FROM tmp1 WHERE fld1=EVALUATE('REPL(CHR(97+RECN()-1),3)') 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) ; or some functions which support an optional alias or work area 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) ; or some functions which support an optional alias or work area 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) or 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 http://www.baiyujia.com/vfpdocuments/f_vfp9fix340.asp http://www.baiyujia.com/vfpdocuments/f_vfp9fix341.asp 6. OTHER: For reference only, there is no guarantees. Any questions or suggestions, please send me an email at ccb2000@163.com. |