Last modified: 2014-11-13 12:24:06 UTC

Wikimedia Bugzilla is closed!

Wikimedia has migrated from Bugzilla to Phabricator. Bug reports should be created and updated in Wikimedia Phabricator instead. Please create an account in Phabricator and add your Bugzilla email address to it.
Wikimedia Bugzilla is read-only. If you try to edit or create any bug report in Bugzilla you will be shown an intentional error message.
In order to access the Phabricator task corresponding to a Bugzilla report, just remove "static-" from its URL.
You could still run searches in Bugzilla or access your list of votes but bug reports will obviously not be up-to-date in Bugzilla.
Bug 67065 - SQL casting error in Special:ProtectedPages
SQL casting error in Special:ProtectedPages
Product: MediaWiki
Classification: Unclassified
Database (Other open bugs)
All All
: Normal normal (vote)
: ---
Assigned To: Greg Sabino Mullane
: patch, patch-need-review
Depends on:
Blocks: postgres
  Show dependency treegraph
Reported: 2014-06-25 02:03 UTC by Greg Sabino Mullane
Modified: 2014-11-13 12:24 UTC (History)
4 users (show)

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

Fix for SQL casting error with Postgres (522 bytes, patch)
2014-06-25 02:03 UTC, Greg Sabino Mullane

Description Greg Sabino Mullane 2014-06-25 02:03:52 UTC
Created attachment 15728 [details]
Fix for SQL casting error with Postgres

In Special:ProtectedPages, a text field is compared directly to an integer, which moden versions of Postgres do not like. Error seen:

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 > '2014-06-25 01:59:54 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.

Yes, Postgres is being pedantic, but it is weird that the where clause has a proper cast right before it:

ON (ls_field = 'pr_id' AND (ls_value = pr_id))

The solution is to quote the pr_id. Diff attached.
Comment 1 Andre Klapper 2014-06-25 09:11:12 UTC
Hi! Thanks for your patch!

You are welcome to use Developer access
to submit this as a Git branch directly into Gerrit:
Putting your branch in Git makes it easier to review it quickly. If you don't want to set up Git/Gerrit, you can also use
Thanks again!
Comment 2 Umherirrender 2014-06-25 18:20:09 UTC
Table 'log_search' is a table to match a row of the logging table and corresponding data. The ls_field is used to determine between all the given values.

In this case the value of column ls_value (varchar(255)) is compared to the value of column pr_id (integer) which is needed here to find all the log_ids for protected pages.
Postgres seems to have problems with different data types. So mediawiki needs a new column ls_value_int or a explicit database cast. Quoting is not a option here, because that will change the meaning of the query (ls_value never contains the word 'pr_id').

Before the use of pr_id the log_search table was only used to find user names, so there was no cast problem.
Comment 3 Greg Sabino Mullane 2014-06-25 19:42:19 UTC
Oh, right, sorry about that. Looks like a sticky problem then. Maybe we can use CAST() as both systems seem to support it?
Comment 4 Greg Sabino Mullane 2014-10-04 18:38:01 UTC
Except MySQL does not support casting to VARCHAR. Sigh.
Comment 5 Greg Sabino Mullane 2014-10-05 12:03:23 UTC
Made a gerrit patch here:

As the comments say, this is a tricky problem, with no immediate general solution I could see. The only commonality seems to be Postgres and Sqlite could both use something like CAST(pr_id AS TEXT). Oracle needs VARCHAR2. MySQL cannot cast to VARCHAR. I can envision some other workarounds, but for the moment, a direct exception for Postgres seems the best answer. Other opinions welcome.
Comment 6 Andre Klapper 2014-10-05 20:07:15 UTC
(In reply to Greg Sabino Mullane from comment #5)
> Made a gerrit patch here

Thanks! Following is welcome which triggers automatic notifications in Bugzilla.
Comment 7 Gerrit Notification Bot 2014-11-13 12:24:06 UTC
Change 164765 had a related patch set uploaded by Nemo bis:
Force cast the pr_id column (an int) to text

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