>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 = 1
>*--
>
>*-- 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

this is a new bug.

this minimize the repro code, NVL(SUM(1),0) is a workaround

CLEAR

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")

INDEX ON DELETED() TAG deleted BINARY

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

INDEX ON DELETED() TAG deleted1 BINARY

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

*-- Result: 2 rows
SYS(3054,12)

*-- RETURN 1
SELECT * FROM table1 INNER JOIN table2 ON ;
table1.field1=table2.field1 AND table1.field2=table2.field2

*-- RETURN 1 *** workaround ***
SELECT CAST(NVL(SUM(1),0) AS I) FROM table1 INNER JOIN table2 ON ;
table1.field1=table2.field1 AND table1.field2=table2.field2

*-- Bug: RETURN 3
SELECT COUNT(*) FROM table1 INNER JOIN table2 ON ;
table1.field1=table2.field1 AND table1.field2=table2.field2

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