>See MSKB#Q176884 BUG: Problems with SET COLLATE and Queries with Integer Fields

Wow, that explains it. The symptoms don't seem to fit because I was already doing what they suggest as a fix (using JOIN instead of WHERE). But I SET COLLATE TO "MACHINE" before the queries and that sure fixed it! Thanks very much, Sergey.

I'm not sure that I like this solution but at least I know that I'm not going crazy <g>. What are the ramifications of changing SET COLLATE on the fly like that when there are existing indexes that are (necessarily) using a different collation sequence? Is it going to affect the query's ability to do WHERE ATC() and other such operations on character fields in those tables? Will it disable the Rushmore optimization which uses those indexes? I do SET COLLATE back again after the query.

If there is a risk that changing SET COLLATE will break other things, then I suppose that I'll have to use numeric key fields instead of integer ones to avoid the bug. But that will negatively affect performance and table size.

Thanks again, Sergey, and I'd appreciate any further advice.

-Bob