FIX: Subquery Causes SUM to Return Incorrect Results

ID: Q165066

The information in this article applies to:

Microsoft Visual FoxPro for Windows, version 5.0

SYMPTOMS

Including a subquery with a SQL-SELECT statement containing a SUM command causes the SUM command to report incorrect amounts.

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 Visual FoxPro 5.0a for Windows.

MORE INFORMATION

The following code creates a table, populates it with data, and performs a SELECT-SQL statement containing a subquery.

Steps to Reproduce Behavior

Create a program containing the following lines of code and execute it:


   *** Begin program code

   CLOSE ALL
   CREATE CURSOR Master ( nId I, cName C(10))
   INSERT INTO Master VALUES ( 1, "One")
   INSERT INTO Master VALUES ( 2, "Two")
   INSERT INTO Master VALUES ( 3, "Three")
   INSERT INTO Master VALUES ( 4, "Four")

   CREATE CURSOR Results ( nId I, dDate D)
   INSERT INTO Results VALUES ( 1, DATE())
   INSERT INTO Results VALUES ( 1, DATE())
   INSERT INTO Results VALUES ( 1, DATE())
   INSERT INTO Results VALUES ( 1, DATE())
   INSERT INTO Results VALUES ( 2, DATE() + 3)
   INSERT INTO Results VALUES ( 2, DATE() + 3)
   INSERT INTO Results VALUES ( 2, DATE() + 3)

   CREATE CURSOR Detail ( nId I, nAmount Y)
   INSERT INTO Detail VALUES ( 1, 100.00)
   INSERT INTO Detail VALUES ( 3, 300.00)

   * This will show correct sums
   WAIT WINDOW "Correct:" timeout 3
   SELECT Master.cName, ;
      SUM( NVL( Detail.nAmount, 0)) AS nSum ;
      FROM Master LEFT OUTER JOIN Detail ON Detail.nId = Master.nId ;
      GROUP BY 1

   * However, if I add an additional subquery, sum results are wrong.
   WAIT WINDOW "w/ Subquery - Wrong under VFP 5.0:" timeout 3
   SELECT Master.cName, ;
      SUM( NVL( Detail.nAmount, 0)) AS nSum ;
      FROM Master LEFT OUTER JOIN Detail ON Detail.nId = Master.nId ;
      WHERE Master.nId IN ( SELECT DISTINCT nId FROM Results ) ;
      GROUP BY 1

   CLOSE ALL
   *** End of program code

Executing the first query in Visual FoxPro 5.0 gives the following results:


   Cname       Nsum
   -----       -----
   Four        0.0000
   One         100.000
   Three       300.000
   Two         0.00000

The second query in Visual FoxPro 5.0, which contains the subquery, gives the following incorrect results based on the same data:


   Cname       Nsum
   -----       -----
   One         0
   Two         0

In Visual FoxPro 5.0a, the results of the first query remain the same. However, the second query correctly returns to following values:


   Cname       Nsum
   -----       -----
   One         100.0000
   Two         0.0000

Additional query words: 5.0

Keywords : kbVFp500afix kbVFp500bug FxprgSql
Version : WINDOWS:5.0
Platform : WINDOWS
Issue type : kbbug