Microsoft KB Archive/139048

= Update SQL works incorrectly with RAND function in WHERE clause =

Article ID: 139048

Article Last Modified on 2/21/2005

-

APPLIES TO


 * Microsoft Visual FoxPro 3.0 Standard Edition
 * Microsoft Visual FoxPro 3.0b Standard Edition
 * Microsoft Visual FoxPro 5.0 Standard Edition
 * Microsoft Visual FoxPro 5.0a
 * Microsoft Visual FoxPro 6.0 Professional Edition
 * Microsoft Visual FoxPro 7.0 Professional Edition
 * Microsoft Visual FoxPro 8.0 Professional Edition
 * Microsoft Visual FoxPro 9.0 Professional Edition

-



This article was previously published under Q139048



SYMPTOMS
Using the RAND in an SQL UPDATE's WHERE clause can cause random results to occur.



CAUSE
The value of RAND changes each time it is evaluated, unlike most FoxPro functions. FoxPro's internal SQL parser evaluates it once to do the Rushmore optimization and once more as each filter condition is tested for each record. This causes unpredictable, random results.



RESOLUTION
Instead of using the RAND function in the WHERE clause, assign the value of the RAND function to a memory variable. Then use the variable in the WHERE clause. For an example, please see the "Code to Correct Problem" section of this article.

A new RAND can be generated for every record tested in the filter by putting RAND in a User Defined Function (UDF) and calling the UDF in the SQL command in place of RAND: SELECT * FROM WHERE RAND > .5 In this example the article's CAUSE section applies: SELECT * FROM WHERE myudf > .5 PROCEDURE myudf RETURN RAND



Code to reproduce the problem
The following code illustrates the problem. If there wasn't a problem, the code should make 1000 updates to the table. Instead, the update fails either 999 or 1000 times. *-- Code Begins Here CREATE TABLE testfile.dbf ; (mach_id i, mach_name c(10), db_key i, updcount i)  INDEX ON db_key TAG db_key SET ORDER TO  SET STATUS BAR ON

FOR ii = 1 TO 1000 INSERT INTO testfile.dbf VALUES (0, "", ii, 0) ENDFOR

USE IN testfile USE testfile.dbf IN 0 SHARED SELECT testfile failcount = 0 passcount = 0

FOR ii = 1 to 1000 *updval = INT(RAND * 1000 + 1) UPDATE testfile ; SET mach_id = 1, ; mach_name = 'JOHNDOE', ; updcount = updcount+1 ; WHERE db_key = INT(RAND * 1000 + 1) IF _TALLY = 0 Failcount=Failcount + 1 ELSE passcount=passcount + 1 ENDIF SET MESSAGE TO ALLTRIM(STR(FAILCOUNT))+' Failed, '; +ALLTRIM(STR(PASSCOUNT))+' Passed' ENDFOR *-- Code Ends Here

Code to correct the problem
To fix the problem, remove the '*' from the updval line and substitute 'updval'for 'INT(RAND * 1000 + 1)' of the UPDATE's WHERE clause. The new line would look like the following: WHERE db_key = updval

Additional query words: inconsistent kbvfp300 kbvfp500 kbvfp600

Keywords: kbbug kbprogramming KB139048

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.