FIX: SQL SELECT Can Cause Extra Output

ID: Q106279

2.50    | 2.50
WINDOWS | MS-DOS kbprg kbfixlist kbbuglist

The information in this article applies to:

SYMPTOMS

Under certain circumstances, a Structured Query Language (SQL) SELECT command can return extra output.

NOTE: This problem does not occur on all hardware.

STATUS

Microsoft has confirmed this to be a problem in FoxPro 2.5 for MS-DOS and FoxPro 2.5 for Windows. This problem was corrected in FoxPro 2.5a for MS-DOS and FoxPro 2.5a for Windows.

MORE INFORMATION

Steps to Reproduce Problem

Create and run a program with the following code:

   * Create A_FILE, B_FILE, and A_B_XREF, which is an x-ref file that
   * sets up a many-to-many relationship between A_FILE and B_FILE.
   CREATE TABLE a_file    (;
      a_code     C(1),      ;
      a_name     C(10)     )

   CREATE TABLE a_b_xref  (;
      a_code     C(1),      ;
      b_code     C(1)      )

   CREATE TABLE b_file    (;
      b_code     C(1),      ;
      b_name     C(10)     )

   CLOSE DATABASES

   * Note that some of the A_CODE values are in
   * the nonprintable range below ASCII code 32.
   INSERT INTO a_file VALUES(CHR(29),"A_NAME a29")
   INSERT INTO a_file VALUES(CHR(30),"A_NAME a30")
   INSERT INTO a_file VALUES(CHR(31),"A_NAME a31")
   INSERT INTO a_file VALUES(CHR(32),"A_NAME a32")
   INSERT INTO a_file VALUES(CHR(33),"A_NAME a33")

   INSERT INTO a_b_xref VALUES(CHR(30),"1")
   INSERT INTO a_b_xref VALUES(CHR(31),"1")
   INSERT INTO a_b_xref VALUES(CHR(29),"2")
   INSERT INTO a_b_xref VALUES(CHR(32),"2")
   INSERT INTO a_b_xref VALUES(CHR(33),"2")

   INSERT INTO b_file VALUES("1","B_NAME 1")
   INSERT INTO b_file VALUES("2","B_NAME 2")

   CLOSE DATABASES

   * Index fields
   SELECT 0
   USE a_file
   INDEX ON a_code TAG a_code
   INDEX ON a_name TAG a_name
   USE

   SELECT 0
   USE a_b_xref
   INDEX ON a_code TAG a_code
   INDEX ON b_code TAG b_code
   USE

   SELECT 0
   USE b_file
   INDEX ON b_code TAG b_code
   INDEX ON b_name TAG b_name
   USE

   SELECT ;
      DISTINCT ;
      a_file.a_name,b_file.b_name ;
      FROM a_file,a_b_xref,b_file ;
      WHERE a_file.a_code==a_b_xref.a_code AND ;
      b_file.b_code==a_b_xref.b_code AND ;
      b_file.b_name="B_NAME 1"

This program displays a Browse window containing three records. It should display only two records.

Additional reference words: FoxDos FoxWin 2.50 buglist2.50 fixlist2.50a KBCategory: kbprg kbfixlist kbbuglist KBSubcategory: FxprgSql Solution Type : kbfix

Last Reviewed: October 14, 1997