Previous issue Thread #964674 and Thread #964973

Bug/Issue : 18 of 36

TITLE: Bug, equivalent aggregate SELECTs return different results

VERSION: 09.00.0000.1720

EXPECTED:
these selects are invariant respect to a grouping operation

SELECT aggregatefunc() FROM  Table
SELECT constant,aggregatefunc() FROM  Table
SELECT constant,aggregatefunc() FROM  Table GROUP BY 1

therefore the result must be the same one.
With a empty table, the first return 1 row,
then all must to return 1 row.

OBSERVED: on VFP8 you have 2 bugs, on VFP9b a bug it is fixed.

NOTE: MS SQL removes the problem preventing the grouping of one constant ( fire a plan error )

REPRO code with comments:

CREATE CURSOR TT (F1 INT)
* append blank  && if the thought wavers, uncommomment this line

SET ENGINEBEHAVIOR 90

* this explain the root concept, COUNT return the number of rows
SELECT COUNT(*) FROM TT INTO CURSOR OK
BROWSE TITLE "SET ENGINEBEHAVIOR 70,80,90 = 1 rows OK "

* I ADD A CONSTANT COLUMN, this don't change the set
* THEN EXPECTED ROWS ON RESULT MUST TO BE EQUAL TO THE PREVIOUS
SELECT 1 FA,COUNT(*) FB FROM TT INTO CURSOR BUG8
BROWSE TITLE "SET ENGINEBEHAVIOR 70,80 = 0 rows : BUG   90 = 1 rows FIXED"

* I ADD A CONSTANT COLUMN AND GROUP THE CONSTANT VALUE,
* BUT GROUPING A CONSTANT VALUE IT IS EQUAL NOT TO GROUP
* THEN EXPECTED ROWS ON RESULT MUST TO BE EQUAL TO PREVIOUS
SELECT 1 FA,COUNT(*) FB FROM TT GROUP BY 1 INTO CURSOR NOK
BROWSE TITLE "SET ENGINEBEHAVIOR 70,80,90 = 0 rows : BUG"

CLOSE TABLES

Fabio