Page MenuHomePhabricator

MSSQL Error when using limit statement in Cargo Query
Closed, DeclinedPublic

Description

Installed software
MediaWiki 1.27.1
PHP 5.6.30 (cgi-fcgi)
MS SQL Server 10.50.6529
Cargo 1.3
Page Forms 4.0.2
Problem:
During testing of Cargo extension on a Microsoft SQL Server based MEDIAWIKI installation, we encountered some errors documented below.
The problem was discovered on a cargo page that contain the following cargo query:

{{#cargo_query:
tables=Observation

fields=_pageName=Topic,Observation_recommendation=Recommendation,keyword=Keywords
where=Observation_type LIKE 'WFC'
limit=20
default=No results to display
more results text=Further Results...
format=dynamic table

}}

problem: at the bottom of the screen, is the line "Further Results" If that link is clicked on we receive the following error:

A database query error has occurred. This may indicate a bug in the software.
◾Query:
SELECT _pageName AS [Topic],Observation_recommendation AS [Recommendation],keywordfull AS [Keywords] FROM ( SELECT ROW_NUMBER() OVER(ORDER BY [_pageName]) AS rowNumber, * FROM (SELECT _pageName AS [Topic],Observation_recommendation AS [Recommendation],keywordfull AS [Keywords] FROM [wiki].[cargo__Observation] WHERE (Observation_type LIKE 'WFC') ) sub_0 ) sub_1 WHERE rowNumber BETWEEN 21 AND 121 ORDER BY [_pageName]

◾Function: ViewDataPage::reallyDoQuery
◾Error: 207 [SQLSTATE 42S22][Error Code 207][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name '_pageName'. [SQLSTATE 42S22][Error Code 207][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name '_pageName'. [SQLSTATE 42S22][Error Code 207][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'Observation_recommendation'. [SQLSTATE 42S22][Error Code 207][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name 'keyword__full'. [SQLSTATE 42S22][Error Code 207][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Invalid column name '_pageName'. [SQLSTATE 42000][Error Code 8180][Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Statement(s) could not be prepared.

Backtrace:

Backtrace:
#0 C:\inetpub\wwwroot\WCR\includes\db\Database.php(901): DatabaseBase->reportQueryError('[SQLSTATE 42S22...', 207, 'SELECT _pageNam...', 'ViewDataPage::r...', false)
#1 C:\inetpub\wwwroot\WCR\includes\db\DatabaseMssql.php(427): DatabaseBase->query('SELECT _pageNam...', 'ViewDataPage::r...')
#2 C:\inetpub\wwwroot\WCR\includes\specialpage\QueryPage.php(422): DatabaseMssql->select(Array, Array, Array, 'ViewDataPage::r...', Array, Array)
#3 C:\inetpub\wwwroot\WCR\includes\specialpage\QueryPage.php(575): QueryPage->reallyDoQuery(101, 20)
#4 C:\inetpub\wwwroot\WCR\extensions\Cargo\specials\CargoViewData.php(37): QueryPage->execute(NULL)
#5 C:\inetpub\wwwroot\WCR\includes\specialpage\SpecialPage.php(479): CargoViewData->execute(NULL)
#6 C:\inetpub\wwwroot\WCR\includes\specialpage\SpecialPageFactory.php(576): SpecialPage->run(NULL)
#7 C:\inetpub\wwwroot\WCR\includes\MediaWiki.php(282): SpecialPageFactory::executePath(Object(Title), Object(RequestContext))
#8 C:\inetpub\wwwroot\WCR\includes\MediaWiki.php(745): MediaWiki->performRequest()
#9 C:\inetpub\wwwroot\WCR\includes\MediaWiki.php(519): MediaWiki->main()
#10 C:\inetpub\wwwroot\WCR\index.php(43): MediaWiki->run()
#11 {main}

Event Timeline

Paladox may well be right - upgrading to the latest Page Forms code may fix this problem.

Update PageForms to Page Forms 4.1 (380aafb)
Still are seeing the above problem.

Update PageForms to Page Forms 4.1 (380aafb)
Still are seeing the above problem.

Also try updating cargo please.

I updated Cargo as of 0900 23 March 2017, has there been a change since then?

Oops, sorry, I meant "upgrading to the latest Cargo code". But clearly you are using the latest Cargo code. I'll have to look into this.

Okay, I think I figured out the problem - it has to do with the unusual way in which "offset" queries (e.g. "display rows 21 to 40") are handled in SQL Server. Out of curiosity, if you remove all the aliases from the query, so just have it be "fields=_pageName, Observation_recommendation, keyword", does the "More results" link work?

Did you try that out? I think I might have a fix for this, but first I want to make sure that it's the aliases that are the problem.