Last modified: 2011-11-25 07:43:05 UTC

Wikimedia Bugzilla is closed!

Wikimedia migrated from Bugzilla to Phabricator. Bug reports are handled in Wikimedia Phabricator.
This static website is read-only and for historical purposes. It is not possible to log in and except for displaying bug reports and their history, links might be broken. See T33913, the corresponding Phabricator task for complete and up-to-date bug report information.
Bug 31913 - GROUP BY clauses incorrect in SQL generated for special pages
GROUP BY clauses incorrect in SQL generated for special pages
Status: NEW
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
1.18.x
PC Windows 7
: Normal normal (vote)
: ---
Assigned To: Nobody - You can work on this!
: need-unittest
Depends on: 32118
Blocks:
  Show dependency treegraph
 
Reported: 2011-10-24 06:15 UTC by D J Bauch
Modified: 2011-11-25 07:43 UTC (History)
2 users (show)

See Also:
Web browser: ---
Mobile Platform: ---
Assignee Huggle Beta Tester: ---


Attachments

Description D J Bauch 2011-10-24 06:15:06 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.
Comment 1 Brion Vibber 2011-10-24 14:53:31 UTC
To clarify -- it should be grouping on the original column names, not on the output column names?
Comment 2 D J Bauch 2011-10-24 15:58:12 UTC
(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 ...
Comment 3 Brion Vibber 2011-10-24 18:08:19 UTC
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.
Comment 4 Mark A. Hershberger 2011-10-24 20:28:42 UTC
(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.
Comment 5 Brion Vibber 2011-10-25 20:06:34 UTC
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...?
Comment 6 D J Bauch 2011-10-26 03:21:25 UTC
Recommend changing line 71 of SpecialMostlinkedtemplates.php from
	'options' => array( 'GROUP BY' => 'namespace, title' )
to
	'options' => array( 'GROUP BY' => 'tl_namespace, tl_title' )
Comment 7 D J Bauch 2011-10-26 03:34:19 UTC
(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.
Comment 8 Antoine "hashar" Musso (WMF) 2011-11-01 13:19:05 UTC
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.
Comment 9 Antoine "hashar" Musso (WMF) 2011-11-01 13:28:28 UTC
(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
Comment 10 Brion Vibber 2011-11-01 15:54:30 UTC
Added it as a dep.

Note You need to log in before you can comment on or make changes to this bug.


Navigation
Links