We have found a discrepancy in the results from SQL Selects in VFP7, depending on whether the files being queried have indexes or not. Try running the following code and see for yourself. I am using VFP7 with the 1st service pack (VFP version: 07.00.0000.9262).

We also tried this with VFP6 SP5 and found even different results. (see comments in following code).

The purpose of these select statements is to locate records that exist in the "left" table that are not in the "right" table.

Can anyone explain? It seems to be a bug.

************* copy this code and run it. *************
CLOSE all

* create 2 dummy tables in current directory and add 2 records to each
* note: one record is different in the tables.
CREATE TABLE tbl1 (userid c(10))
INSERT INTO tbl1 (userid) VALUES ("mark")
INSERT INTO tbl1 (userid) VALUES ("ed")

CREATE TABLE tbl2 (userid c(10))
INSERT INTO tbl2 (userid) VALUES ("john")
INSERT INTO tbl2 (userid) VALUES ("ed")

* run query on our tables, which are not indexed.
* As expected, in VFP7, this will return 1 row. (the row that doesn't exist in tbl2).
* In VFP6 we get 0 rows.
SELECT tbl1.*;
FROM tbl1;
LEFT JOIN tbl2;
ON;
tbl1.userid = tbl2.userid;
WHERE;
ISNULL(tbl2.userid)

* now run same query, but on indexed files.
* In VFP7, this will incorrectly return ALL rows.
* In VFP6 we get 0 rows.
SELECT tbl1
INDEX ON userid TAG userid
SELECT tbl2
INDEX ON userid tag userid

SELECT tbl1.*;
FROM tbl1;
LEFT JOIN tbl2;
ON;
tbl1.userid = tbl2.userid;
WHERE;
ISNULL(tbl2.userid)


* now run same query using the HAVING clause instead of the WHERE clause.
* This will return correct results, in VFP7 or VFP6, whether the file is indexed or not.
SELECT tbl1.*;
FROM tbl1;
LEFT JOIN tbl2;
ON;
tbl1.userid = tbl2.userid;
HAVING;
ISNULL(tbl2.userid)


CLOSE ALL
DELETE FILE tbl1.*
DELETE FILE tbl2.*

***********