Skip to content
Permalink

Comparing changes

Choose two branches to see what’s changed or to start a new pull request. If you need to, you can also or learn more about diff comparisons.

Open a pull request

Create a new pull request by comparing changes across two branches. If you need to, you can also . Learn more about diff comparisons here.
base repository: mantisbt/mantisbt
Failed to load repositories. Confirm that selected base ref is valid, then try again.
Loading
base: 4a952233f0fd
Choose a base ref
...
head repository: mantisbt/mantisbt
Failed to load repositories. Confirm that selected head ref is valid, then try again.
Loading
compare: c9bc0646d69b
Choose a head ref
  • 3 commits
  • 1 file changed
  • 1 contributor

Commits on Mar 18, 2013

  1. Revert "filter_api: ensure that the free_text where clauses are alway… 

    …s ANDed"

This reverts commit 543ba01.
    @dregad
    dregad committed Mar 18, 2013

    Verified

    This commit was signed with the committer’s verified signature. The key has expired.
    schneefux
    GPG key ID: 50F2BF336737C2F0
    Expired
    Verified
    Learn about vigilant mode
    Copy the full SHA
    d4e7b22 View commit details

  2. Fix filter api issue with 'any condition' and text search 

    A filter combining some criteria and a text search with 'any condition'
results in a cartesian product, which has the potential to bring down
the site as the RDBMS eats up all available resources.

The root cause of this behavior is joining the bug_text table with a
from clause and setting the join's criteria in the query's where clause,
without taking consideration the operator's precedence (AND/OR).

This commit resolves the problem by using a JOIN clause instead, which
makes the query cleaner.

Fixes #15573
    @dregad
    dregad committed Mar 18, 2013
    1

    Verified

    This commit was created on GitHub.com and signed with GitHub’s verified signature. The key has expired.
    GPG key ID: 4AEE18F83AFDEB23
    Expired
    Verified
    Learn about vigilant mode
    Copy the full SHA
    d16988c View commit details

  3. Filter api: systematic use JOIN when building SQL 

    Do not join tables using the where clause, for better readability and
avoiding risk of issues with operator precedence and 'any condition'
filtering mode.

This commit also removes an unnecessary LEFT JOIN between the bugnote
and bugnote_text tables; since this is a strict 1:1 relationship, an
inner join is sufficient and yields better performance.
    @dregad
    dregad committed Mar 18, 2013
    6
    Copy the full SHA
    c9bc064 View commit details
Showing with 11 additions and 10 deletions.
  1. +11 −10 core/filter_api.php
21 changes: 11 additions & 10 deletions core/filter_api.php
View file
Original file line number Diff line number Diff line change
@@ -1092,7 +1092,6 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p

$t_project_where_clauses = array(
"$t_project_table.enabled = " . db_param(),
"$t_project_table.id = $t_bug_table.project_id",
);
$t_where_params = array(
1,
@@ -1101,8 +1100,13 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p
"$t_bug_table.*",
);

$t_join_clauses = array();
$t_from_clauses = array();
$t_from_clauses = array(
$t_bug_table,
);

$t_join_clauses = array(
"JOIN $t_project_table ON $t_project_table.id = $t_bug_table.project_id",
);

// normalize the project filtering into an array $t_project_ids
if( 'simple' == $t_view_type ) {
@@ -1995,10 +1999,10 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p

# add text query elements to arrays
if ( !$t_first ) {
$t_from_clauses[] = "$t_bug_text_table";
$t_where_clauses[] = "$t_bug_table.bug_text_id = $t_bug_text_table.id AND $t_textsearch_where_clause";
$t_join_clauses[] = " LEFT JOIN $t_bugnote_table ON $t_bug_table.id = $t_bugnote_table.bug_id";
$t_join_clauses[] = " LEFT JOIN $t_bugnote_text_table ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id";
$t_join_clauses[] = "JOIN $t_bug_text_table ON $t_bug_table.bug_text_id = $t_bug_text_table.id";
$t_join_clauses[] = "LEFT JOIN $t_bugnote_table ON $t_bug_table.id = $t_bugnote_table.bug_id";
$t_join_clauses[] = "JOIN $t_bugnote_text_table ON $t_bugnote_table.bugnote_text_id = $t_bugnote_text_table.id";
$t_where_clauses[] = $t_textsearch_where_clause;
}
}

@@ -2012,9 +2016,6 @@ function filter_get_bug_rows( &$p_page_number, &$p_per_page, &$p_page_count, &$p

log_event(LOG_FILTERING, 'Join operator : ' . $t_join_operator);

$t_from_clauses[] = $t_project_table;
$t_from_clauses[] = $t_bug_table;

$t_query_clauses['select'] = $t_select_clauses;
$t_query_clauses['from'] = $t_from_clauses;
$t_query_clauses['join'] = $t_join_clauses;