In testing the Siberia site, I got strange results (see details below). The cause is a bug in that specific version of mysql involving compound WHERE or JOIN clauses which involve equality tests across two or more tables. Greg is working with the sysadmins to update the mysql engine running on our server, as newer versions of mysql have debugged this problem. Strange that nobody else has encountered it as we have at least a dozen databases running on that engine.

SELECT documents.doc_id, archives.archive_en
FROM documents,archives
WHERE documents.doc_archive_id = archives.archive_id
--> 2261 records (seems right)

SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
WHERE 1
--> returns 2261 records (seems right)

SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents,archives,collections
WHERE documents.doc_archive_id = archives.archive_id
AND documents.doc_coll_id = collections.coll_id
--> returns 1296 records (seems wrong, should be 2261)

SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
JOIN collections ON (documents.doc_coll_id = collections.coll_id)
WHERE 1
--> returns 1296 records (seems wrong, should be 2261)

Not entirely clear what the pattern of the problem is:

SELECT documents.doc_id, archives.archive_en
FROM documents,archives
WHERE documents.doc_archive_id = archives.archive_id
AND archives.archive_id = 4
--> returns 2 records (seems right - consistent with test above)

SELECT documents.doc_id, archives.archive_en
FROM documents,archives
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
WHERE archives.archive_id = 4
-- returns 2 records (seems right - consistent with test above)

SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents,archives,collections
WHERE documents.doc_archive_id = archives.archive_id
AND documents.doc_coll_id = collections.coll_id
AND archives.archive_id = 4
--> returns 2 records (seems right - NOT consistent with test above)

SELECT documents.doc_id, archives.archive_en, collections.coll_en
FROM documents
JOIN archives ON (documents.doc_archive_id = archives.archive_id)
JOIN collections ON (documents.doc_coll_id = collections.coll_id)
WHERE archives.archive_id = 4
--> returns 2 records (seems right - NOT consistent with test above)