PRB: Problems with SET COLLATE and Queries with Integer Fields

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

SYMPTOMS

Queries in Visual FoxPro that compare integer fields in the WHERE clause of a SQL Select statement between two or more tables may not return all matching records. This problem occurs when the collation sequence is set to anything but Machine.

RESOLUTION

The workaround to this problem depends on which version of Visual FoxPro is being used. Four workarounds are listed below:

• Use the SET COLLATE command prior to running the query to set the collation sequence to Machine. This works in all versions of Visual FoxPro.
• Create an index on the integer fields before running the query. The program in the Steps To Reproduce Behavior section can be modified to accomplish this. All records will then be returned.

  Follow each of the two CREATE CURSOR commands in the sample program with a command to create an index on the integer field. A sample follows:

        CREATE CURSOR t1 (in1 i)
        INDEX ON in1 TAG in1
        CREATE CURSOR t2 (in2 i)
        INDEX ON in2 TAG in2
  

• Use numeric, float, or double type fields without decimal places instead of integer fields although this may affect query performance. You will need to test query speed if query performance is a concern. This workaround works in all versions of Visual FoxPro.
• If you are using Visual FoxPro 5.0 or later, use the LEFT or RIGHT JOIN syntax to join the tables rather than the WHERE clause.

  For example, using the code from the "Steps to Reproduce Behavior" section below, you can use the following queries to produce the correct results. Keep in mind that the cursors created in the program below are stripped down with only an integer field in each. The SELECT statements below may produce very different results on real data although they produce identical results on the test data.

        SELECT * FROM t1 LEFT JOIN t2 ON t1.in1=t2.in2 INTO CURSOR t3
  

        SELECT * FROM t1 RIGHT JOIN t2 ON t1.in1=t2.in2 INTO CURSOR t3
  

  Either statement above can replace the following line in the sample program below:

        SELECT * FROM t1,t2 WHERE in1=in2 INTO CURSOR t3
  

  Unfortunately, the INNER JOIN syntax has the same problem as the SQL Select statement with the WHERE clause.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The SET COLLATE command, according to the Visual FoxPro Online help file, "Specifies a collation sequence for character fields in subsequent indexing and sorting operations". The command should not have any affect on numeric or integer type fields or their comparison.

Steps to Reproduce Behavior

1. Run the following code in a program file in Visual FoxPro:

         CLEAR ALL
         CLEAR
   

         ** Store the current collation sequence to the variable yyy.
   

         yyy=SET("collate")
   

         ** Setting collate to General, a collation sequence that illustrates
         ** the problem.
   

         SET COLLATE TO "General"
   

         ** Create two cursors with one integer field in each
   

         CREATE CURSOR t1 (in1 i)
         CREATE CURSOR t2 (in2 i)
   

         ** Populate each cursor with 1000 records, inserting the loop counter
         ** into the integer field.
   

         FOR x=1 TO 1000
         INSERT INTO t1 (in1) VALUES(x)
         INSERT INTO t2 (in2) VALUES(x)
         ENDFOR
   

         ** The SELECT statement to join the two tables.
   

         SELECT * FROM t1,t2 WHERE in1=in2 INTO CURSOR t3
   

         ** Checking  _Tally to see how many records the query returned.
   

         WAIT WINDOW STR(_Tally)+" records returned by the query"
   

         ** Start of loop to determine what records are missing.
   

         x=0
         SCAN
         x=x+1
         IF NOT in1=x
         WAIT WINDOW "Missing integer"+ STR(x)
         x=in1
         ENDIF
         ENDSCAN
   

         ** Set collate back to its original setting
   

         SET COLLATE TO yyy
   

2. The following table shows which records, out of the 1,000 in the test tables created above, are missing with the different collation settings and the platform (Windows vs. Macintosh.)

         Collation Sequence     Platform           Missing Records
         ------------------     --------           ---------------
         Dutch,  General
         German, Iceland
         Nordan, Spanish
         Swefin                 Windows           17, 273, 529, 785
   
         Uniqwt                 Windows, Mac      46, 302, 558, 814
   
         Dutch,  General
         German
         Nordan, Spanish
         Swefin                 Mac               24, 280, 536, 792
   
      NOTE: The Iceland collation sequence is not available in Visual FoxPro
      for the Macintosh.
   
   

3. Change the command SET COLLATE TO "General" to SET COLLATE TO "Machine" in the program above and note that no records are missing. One thousand records are returned from the query.

The Unique collation sequence's pattern is also different. Here the missing numbers all end in E when converted to hexadecimal. If the number of records used is increased to 2,000, hexadecimal numbers 42E (decimal 1070) and 52E (decimal 1326) are not missing.

The problem described in this article is similar to another problem that was fixed in Visual FoxPro 5.0a that was related to character fields not integer fields.

REFERENCES

For additional information, please see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q164869
   TITLE     : SET COLLATE TO "GENERAL" May Affect Search Results