Last modified: 2011-11-25 07:43:05 UTC
While working on SQL SERVER support for 1.19alpha (Bug 9767), which is mostly working, I discovered an issue with at least one of the special pages. Special:ListRedirects generates SQL that looks like (LIMIT/TOP clauses, which differ between MySQL and SQL Server, were removed): SELECT tl_namespace AS namespace,tl_title AS title, COUNT(*) AS value FROM templatelinks WHERE tl_namespace = '10' GROUP BY namespace, title ORDER BY value DESC It should look like SELECT tl_namespace AS namespace,tl_title AS title, COUNT(*) AS value FROM templatelinks WHERE tl_namespace = '10' GROUP BY tl_namespace, tl_title ORDER BY value DESC The former syntax is peculiar to MySQL, while the latter syntax is correct on both MySQL and SQL Server.
To clarify -- it should be grouping on the original column names, not on the output column names?
(In reply to comment #1) > To clarify -- it should be grouping on the original column names, not on the > output column names? Yes, the form should be SELECT column-name AS alias ... GROUP BY column-name ...
So is this the only such problem or are there others that need fixing? A test plan would probably be good; do phpunit tests run clean on MSSQL yet? We should make sure that all the various query special pages get their queries run in unit tests.
(In reply to comment #3) > A test plan would probably be good Please help by filling out http://www.mediawiki.org/wiki/Database_testing with relevant information.
Well, relevant for this would be having unit tests running all the query page queries to make sure they work. That page seems to cover more general low-level setup issues...?
Recommend changing line 71 of SpecialMostlinkedtemplates.php from 'options' => array( 'GROUP BY' => 'namespace, title' ) to 'options' => array( 'GROUP BY' => 'tl_namespace, tl_title' )
(In reply to comment #3) > So is this the only such problem or are there others that need fixing? > > A test plan would probably be good; do phpunit tests run clean on MSSQL yet? We > should make sure that all the various query special pages get their queries run > in unit tests. The only other example of this particular problem I've found is in maintenance/storage/storageTypeStats.php, line 77. I could have missed some.
I have applied DJ Bauch patch to trunk with r101470. The issue also happens in REL1_18 and thus the patch will need a backport (revision tagged accordingly). I am wondering if we could detect this issue when building the query with makeSelectOptions(). Might throw up a developer warning / exception which will help us fix the issue proactively.
(In reply to comment #5) > Well, relevant for this would be having unit tests running all the query page > queries to make sure they work. I have opened bug issue Bug 32118 against 'Mediawiki > unit testing' component: test special pages SQL queries against all supported DB
Added it as a dep.