Page MenuHomePhabricator

wrong select statement with wildcard for postgreSQL
Open, Needs TriagePublic

Description

Dear Ladies and Gentlemen,

i found a bug in the search in mediawiki for postgresql.

When using a wilcard in the search like "example*" the generated SQL statment is wrong. The genereated SQL statement is:

SELECT page_id, page_namespace, page_title, ts_rank('textvector', to_tsquery('example*'), 2) AS score FROM page p, revision r, pagecontent c WHERE p.page_latest = r.rev_id AND r.rev_text_id = c.old_id AND textvector @@ to_tsquery('example*');

But the part "to_tsquery('example*')" should be "to_tsquery('example:*')".

It think the best solution is to change the behavior of the function "parseQuery" in the file searchPostgres.php. There should be added that a trailing "*" become ":*".

If you need any other information let me know.

Yours,

Erich Lerch

Event Timeline

Erich1978 updated the task description. (Show Details)
Erich1978 raised the priority of this task from to Needs Triage.
Erich1978 added a subscriber: Erich1978.
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptOct 7 2015, 12:49 PM
Aklapper set Security to None.

@Erich1978: Thanks for reporting this and taking a look at the code!

It think the best solution is to change the behavior of the function "parseQuery" in the file searchPostgres.php. There should be added that a trailing "*" become ":*".

You are very 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 the Gerrit Patch Uploader. Thanks again!

Jdforrester-WMF added a subscriber: Jdforrester-WMF.

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

Restricted Application added a project: Discovery-Search. · View Herald TranscriptAug 14 2018, 3:11 AM
debt added a subscriber: debt.

Removing the Discovery-Search tag as we don't use PostGres.

I edited SearchPostgres.php and changed this:

$query = "SELECT page_id, page_namespace, page_title, " .
                               "ts_rank($fulltext, to_tsquery($searchstring), 5) AS score " .
                               "FROM page p, revision r, pagecontent c WHERE p.page_latest = r.rev_id " .
                               "AND r.rev_text_id = c.old_id AND $fulltext @@ to_tsquery($searchstring)";

To

$query = "SELECT page_id, page_namespace, page_title, " .
                               "ts_rank($fulltext, to_tsquery($searchstring||':*'), 5) AS score " .
                               "FROM page p, revision r, pagecontent c WHERE p.page_latest = r.rev_id " .
                               "AND r.rev_text_id = c.old_id AND $fulltext @@ to_tsquery($searchstring||':*')";

The problem is tsquery is full text search but if you files a called files_test_one.png this is 1 word.