-------------------------------------------------------------------------------------------- [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", 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" 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. |