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

ID: Q139048

The information in this article applies to:

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

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

In this example the article's CAUSE section applies:

   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

line would look like the following:

   WHERE db_key = updval

KBCategory: kbprg kbbuglist KBSubcategory: FxprgSql vfoxwinbuglist3.00 buglist3.00b buglist5.00 Additional reference words: 5.00 3.00 3.00b inconsistent kbvfp300 kbvfp500 kbvfp600

Keywords          : kbprg kbVFp kbVFp300bbug kbVFp500abug kbVFp500bug buglist3.00 FxprgSql kbbuglist
Version           : WINDOWS: 3.0,3.0b,5.0,5.0a,6.0
Platform          : WINDOWS
Issue type        : kbbug