FIX: Subquery Causes SUM to Return Incorrect Results (165066)

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