Preface:
a) Before VFP9, SQL LIKE , as the LIKE() function, not respect the COLLATE setting, and uses only binary comparison (MACHINE collate).

b) On VFP9 the SQL sString LIKE sMask clause with sMask can to be optimizable.

c) VFP have a new 90 ENGINEBEHAVIOR level.

d) The expected functionally of SQL LIKE clause is that of respect the COLLATE context,
like MS SQL or other RDBMS.

Then, the questions are:

On VFP9 the LIKE clause it respects, in restrictive way, the COLLATE context
in order to make the choice of the index for the optimization,
but it does not respect the rules of the COLLATE.

- Has sense this behavior?

If none index with COLLATE context exists, but a MACHINE index exist,
then VFP9 not optimized the LIKE comparison!

- Has sense this behavior?

Inasmuch as the code that implements the LIKE has been modified,
why it has not been finished the job and implemented the point d)
when the level of the Engine is 90?

Repro code:

CLEAR
CREATE CURSOR cursorTest (CharField C)

INSERT INTO cursorTest VALUES ('a')

SYS(3054,11)

SET COLLATE TO "MACHINE"

INDEX ON CharField TAG tagMach		COLLATE "MACHINE"

* Now the Engine uses the tagMach index for the = and == and LIKE comparison

SELECT "= " comparison,COUNT(*) FROM cursorTest WHERE CharField ='A';
UNION ALL ;
SELECT "==" comparison,COUNT(*) FROM cursorTest WHERE CharField =='A';
UNION ALL ;
SELECT "LK" comparison,COUNT(*) FROM cursorTest WHERE CharField LIKE 'A';
INTO CURSOR results

LIST  && Expected 0,0,0

SET COLLATE TO "GENERAL"

* Now the Engine not found a GENERAL index, and none is optimized

SELECT "= " comparison,COUNT(*) FROM cursorTest WHERE CharField ='A';
UNION ALL ;
SELECT "==" comparison,COUNT(*) FROM cursorTest WHERE CharField =='A';
UNION ALL ;
SELECT "LK" comparison,COUNT(*) FROM cursorTest WHERE CharField LIKE 'A';
INTO CURSOR results

LIST  && Expected 1,1,1

SELECT cursorTest

INDEX ON CharField TAG tagGeneral 	COLLATE "GENERAL"

* Now the Engine uses the tagGeneral index for the = and == comparison,
* the LIKE is optimized with tagGeneral, but LIKE uses MACHINE rules!

SELECT "= " comparison,COUNT(*) FROM cursorTest WHERE CharField ='A';
UNION ALL ;
SELECT "==" comparison,COUNT(*) FROM cursorTest WHERE CharField =='A';
UNION ALL ;
SELECT "LK" comparison,COUNT(*) FROM cursorTest WHERE CharField LIKE 'A';
INTO CURSOR results

LIST  && Expected 1,1,1

SYS(3054,0)
CLOSE TABLES ALL

Fabio