----------------------------------------------------------------------------------------------
[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" (the default setting), it does not occur in the FOR clause when SET("OPTIMIZE")="OFF".
The bug occurs in the WHERE clause when SET("OPTIMIZE")="ON" (the default setting) 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.
|