----------------------------------------------------------------------------------
[ENHANCED] VFP 9.0 FIX - RUSHMORE QUERY OPTIMIZATION IN THE WHERE CLAUSE
September 2024
----------------------------------------------------------------------------------
CCB
1. BUG:
In vfp9 (and vfp6, vfp7, vfp8), we can use SET OPTIMIZE ON|OFF to globally enable or disable Rushmore Query Optimization in the FOR clause,
it always enables Rushmore Query Optimization in the WHERE clause.
In VFP Advanced, we can use SET OPTIMIZE ON|OFF to globally enable or disable Rushmore Query Optimization in the FOR clause,
we can also use SYS(9166,1|0) to globally enable or disable Rushmore Query Optimization in the WHERE clause.
2. CAUSE:
There are some BUGs in the following code.
3. RESOLUTION:
We can write some code to fix the BUG.
CallBack5379e0 :: ; proc near
mov ecx , edi ;0x005379e0 : 8bcf
Label5379e2 ::
mov edx , dword ptr [ ebx + 0C4h ] ;0x005379e2 : 8b93c4000000
lea ecx , dword ptr [ ecx + 8 * ecx ] ;0x005379e8 : 8d0cc9
lea esi , dword ptr [ edx + 4 * ecx ] ;0x005379eb : 8d348a
mov edx , dword ptr [ edx + eax ] ;0x005379ee : 8b1402
cmp byte ptr [edx] , 0D1h ;0x005379f1 : 803ad1
mov ecx , dword ptr [ esi + 8 ] ;0x005379f4 : 8b4e08
mov esi , dword ptr [ esi + 12 ] ;0x005379f7 : 8b760c
je Label5cd133 ;0x005379fa : 0f8433570900
Label537a00 ::
test ecx , ecx ;0x00537a00 : 85c9
jl Label5cd13d ;0x00537a02 : 0f8c35570900
Label537a08 ::
mov edx , dword ptr [ esp + 24 ] ;0x00537a08 : 8b542418
push ecx ;0x00537a0c : 51
push ebp ;0x00537a0d : 55
mov ecx , ebx ;0x00537a0e : 8bcb
call Fun53234e ;0x00537a10 : e839a9ffff
mov ecx , dword ptr [ ebp + 0 ] ;0x00537a15 : 8b4d00
mov eax , dword ptr [ ecx - 4 ] ;0x00537a18 : 8b41fc
lea edx , dword ptr [ eax - 8 ] ;0x00537a1b : 8d50f8
call Fun5320f8 ;0x00537a1e : e8d5a6ffff
;
; -----------------------------------------------------------------------
; VFP 9.0 FIX - RUSHMORE QUERY OPTIMIZATION IN THE WHERE CLAUSE
; September 2024
; -----------------------------------------------------------------------
; CCB
;
; In 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,
; it is an optimizable expression, sometimes it returns incorrect data!
; In Visual FoxPro Advanced, if the expression includes UDFs (user-defined functions)
; or some functions which support an optional alias or work area in the WHERE clause,
; we can set SYS(9166,0) to globally disable Rushmore Query Optimization in the WHERE clause (as a temporary solution),
; it is not an optimizable expression, it returns correct data.
;
; 2024/9/2, by ccb
;
Label537a20_start ::
cmp dword ptr vfpa_sys9166_data,00h
jne Label537a20_end
inc eax
jmp Label537a23
Label537a20_end ::
Label537a23 ::
mov edi , eax ;0x00537a23 : 8bf8
dec edi ;0x00537a25 : 4f
test esi , esi ;0x00537a26 : 85f6
jl Label4d379c ;0x00537a28 : 0f8c6ebdf9ff
mov edx , dword ptr [ esp + 20 ] ;0x00537a2e : 8b542414
push esi ;0x00537a32 : 56
mov esi , dword ptr [ esp + 20 ] ;0x00537a33 : 8b742414
push esi ;0x00537a37 : 56
mov ecx , ebx ;0x00537a38 : 8bcb
call Fun53234e ;0x00537a3a : e80fa9ffff
mov ecx , dword ptr [esi] ;0x00537a3f : 8b0e
mov eax , dword ptr [ ecx - 4 ] ;0x00537a41 : 8b41fc
lea edx , dword ptr [ eax - 8 ] ;0x00537a44 : 8d50f8
call Fun5320f8 ;0x00537a47 : e8aca6ffff
;
; -----------------------------------------------------------------------
; VFP 9.0 FIX - RUSHMORE QUERY OPTIMIZATION IN THE WHERE CLAUSE
; September 2024
; -----------------------------------------------------------------------
; CCB
;
; In 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,
; it is an optimizable expression, sometimes it returns incorrect data!
; In Visual FoxPro Advanced, if the expression includes UDFs (user-defined functions)
; or some functions which support an optional alias or work area in the WHERE clause,
; we can set SYS(9166,0) to globally disable Rushmore Query Optimization in the WHERE clause (as a temporary solution),
; it is not an optimizable expression, it returns correct data.
;
; 2024/9/2, by ccb
;
Label537a49_start ::
cmp dword ptr vfpa_sys9166_data,00h
jne Label537a49_end
inc eax
jmp Label537a4c
Label537a49_end ::
Label537a4c ::
dec eax ;0x00537a4c : 48
Label537a4d ::
test edi , edi ;0x00537a4d : 85ff
je Label4d377b ;0x00537a4f : 0f8426bdf9ff
test eax , eax ;0x00537a55 : 85c0
jne Label537eb2 ;0x00537a57 : 0f8555040000
Label537a5d ::
mov esi , dword ptr [ esp + 36 ] ;0x00537a5d : 8b742424
mov edx , dword ptr [ ebx + 0CCh ] ;0x00537a61 : 8b93cc000000
imul esi , esi , 02Ch ;0x00537a67 : 6bf62c
mov dword ptr [ esi + edx ] , 03h ;0x00537a6a : c7041603000000
Label537a71 ::
mov eax , dword ptr [ esp + 16 ] ;0x00537a71 : 8b442410
mov ecx , dword ptr [ ebp + 0 ] ;0x00537a75 : 8b4d00
mov eax , dword ptr [eax] ;0x00537a78 : 8b00
mov edx , dword ptr [ ecx - 4 ] ;0x00537a7a : 8b51fc
mov edi , dword ptr [ eax - 4 ] ;0x00537a7d : 8b78fc
sub edx , 08h ;0x00537a80 : 83ea08
sub edi , 08h ;0x00537a83 : 83ef08
cmp edx , edi ;0x00537a86 : 3bd7
jb Label537a8c ;0x00537a88 : 7202
mov edx , edi ;0x00537a8a : 8bd7
Label537a8c ::
add edx , eax ;0x00537a8c : 03d0
cmp eax , edx ;0x00537a8e : 3bc2
jnb Label537aa0 ;0x00537a90 : 730e
Label537a92 ::
mov edi , dword ptr [eax] ;0x00537a92 : 8b38
or dword ptr [ecx] , edi ;0x00537a94 : 0939
add eax , 04h ;0x00537a96 : 83c004
add ecx , 04h ;0x00537a99 : 83c104
cmp eax , edx ;0x00537a9c : 3bc2
jb Label537a92 ;0x00537a9e : 72f2
Label537aa0 ::
mov ecx , dword ptr [ ebx + 0CCh ] ;0x00537aa0 : 8b8bcc000000
mov dword ptr [ esi + ecx + 36 ] , ebp ;0x00537aa6 : 896c0e24
mov ecx , dword ptr [ esp + 16 ] ;0x00537aaa : 8b4c2410
mov eax , ebx ;0x00537aae : 8bc3
call Fun541571 ;0x00537ab0 : e8bc9a0000
mov edx , dword ptr [ esp + 20 ] ;0x00537ab5 : 8b542414
mov ebp , dword ptr [ esp + 24 ] ;0x00537ab9 : 8b6c2418
mov eax , dword ptr [edx] ;0x00537abd : 8b02
mov edi , dword ptr [ ebp + 0 ] ;0x00537abf : 8b7d00
mov ecx , dword ptr [ edi - 4 ] ;0x00537ac2 : 8b4ffc
mov edx , dword ptr [ eax - 4 ] ;0x00537ac5 : 8b50fc
sub ecx , 08h ;0x00537ac8 : 83e908
sub edx , 08h ;0x00537acb : 83ea08
cmp ecx , edx ;0x00537ace : 3bca
jb Label537ad4 ;0x00537ad0 : 7202
mov ecx , edx ;0x00537ad2 : 8bca
Label537ad4 ::
add ecx , eax ;0x00537ad4 : 03c8
cmp eax , ecx ;0x00537ad6 : 3bc1
jnb Label537aeb ;0x00537ad8 : 7311
lea esp , dword ptr [ esp ] ;0x00537ada : 8d2424
Label537add ::
mov edx , dword ptr [eax] ;0x00537add : 8b10
or dword ptr [edi] , edx ;0x00537adf : 0917
add eax , 04h ;0x00537ae1 : 83c004
add edi , 04h ;0x00537ae4 : 83c704
cmp eax , ecx ;0x00537ae7 : 3bc1
jb Label537add ;0x00537ae9 : 72f2
Label537aeb ::
mov eax , dword ptr [ ebx + 0CCh ] ;0x00537aeb : 8b83cc000000
mov ecx , dword ptr [ esp + 20 ] ;0x00537af1 : 8b4c2414
mov dword ptr [ esi + eax + 32 ] , ebp ;0x00537af5 : 896c0620
mov eax , ebx ;0x00537af9 : 8bc3
call Fun541571 ;0x00537afb : e8719a0000
mov ecx , dword ptr [ esp + 32 ] ;0x00537b00 : 8b4c2420
mov edx , dword ptr [ ebx + 0C8h ] ;0x00537b04 : 8b93c8000000
imul ecx , ecx , 0A4h ;0x00537b0a : 69c9a4000000
mov eax , dword ptr [ edx + ecx + 24 ] ;0x00537b10 : 8b440a18
test eax , eax ;0x00537b14 : 85c0
jne Label5cd144 ;0x00537b16 : 0f8528560900
Label537b1c ::
mov edx , dword ptr [ ebx + 0C8h ] ;0x00537b1c : 8b93c8000000
mov ecx , dword ptr [ ecx + edx + 28 ] ;0x00537b22 : 8b4c111c
test ecx , ecx ;0x00537b26 : 85c9
jne Label5cd17a ;0x00537b28 : 0f854c560900
Label537b2e ::
pop edi ;0x00537b2e : 5f
pop esi ;0x00537b2f : 5e
pop ebp ;0x00537b30 : 5d
pop ebx ;0x00537b31 : 5b
add esp , 0Ch ;0x00537b32 : 83c40c
ret 08h ;0x00537b35 : c20800
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 set SYS(9166,0) to globally disable Rushmore Query Optimization in the WHERE clause (as a temporary solution) to fix the bug:
PRIVATE m.q_oldsys9166
m.q_oldsys9166=VAL(SYS(9166))
=SYS(9166,0) && Disables Rushmore Query Optimization in the WHERE clause (as a temporary solution)
SELECT * FROM tmp1 WHERE fld1=test_subproc("aaa") INTO CURSOR tmp2 nofilter && it returns correct data.
SELECT * FROM tmp1 WHERE fld1=REPL(CHR(97+RECN()-1),3) INTO CURSOR tmp2 nofilter && it returns correct data.
=SYS(9166,m.q_oldsys9166)
5. REFERENCE WEBSITES:
1, baiyujia.com:
http://www.baiyujia.com
http://www.baiyujia.com/vfpdocuments/f_vfp9fix340.asp
http://www.baiyujia.com/vfpdocuments/f_vfp9fix326.asp
http://www.baiyujia.com/vfpdocuments/f_vfp9fix327.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
6. OTHER:
For reference only, there is no guarantees.
Any questions or suggestions, please send me an email at ccb2000@163.com.
|