Hi all,

because it would be useful to do in our application some queries with collate=’general’, I did some tests. Thereby I got wrong results with “select count(*)..”, when I join 2 Tables. I think, it’s a bug. I tried it in VFP6-SP5 and VFP9-SP1. Please try the following code to reproduce the problem.

My Sample-Code:

SET COLLATE TO 'GENERAL'
SET DELETED ON
 
*-- Create Tables and Index
CREATE TABLE table1 (field1 C(1), field2 C(1))
INSERT INTO table1 (field1,field2) VALUES("A","1")
INSERT INTO table1 (field1,field2) VALUES("A","2")
INSERT INTO table1 (field1,field2) VALUES("A","3")
INSERT INTO table1 (field1,field2) VALUES("B","1")
INSERT INTO table1 (field1,field2) VALUES("B","2")
INSERT INTO table1 (field1,field2) VALUES("B","3")

INDEX ON field1 TAG field1 COLLATE 'general' 
INDEX ON field2 TAG field2 COLLATE 'general' ADDITIVE
INDEX ON DELETED() TAG deleted COLLATE 'general' ADDITIVE

CREATE TABLE table2 (field1 C(1), field2 C(1))
INSERT INTO table2 (field1,field2) VALUES("A","1")
INSERT INTO table2 (field1,field2) VALUES("A","2")
INSERT INTO table2 (field1,field2) VALUES("B","1")
INSERT INTO table2 (field1,field2) VALUES("B","2")

INDEX ON field1 TAG field1 COLLATE 'general' 
INDEX ON field2 TAG field2 COLLATE 'general' ADDITIVE
INDEX ON DELETED() TAG deleted COLLATE 'general' ADDITIVE

*--
*-- Count the joined records. Expected Result = 4
*--

*-- Wrong Result: 12
SELECT COUNT(*) FROM table1 INNER JOIN table2 ON ;
table1.field1=table2.field1 AND ;
table1.field2=table2.field2

*-- Wrong Result: 8
SELECT COUNT(*) FROM table1 INNER JOIN table2 ON ;
table1.field2=table2.field2 AND ;
table1.field1=table2.field1

*-- Correct Result: 4
SELECT COUNT(*) FROM table1 INNER JOIN table2 ON ;
table1.field1+table1.field2=table2.field1+table2.field2

When I do only one of the following things, the result is correct:

1. SET COLLATE TO ‘MACHINE’ (with or without changing the collate sequence of the indexes)
2. SET DELETED OFF (not useful in a real world application)
3. Delete the tag ‘DELETED’ (in great tables I always create the tag deleted for better performance)
4. Do the join-condition like the last query in the sample-Code
5. Use ‘SELECT ’ instead off ‘SELECT COUNT(*)’ and then count the result-set ore use _TALLY

Now my Questions:
Is this a known bug? Are the any other known settings or useful resolutions to make all the shown queries working correctly?

Thanks
Rolf