Microsoft KB Archive/192862

= BUG: Incorrect Return of SubSelect in SELECT-SQL Statement =

Article ID: 192862

Article Last Modified on 5/12/2003

-

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

-



This article was previously published under Q192862



SYMPTOMS
A SQL Select using the Not In clause with a subquery might incorrectly return no records.



RESOLUTION
Use a user defined function in the subselect similar to the example below: SELECT * FROM orders WHERE order_id NOT IN ; (SELECT order_id FROM orditems WHERE MyFunction(order_id))

FUNCTION MyFunction LPARAM lx      RETURN .F.    ENDFUNC



STATUS
Microsoft has confirmed this to be a bug in the Microsoft products listed at the beginning of this article.



MORE INFORMATION
In the following cases, the main query could return an incorrect number of records: SELECT * FROM orders WHERE order_id NOT IN ; (SELECT order_id FROM orditems WHERE 1=2)

-or-

SELECT * FROM orders WHERE order_id NOT IN ; (SELECT order_id FROM orditems WHERE .F.) The above queries return 0 records rather than all records in the table.

Steps to Reproduce Behavior

 * 1) Open the TestData Sample Database.
 * 2) Run one of the SQL Select statements listed in the MORE INFORMATION section of this article.
 * 3) Notice that the Select returns no rows.

Additional query words: kbDSupport KBDSE kbVFp300bbug kbVFp500abug kbVFp600bug kbSQL

Keywords: kbbug KB192862

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.