Can't retrieve data from field with table alias
Closed, ResolvedPublic
Actions

Assigned To

Authored By

	Onandonandonandon
	Aug 3 2021, 7:40 AM

Description

Fields from an aliased table can't be read into data.

Consider following query:

{{#get_db_data:db=bugs
|from=heat=h,profiles=p 
|join on=h.assigned_to=p.userid
|data=loginName=p.login_name,rate=count(*),heat=sum(h.heat)
|group by=p.login_name
}}
{| class="prettytable sortable" width="100%"
! Assigned to
! Count
! Sum
{{#for_external_table:<nowiki/>
{{!}}- style="background-color:#EEEEEE;"
{{!}} {{{loginName}}}
{{!}} {{{rate}}}
{{!}} {{{heat}}}
}}
|}
{{#clear_external_data:}}

The resulting table will hold empty entries for column loginName. If the assignment is changed to |data=loginName=login_name, the column is filled correctly.

While this is a doable workaround for some queries, this fails, if two tables containing identical column names need to be joined:

Giving an alias in data will result in an empty column
Removing the alias will fail the query due to ambiguous names

I've noticed this behavior, while migrating a MediaWiki 1.26 to 1.36. In 1.26 the queries can retrieve the data from the aliased fields. The breaking changes seem to have been introduced with REL1_31.

Details

	Subject	Repo	Branch	Lines +/-
	Enable table {{#get_db_data:}} to work with table prefixes, including aliases, in \|data= param.	mediawiki/extensions/ExternalData	master	+13 -1

Customize query in gerrit

Event Timeline

Onandonandonandon created this task.Aug 3 2021, 7:40 AM

Restricted Application added a subscriber: Aklapper. · View Herald TranscriptAug 3 2021, 7:40 AM

alex-mashin added a subscriber: Yaron_Koren.Aug 3 2021, 7:49 AM

alex-mashin subscribed.

Can you reproduce undesired behaviour on this database?

In T287942#7254771, @alex-mashin wrote:

Can you reproduce undesired behaviour on this database?

I'm behind a corporate proxy, which follows a restrictive policy for outgoing connections. Thus I can't access this database. Sorry.

Described behavior shouldn't be limited to my database. A similar issue has been reported on the extension's discussion page.

I can give an example for the wiki-db itself:

External data configuration

$edgDBServer['wiki'] = getenv('WIKI_DATABASE_HOST');
$edgDBServerType['wiki'] = "mysql"; 
$edgDBName['wiki'] =  getenv('WIKI_DATABASE');
$edgDBUser['wiki'] =  getenv('WIKI_DATABASE_USER');
$edgDBPass['wiki'] =  getenv('WIKI_DATABASE_PASSWORD');

Query to show latest revision of a page

{{#get_db_data:
db=wiki
|from=page=p,revision=r
|join on=p.page_id=r.rev_page
|where=p.page_latest=r.rev_id
|data=title=p.page_title,latest=r.rev_id
|limit=100
}}
{| class="prettytable sortable" width="100%"
! Title
! Latest Rev
{{#for_external_table:<nowiki/>
{{!}}- style="background-color:#EEEEEE;"
{{!}} {{{title}}}
{{!}} {{{latest}}}
}}
|}

With the aliases, a table with empty rows and columns is shown.

I can see identical behavior with External Data 2.3 and 2.4.1.

alex-mashin claimed this task.Aug 3 2021, 9:42 AM

alex-mashin triaged this task as Unbreak Now! priority.

Change 709670 had a related patch set uploaded (by Alex Mashin; author: mashin):

[mediawiki/extensions/ExternalData@master] Enable table {{#get_db_data:}} to work with table prefixes, including aliases, in |data= param.

https://gerrit.wikimedia.org/r/709670

gerritbot added a project: Patch-For-Review.Aug 3 2021, 9:48 AM

Awesome @alex-mashin!

Current master patched with your set works.

Thanks for the quick fix!

Change 709670 merged by jenkins-bot: