Hi Andrus,
Usually, VFP9 reports this error when it failed to accurately determine the maximum length of the index/ORDER BY/GROUP BY key and one of the generated keys doesn't fit into reserved space. To determine the maximum length of the key, SQL engine evaluates expression, usually, against the first record in the table, but sometimes against a blank record, even if there are no blank records in the table. If this evaluation doesn't return a value of the max size, the error is expected.
VFP8 doesn't report this error and this is a BUG, it silently returns incorrect result. Run the following code in VFP8, note that the first query returns unexpected result. In VFP9 the error is reported.
CLOSE DATABASES all
CREATE CURSOR foo (f1 I)
insert INTO foo VALUES (1)
insert INTO foo VALUES (10)
insert INTO foo VALUES (10)
insert INTO foo VALUES (100)
insert INTO foo VALUES (100)
insert INTO foo VALUES (100)
SELECT TRANSFORM(f1),count(*) FROM foo ;
GROUP BY 1 INTO CURSOR res1
IF USED("res1")
SELECT res1
?ALIAS()
list
ENDIF
SELECT LEFT(TRANSFORM(f1),11),count(*) FROM foo ;
GROUP BY 1 INTO CURSOR res2
IF USED("res2")
SELECT res2
?ALIAS()
list
ENDIF
RETURN
Result in VFP8:
RES1
Record# EXP_1 CNT
1 1 6
RES2
Record# EXP_1 CNT
1 1 1
2 10 2
3 100 3
Thanks,
Aleksey.