Page MenuHomePhabricator

DPL3 (third-party, dynamic page list) is generating SQL which is flawed, generating MariaDB 10 errors
Closed, DeclinedPublicBUG REPORT

Description

DynamicPageList3 ver 3.3.2 (5157453, 19 sep 2018) is generating SQL in a few places which is not accepted by 10.1.38-MariaDB-0ubuntu0.18.04.1

This is either faulty or incompatible SQL syntax.

This DPL query from https://desencyclopedie.org/wiki/Cat%C3%A9gorie:BestOf fails:

{{#dpl:
|mode=userformat
|columns=3
|rowcolformat=cellspacing=10
|category=Forum BO
|category=VBO 2018
|listseparators=,\n* [[%TITLE%]]&nbsp;<small>([[%PAGE%|vote]])</small>,,
|addfirstcategorydate=true
|namespace=Forum
|replaceintitle=/^VBO\//,
|ordermethod=title
|ordercollation=latin1_swedish_ci
|allowcachedresults=true
}}

with this result:

SELECT DISTINCT REPLACE(CONCAT(IF(`page`.page_namespace = 0, '', CONCAT(CASE `page`.page_namespace WHEN 1 THEN 'Discussion:' WHEN 2 THEN 'Utilisateur:' WHEN 3 THEN 'Discussion_utilisateur:' WHEN 4 THEN 'Désencyclopédie:' WHEN 5 THEN 'Discussion_Désencyclopédie:' WHEN 6 THEN 'Fichier:' WHEN 7 THEN 'Discussion_fichier:' WHEN 8 THEN 'MediaWiki:' WHEN 9 THEN 'Discussion_MediaWiki:' WHEN 10 THEN 'Modèle:' WHEN 11 THEN 'Discussion_modèle:' WHEN 12 THEN 'Aide:' WHEN 13 THEN 'Discussion_aide:' WHEN 14 THEN 'Catégorie:' WHEN 15 THEN 'Discussion_catégorie:' WHEN 110 THEN 'Forum:' WHEN 111 THEN 'Discussion_Forum:' WHEN 112 THEN 'Désinformation:' WHEN 113 THEN 'Discussion_Désinformation:' WHEN 114 THEN 'Portail:' WHEN 115 THEN 'Discussion_Portail:' WHEN 500 THEN 'Blog_utilisateur:' WHEN 501 THEN 'Commentaire_blog_utilisateur:' WHEN 502 THEN 'Blog:' WHEN 503 THEN 'Discussion_blog_utilisateur:' WHEN 828 THEN 'Module:' WHEN 829 THEN 'Discussion_module:' WHEN 1200 THEN 'Mur:' WHEN 1201 THEN 'Fil:' WHEN 1202 THEN 'Bienvenue_mur_discussion:' WHEN 2300 THEN 'Gadget:' WHEN 2301 THEN 'Discussion_gadget:' WHEN 2302 THEN 'Définition_de_gadget:' WHEN 2303 THEN 'Discussion_définition_de_gadget:' END, ':')), `page`.page_title), '_', ' ') COLLATE latin1_swedish_ci AS `sortkey`,DATE_FORMAT(cl1.cl_timestamp, '%Y%m%d%H%i%s') AS `cl_timestamp`,`page`.page_namespace AS `page_namespace`,`page`.page_id AS `page_id`,`page`.page_title AS `page_title` FROM `page` INNER JOIN `categorylinks` `cl1` ON ((`page`.page_id = cl1.cl_from AND cl1.cl_to = 'Forum_BO')) INNER JOIN `categorylinks` `cl2` ON ((`page`.page_id = cl2.cl_from AND cl2.cl_to = 'VBO_2018')) WHERE `page`.page_is_redirect = '0' AND `page`.page_namespace = '110' ORDER BY sortkey ASC LIMIT 500

Function: Wikimedia\Rdbms\Database::query
Error: 1253 COLLATION 'latin1_swedish_ci' is not valid for CHARACTER SET 'binary' (localhost)

This query from https://desencyclopedie.org/wiki/Utilisateur:Thaumasnot also fails:

<DPL>
mode=userformat
categorymatch=Capsule%
namespace=Désinformation
shownamespace=false
minoredits=exclude
createdby=Thaumasnot
ordermethod=firstedit
order=descending
includepage={Suite désinfo}.dpl,accroche,{Suite désinfo}.dpl2
count=15
</DPL>

giving this result:

SELECT DISTINCT rev.rev_timestamp,creation_rev.rev_user,creation_rev.rev_user_text,creation_rev.rev_comment,`page`.page_namespace AS `page_namespace`,`page`.page_id AS `page_id`,`page`.page_title AS `page_title` FROM `revision` `rev`,`revision` `creation_rev`,`page` INNER JOIN `categorylinks` `cl1` ON ((`page`.page_id = cl1.cl_from AND cl1.cl_to LIKE 'Capsule%')) WHERE (`page`.page_id = rev.rev_page) AND (rev.rev_timestamp = (SELECT MIN(rev_aux.rev_timestamp) FROM `revision` AS rev_aux WHERE rev_aux.rev_page=rev.rev_page)) AND `page`.page_is_redirect = '0' AND `page`.page_namespace = '112' AND (rev_minor_edit = 0) AND ('Thaumasnot' = creation_rev.rev_user_text) AND (creation_rev.rev_page = page_id) AND (creation_rev.rev_parent_id = 0) ORDER BY rev.rev_timestamp DESC LIMIT 15

Function: Wikimedia\Rdbms\Database::query
Error: 1052 Column 'rev_minor_edit' in where clause is ambiguous (localhost)

Neither of these are invalid DPL queries; the faulty SQL could be fixed by removing COLLATION 'latin1_swedish_ci' from the SQL in the first query and changing "rev_minor_edit" to "rev.rev_minor_edit" in the second query. That would involve a pair of relatively minor changes to the DPL3 source code - just search the code for these queries and fix them.

While there are other, more serious issues with every version of DPL (Intersection, DPL2, DPL3) such as T124841 the syntactically-flawed SQL queries are low-hanging fruit which should be picked off?

Event Timeline

Carlb created this task.Apr 22 2019, 7:11 PM
Restricted Application added subscribers: Liuxinyu970226, Aklapper. · View Herald TranscriptApr 22 2019, 7:11 PM

This is actually for the gitlab version of the extension, to clarify.

Anyway, @ashley's looking into this, seeing if he can submit the fixes in question, or at least file specific tasks there.

Aklapper updated the task description. (Show Details)Apr 22 2019, 7:32 PM

Opened https://gitlab.com/hydrawiki/extensions/DynamicPageList/merge_requests/95 for the latter issue as it appeared to be a two-line fix.

Reedy closed this task as Declined.Oct 19 2020, 1:26 AM
Reedy added a subscriber: Reedy.