>Hi Matt,
>
>It looks like a bug to me. In addition, VFP doesn't use BINARY index with ISNULL() for query optimization anyway, which is another bug.

Hi Sergey,

VFP use ISNULL() like a expression,
for EMPTY(),DELETE(),INLIST()... the VFP planner have a special catch case.

That is a VFP's extremely poor design.

CLEAR
CREATE CURSOR Test (Int1 I NULL, int2 I NOT NULL)
INDEX ON DELETED() TAG BinDel BINARY
INDEX ON ISNULL(int1) FOR NOT DELETED() TAG NormTest1 && this is sufficient for do the full optimization
INDEX ON ISNULL(int2) TAG BinTest2 BINARY	&& binary require another index for NOT DELETED
INDEX ON EMPTY(int2) TAG BinTest3 BINARY

set deleted on
=SYS(3054,12)

SELECT * FROM Test ;
	WHERE EMPTY(int2) ; && TWO INDEXES WITH BINARY CORRELATION
	INTO CURSOR crsTemp

?
SELECT * FROM Test ;
	WHERE ISNULL(int1)=.T. ; && 1 INDEX !!!
	INTO CURSOR crsTemp

?
SELECT * FROM Test ;
	WHERE ISNULL(int2)=.T. ; && TWO INDEXES WITH BINARY CORRELATION
	INTO CURSOR crsTemp

?
? "This is the true issue, with standard SQL syntax we have a pain !!!!"
?
SELECT * FROM Test ;
	WHERE int1 IS NULL ; && only ONE INDEX FOR THE DELETED IMPLICIT FILTER
	INTO CURSOR crsTemp
	
=SYS(3054,0)

Only:

FOR NOT DELETED()

it is considered in VFP9,
whichever other expression ,
set the index as "not usable"

Ex:

FOR .T.  && useful for exclude a temporary index

Fabio


renders the not usable index from the ottimizzatore