Hi, I was unable to reproduce this on both 1.16 and trunk (the latter had lots of category-related changes though). Could you specify which SQLite version do you have (from Special:Version) and attach a sample database file?

phil wrote:

sqlite database that reproduces the bug (for me)

sqlite version 3.3.6

This a pristine just-installed database, with the single change of adding Main_Page to [[Category:Test]] and confirming that it doesn't appear (for me)

Attached:

I managed to find 3.3.7, and everything still worked for me. The only visible difference between your database and databases created by later versions of SQLite is PRAGMA legacy_file_format that is off for you and on for newer versions (this is a documented change in SQLite behaviour).

Can you try the following SQL:

SELECT page_title,page_namespace,page_len,page_is_redirect,cl_sortkey,cat_id,cat_title,cat_subcats,cat_pages,cat_files FROM page INNER JOIN categorylinks ON ((cl_from = page_id)) LEFT JOIN category ON ((cat_title = page_title AND page_namespace = 14)) WHERE (1 = 1) AND cl_to = 'Test' ORDER BY cl_sortkey LIMIT 201

To ensure that it's being run in the same environment, it should be executed via maintenance/sql.php

If everything works, you should receive one row of results. The behaviour you're observing indicates that it returns no rows for you.

On a side note, SQLite 3.3.6 is more than 4 years old, has bugs that were fixed in later versions and has no full-text search support. I wonder if we should drop support for it completely. Are your packages up to date? Or you're using something in the line of Debian Lenny, RHEL and XAMPP?

Phil, did you try Max's select query?

No response, assuming it's due to antiquated SQLite version. I'll consider adding a warning when installing on anything less than 3.6.