BUG: Update SQL Works Incorrectly with RAND() in WHERE Clause

• Microsoft Visual FoxPro for Windows, versions 3.0, 3.0b, 5.0, 5.0a

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 <table> WHERE RAND() > .5

   SELECT * FROM <table> WHERE myudf() > .5
   PROCEDURE myudf()
   RETURN RAND()

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Code to Reproduce 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 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

   WHERE db_key = updval