Q140579: FIX: Incorrect Results from SELECT with Nested SELECTs and OR

Article: Q140579
Product(s): Microsoft FoxPro
Version(s): WINDOWS:3.0
Operating System(s): 
Keyword(s): kbbuglist kbfixlist
Last Modified: 24-MAR-2000

-------------------------------------------------------------------------------
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

2. Run the following program, and notice that the results of the query vary
  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

Additional query words: VFoxWin fixlist3.0b buglist3.00

======================================================================
Keywords          :  kbbuglist kbfixlist
Technology        : kbVFPsearch kbAudDeveloper kbVFP300
Version           : WINDOWS:3.0
Issue type        : kbbug
Solution Type     : kbfix

=============================================================================

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Copyright Microsoft Corporation 1986-2002.