Microsoft KB Archive/176884

= BUG: Problems with SET COLLATE and Queries with Integer Fields =

Article ID: 176884

Article Last Modified on 3/14/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 3.0b for Macintosh

-



This article was previously published under Q176884



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 has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.

<div class="moreinformation_section">

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
<ol>  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 </li>  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): <pre class="fixed_text">     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

Czech,  General Hungary, Polish       Eastern European Windows, MS-DOS  22, 278, 534, 790, 1046

Slovak                Eastern European MS-DOS           21, 277, 533, 789, 1045

Slovak                Eastern European Windows          23, 279, 535, 791, 1047

Czech,  General Slovak                Kamenicky (Czech) MS-DOS       19, 275, 531, 787, 1043

General, Iceland      Icelandic MS-DOS  19, 275, 531, 787, 1043

Greek                 Greek MS-DOS      19, 275, 531, 787, 1043

Greek                 Greek Windows     23, 279, 535, 791, 1047

Russian               Russian MS-DOS    19, 275, 531, 787, 1043

Russian               Russian Windows   22, 278, 534, 787, 1043

Polish                Mazovia (Polish) MS-DOS           17, 273, 529, 785, 1041

NOTE: The Iceland collation sequence is not available in Visual FoxPro for the Macintosh. </li> 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.</li></ol>

There is a pattern to the missing records. For the problematic collation sequences on the Windows platform, if the missing numbers are converted to hexadecimal, they all end in 11. This pattern continues into at least the next 1,000 records (2,000 total). On the Macintosh platform, the missing numbers, when converted to hexadecimal all end in 18. However, as an exception to the pattern, hexadecimal 518 or decimal 1304, does not turn up missing when the number of records in the test tables is 2,000.

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.

<div class="references_section">