Hi Fabio,

>Datatype issue:
>VFP don't support index keys with variable datatype,
>and for optimization the datatype is irrelevant; the point is closed.

at least for expression-enforced length of returned character variables
this is my take as well

>Well, VFP have not a clause for to inform the Engine that an index is deterministic or not;
>( this is a bug design for me ).
>The engine ( VFPT ) have to guess this, but it cannot.

I'ld like to specify which index to use as well,
but that is not the typical use case.

>Then it assume this:
>- a field is deterministic, this is correct
>- a VFP's native expression is deterministic, this is wrong
>- every exp that contain an UDF() is not deterministic,
> and then it cannot put the tag in the optimized plan

agreed that not using any udf in optimization seems to be a very strict
approach - correct as a default, but should be alterable by the developer
for certain queries. But speculating a bit further: if "possible nondeterminism"
is the cause for this, would this not also rule out the use of
privates in optimized expressions:

CREATE CURSOR testudfindex ( aa i )
INSERT INTO testudfindex Values(RECCOUNT())
INSERT INTO testudfindex Values(RECCOUNT())
INSERT INTO testudfindex Values(RECCOUNT())
INSERT INTO testudfindex Values(RECCOUNT())
INSERT INTO testudfindex Values(RECCOUNT())
INSERT INTO testudfindex Values(RECCOUNT())
INSERT INTO testudfindex Values(RECCOUNT())
CLEAR
SYS(3054,12)

INDEX on aa			TAG tfield_TAG

SELECT * FROM testudfindex WHERE aa =1 INTO ARRAY dummy
PRIVATE lnX
lnX = 1

INDEX on aa + lnX TAG withmem

SELECT * FROM testudfindex WHERE aa + lnX  = 2 INTO ARRAY dummy

SELECT * FROM testudfindex WHERE aa + lnX  = FuncDet() INTO ARRAY dummy1

SELECT * FROM testudfindex WHERE aa + lnX  = FuncSideEffect() INTO ARRAY dummy2

*  RAND() IS A NOT DETERMINISTIC FUNCTION ( AA=1 OR 0 )
INDEX on RAND(aa)	TAG VFPF_TAG	&&	Clause: DETERMINISTIC OFF    Default is ON
?
*BUG:  : correct is : don't use the index
SELECT * FROM testudfindex WHERE RAND(aa) =1 INTO ARRAY dummy

INDEX ON UDF(aa)	TAG udf_tag	&& Clause:	DETERMINISTIC ON  default is OFF
?
* RESTRICTION: correct is : use the index
SELECT * FROM testudfindex WHERE UDF(aa) =1 INTO ARRAY dummy

PROCEDURE UDF(x)
	RETURN x

FUNCTION FuncDet()
RETURN 1

FUNCTION FuncSideEffect()
lnX = lnX - 1 
RETURN 1

as the side effect can be as disruptive as the nondeterministic function<g>

regards

thomas