--------------------------------------------------------------------------------------------
[BUG/PRB.] VFP 9.0 FIX - FIX THE INLIST() FUNCTION FOR RUSHMORE QUERY OPTIMIZATION
June 2024
--------------------------------------------------------------------------------------------
CCB
1. BUG:
In vfp9 (and vfp6, vfp7, vfp8), when using UDFs (user-defined functions) in the INLIST() function for Rushmore Query Optimization,
sometimes it will cause the memory access violation exception (exception code 0xC0000005).
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 does not occur 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 does not occur in the following commands (in the WHERE clause):
DELETE - SQL command
SELECT - SQL command
UPDATE - SQL command
The bug also occurs when using some expressions which include field names (in the current work area) in the INLIST() function.
The bug also occurs when using some functions which support an optional alias or work area in the INLIST() function:
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
Using Rushmore Query Optimization to Speed Data Access (from dv_foxhelp9.chm).
Creating Basic Optimizable Expressions
A basic optimizable expression takes one of the two following forms:
eIndex relOp eExp
-or-
eExpr relOp eIndex
A basic optimizable expression has the following characteristics:
eIndex exactly matches the expression on which an index is constructed.
eExpr is any expression and can include variables and fields from other unrelated tables.
relOp is one of the following relational operators: <, >, =, <=, >=, <>, #, ==, or !=.
You can also use the ISNULL( ), BETWEEN( ), or INLIST( ) functions (or their SQL equivalents such as IS NULL, and so on).
You can use BETWEEN( ) or INLIST( ) in the following two forms:
BETWEEN(eIndex, eExpr, eExpr)
-or-
INLIST(eIndex, eExpr [, eExpr, eExpr, ...])
The bug occurs when using UDFs (user-defined functions) in the INLIST() function, for example,
INDEX ON fld1 TAG fld1
LOCATE FOR INLIST(fld1,"aaa",subproc("bbb")) && no error.
LOCATE FOR INLIST(fld1,"aaa",subproc("bbb"),"ccc") && no error.
LOCATE FOR INLIST(fld1,"aaa",subproc("bbb"),"ccc","ddd") && causes the memory access violation exception (exception code 0xC0000005)!
LOCATE FOR INLIST(fld1,"aaa",subproc("bbb"),subproc("ccc"),"ddd") && causes the memory access violation exception (exception code 0xC0000005)!
LOCATE FOR INLIST(fld1,"aaa",subproc("bbb"),subproc("ccc"),subproc("ddd")) && causes the memory access violation exception (exception code 0xC0000005)!
The bug also occurs when using some expressions which include field names (in the current work area) in the INLIST() function, for example,
INDEX ON fld1 TAG fld1
LOCATE FOR INLIST(fld1,"aaa",fld2) && no error.
LOCATE FOR INLIST(fld1,"aaa",fld2,"ccc") && no error.
LOCATE FOR INLIST(fld1,"aaa",fld2,"ccc","ddd") && causes the memory access violation exception (exception code 0xC0000005)!
LOCATE FOR INLIST(fld1,"aaa",fld2,fld2+fld3,"ddd") && causes the memory access violation exception (exception code 0xC0000005)!
LOCATE FOR INLIST(fld1,"aaa",fld2,fld2+fld3,fld2+fld4) && causes the memory access violation exception (exception code 0xC0000005)!
The bug also occurs when using some functions which support an optional alias or work area in the INLIST() function, for example,
INDEX ON fld1 TAG fld1
LOCATE FOR INLIST(fld1,"aaa",FIELD(2)) && no error.
LOCATE FOR INLIST(fld1,"aaa",FIELD(2),"ccc") && no error.
LOCATE FOR INLIST(fld1,"aaa",FIELD(2),"ccc","ddd") && causes the memory access violation exception (exception code 0xC0000005)!
LOCATE FOR INLIST(fld1,"aaa",FIELD(2),FIELD(3),"ddd") && causes the memory access violation exception (exception code 0xC0000005)!
LOCATE FOR INLIST(fld1,"aaa",FIELD(2),FIELD(3),FIELD(4)) && causes the memory access violation exception (exception code 0xC0000005)!
2. CAUSE:
There are some BUGs in the following code.
3. RESOLUTION:
We can write some code to fix the BUG.
Label59b7df ::
mov eax , ebx ;0x0059b7df : 8bc3
call Fun49b895 ;0x0059b7e1 : e8af00f0ff
test eax , eax ;0x0059b7e6 : 85c0
mov dword ptr [ ebp + 24 ] , eax ;0x0059b7e8 : 894518
je Label4767ef ;0x0059b7eb : 0f84feafedff
mov esi , dword ptr [ esi + 8 ] ;0x0059b7f1 : 8b7608
mov dword ptr [ esp + 24 ] , 02h ;0x0059b7f4 : c744241802000000
nop ;0x0059b7fc : 90
Label59b7fd ::
cmp dword ptr [esi] , 00h ;0x0059b7fd : 833e00
jne Label59b844 ;0x0059b800 : 7542
mov ecx , dword ptr [ esi + 4 ] ;0x0059b802 : 8b4e04
mov edx , dword ptr [ ebp + 24 ] ;0x0059b805 : 8b5518
mov eax , dword ptr [ Data9370f8 ] ;0x0059b808 : a1f8709300
mov eax , dword ptr [eax] ;0x0059b80d : 8b00
pushd 00h ;0x0059b80f : 6a00
push ecx ;0x0059b811 : 51
mov ecx , dword ptr [ eax + 0E4h ] ;0x0059b812 : 8b88e4000000
push edx ;0x0059b818 : 52
push edi ;0x0059b819 : 57
push ebx ;0x0059b81a : 53
call Fun77ec57 ;0x0059b81b : e837341e00
mov ecx , dword ptr [ esp + 28 ] ;0x0059b820 : 8b4c241c
test ecx , ecx ;0x0059b824 : 85c9
je Label59b82f ;0x0059b826 : 7407
cmp eax , 01h ;0x0059b828 : 83f801
jne Label59b82f ;0x0059b82b : 7502
xor eax , eax ;0x0059b82d : 33c0
Label59b82f ::
cmp dword ptr [ esp + 24 ] , eax ;0x0059b82f : 39442418
jl Label59b839 ;0x0059b833 : 7c04
mov dword ptr [ esp + 24 ] , eax ;0x0059b835 : 89442418
Label59b839 ::
mov eax , dword ptr [ esp + 24 ] ;0x0059b839 : 8b442418
test eax , eax ;0x0059b83d : 85c0
mov esi , dword ptr [ esi + 8 ] ;0x0059b83f : 8b7608
jne Label59b7fd ;0x0059b842 : 75b9
;
; ---------------------------------------------------------------------------------
; VFP 9.0 FIX - FIX THE INLIST() FUNCTION FOR RUSHMORE QUERY OPTIMIZATION
; June 2024
; ---------------------------------------------------------------------------------
; CCB
;
; Fix the INLIST() function for Rushmore Query Optimization.
;
; 2024/6/10, by ccb
;
cmp dword ptr vfpa_sys9158_data,0FFFFFFFFh
je Label59b844
cmp dword ptr vfpa_sys9158_data,00h
je Label59b844
test esi , esi
je Label59b844
cmp dword ptr [esi] , 00h
jne Label59b844
jmp Label59b7fd
Label59b844 ::
mov ecx , dword ptr [ ebp + 24 ] ;0x0059b844 : 8b4d18
mov eax , dword ptr [ Data9370f8 ] ;0x0059b847 : a1f8709300
mov eax , dword ptr [eax] ;0x0059b84c : 8b00
pushd 00h ;0x0059b84e : 6a00
push esi ;0x0059b850 : 56
push ecx ;0x0059b851 : 51
mov ecx , dword ptr [ eax + 0E4h ] ;0x0059b852 : 8b88e4000000
push edi ;0x0059b858 : 57
push ebx ;0x0059b859 : 53
call Fun77ec57 ;0x0059b85a : e8f8331e00
mov ecx , dword ptr [ esp + 28 ] ;0x0059b85f : 8b4c241c
test ecx , ecx ;0x0059b863 : 85c9
je Label59b86e ;0x0059b865 : 7407
cmp eax , 01h ;0x0059b867 : 83f801
jne Label59b86e ;0x0059b86a : 7502
xor eax , eax ;0x0059b86c : 33c0
Label59b86e ::
cmp dword ptr [ esp + 24 ] , eax ;0x0059b86e : 39442418
jl Label59b878 ;0x0059b872 : 7c04
mov dword ptr [ esp + 24 ] , eax ;0x0059b874 : 89442418
Label59b878 ::
mov eax , dword ptr [ esp + 24 ] ;0x0059b878 : 8b442418
test eax , eax ;0x0059b87c : 85c0
jne Label59b896 ;0x0059b87e : 7516
mov eax , dword ptr [ ebp + 24 ] ;0x0059b880 : 8b4518
call Fun530453 ;0x0059b883 : e8cb4bf9ff
mov dword ptr [ ebp + 24 ] , 00h ;0x0059b888 : c7451800000000
pop edi ;0x0059b88f : 5f
pop esi ;0x0059b890 : 5e
pop ebp ;0x0059b891 : 5d
pop ebx ;0x0059b892 : 5b
ret 0Ch ;0x0059b893 : c20c00
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.
5. REFERENCE WEBSITES:
1, baiyujia.com:
http://www.baiyujia.com
http://www.baiyujia.com/vfpdocuments/f_vfp9fix326.asp
http://www.baiyujia.com/vfpdocuments/f_vfp9fix328.asp
http://www.baiyujia.com/vfpdocuments/f_vfp9fix329.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.
|