Page MenuHomePhabricator

Database error on Special:Protected_Pages when using PostgreSQL
Closed, DuplicatePublic

Description

Using MW 1.23.13, PHP 5.6.20-0 and PostgreSQL 9.4.6 you get an error when trying to access Special:Protected_pages

A database query error has occurred. This may indicate a bug in the software.
Query:

SELECT
pr_id,page_namespace,page_title,page_len,pr_type,pr_level,pr_expiry,pr_cascade,
log_timestamp,log_user,log_comment,log_deleted
FROM "page","page_restrictions"
LEFT JOIN "log_search" ON (ls_field = 'pr_id' AND (ls_value = pr_id))
LEFT JOIN "logging" ON ((ls_log_id = log_id)) 
WHERE (pr_expiry > '2016-05-13 09:54:51 GMT'OR pr_expiry IS NULL) 
AND (page_id=pr_page) AND (pr_type='edit') ORDER BY pr_id LIMIT 51

Function: IndexPager::buildQueryInfo (ProtectedPagesPager)
Error: 42883 ERROR: operator does not exist: text = integer LINE 1: ..."log_search" ON (ls_field = 'pr_id' AND (ls_value = pr_id)) ...
^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

Reason is, ls_field and ls_value are text but pr_id is integer.

Error lies in SpecialProtectedpages.php around line 545. Tried to fix this myself, but my php knowledge isn't good enough.

Related Objects

View Standalone Graph
This task is connected to more than 200 other tasks. Only direct parents and subtasks are shown here. Use View Standalone Graph to show more of the graph.

Event Timeline

Restricted Application added subscribers: Zppix, Aklapper. · View Herald TranscriptMay 13 2016, 10:06 AM

If you change line 545 in /includes/SpecialProtectedpages.php from

'ls_field' => 'pr_id', 'ls_value = pr_id'

to

'ls_field' => 'pr_id', 'CAST(ls_value AS INT) = pr_id'

everything's working.

But I'm not sure, if this is really the best idea. And I can't test with other databases than PostgreSQL.

Change 288745 had a related patch set uploaded (by Jjanes):
PostgreSQL: Allow comparison of integers to strings.

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

Duplicate of T69065, but not sure what best direction to link, because both have patch set in gerrit

Jjanes added a subscriber: Jjanes.May 14 2016, 8:28 PM

I think my approach is more modular (it doesn't embed the database type into the general code, but rather calls out to inherited methods) and it also comes closer to preserving the semantics of MySQL comparison. For example, in MySQL,

5 = ' 5 apples'

is true, while in PostgreSQL

5::text = ' 5 apples'

is false. I don't think those differences are important in this particular case, but we should make a general solution be general.

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

Migrating from the old tracking task to a tag for PostgreSQL-related tasks.