FIX: Incorrect Results from SELECT with Nested SELECTs and OR

ID: Q140579

3.00 WINDOWS kbprg kbfixlist kbbuglist

The information in this article applies to:

• Microsoft Visual FoxPro for Windows, version 3.0

SYMPTOMS

A SELECT statement with two nested SELECTs connected by an OR may return incorrect results depending on which tables are open in which work areas before the select is run.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Visual FoxPro 3.0b for Windows.

MORE INFORMATION

Steps to Reproduce Problem

1. Create three tables named Test1, Test2, and Test3. Place the following

   information in each table:

   Table Test1 has one field (char1) containing these records:

      AAAAA
      BBBBB
      CCCCC
      DDDDD
      EEEEE
      FFFFF
      GGGGG
      HHHHH

   Table Test2 has two fields (Char1 and Char2) containing these records:

      char1    char2
      AAAAA    ZZZZZ
      ZZZZZ    AAAAA
      YYYYY    WWWWW
      BBBBB    VVVVV
      UUUUU    CCCCC
      WWWWW    PPPPP
      VVVVV

   Table Test3 has one field (char1) with one record containing:

      qqqqq

   depending on which tables are open and which work areas they are using:

   CLOSE DATA
   USE test1 IN 1
   SELECT char1 FROM test1 WHERE ;
     char1 IN (SELECT char1 from test2) OR ;
     char1 IN (SELECT char2 from test2) ;
     INTO CURSOR query
   BROWSE TITLE 'Test1 open in WA1'   && returns all records from test1

   CLOSE DATA
   USE test3
   SELECT char1 FROM test1 WHERE ;
     char1 IN (SELECT char1 FROM test2) OR ;
     char1 IN (SELECT char2 FROM test2) ;
     INTO CURSOR QUERY
   BROWSE TITLE 'Test3 open in WA1'   && returns all records from test1

   CLOSE DATA
   USE test1 IN 2
   SELECT char1 FROM test1 WHERE ;
     char1 IN (SELECT char1 FROM test2) OR ;
     char1 IN (SELECT char2 FROM test2) ;
     INTO CURSOR QUERY
   BROWSE TITLE 'Test1 open in WA2' && returns correct records from test1

   CLOSE DATA
   SELECT char1 FROM test1 WHERE ;
     char1 IN (SELECT char1 FROM test2) OR ;
     char1 IN (SELECT char2 FROM test2) ;
     INTO CURSOR QUERY
   BROWSE TITLE 'No tables open'   && returns correct records from test1

   SELECT char1 FROM test1 WHERE ;
     char1 IN (SELECT char1 FROM test2) or ;
     char1 IN (SELECT char2 FROM test2) ;
     INTO CURSOR QUERY
   BROWSE TITLE 'Test1 in WA1 and Test2 in WA2'   && returns no records

Keywords          : FxprgGeneral kbbuglist kbfixlist
Version           : 3.00
Platform          : WINDOWS
Solution Type     : kbfix