>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

Rolf,
It may or not be considered as a bug, I don't care with that part. For years back I have said _Tally was not trustable if collate is used. It still stands in one way or the other (here in your code it was a way to get correct result - but not everytime:). I can show you sample code too giving hard to understand results when used with collate. IOW I think "collate" should always be 'machine'.

3rd item (deleted() tag) is debatable.
Cetin