Yes. The same db column will appear twice in the query.

This can be solved with alias as tablenames:

In core\filter_api.php

Old Code:

if sorting by a custom field

            if ( strpos( $c_sort, 'custom_' ) === 0 ) {
                $t_custom_field = substr( $c_sort, strlen( 'custom_' ) );
                $t_custom_field_id = custom_field_get_id_from_name( $t_custom_field );
                $t_join .= " LEFT JOIN $t_custom_field_string_table ON ( ( $t_custom_field_string_table.bug_id = $t_bug_table.id ) AND ( $t_custom_field_string_table.field_id = $t_custom_field_id ) )";
                $c_sort = "$t_custom_field_string_table.value";
                $t_select_clauses[] = "$t_custom_field_string_table.value";
            }

New Code:

if sorting by a custom field

            if ( strpos( $c_sort, 'custom_' ) === 0 ) {
                $t_custom_field = substr( $c_sort, strlen( 'custom_' ) );
                $t_custom_field_id = custom_field_get_id_from_name( $t_custom_field );
                $t_join .= " LEFT JOIN $t_custom_field_string_table $t_custom_field_string_table$i ON ( ( $t_custom_field_string_table$i.bug_id = $t_bug_table.id ) AND ( $t_custom_field_string_table$i.field_id = $t_custom_field_id ) )";
                $c_sort = "$t_custom_field_string_table$i.value";
                $t_select_clauses[] = "$t_custom_field_string_table$i.value";
            }

Perhaps could you implement this in the next release.

Regards

this still occurs in 1.2.0rc2 and causes the above error. However, cause the filter is cached, even if the user hits the back button, they can't recover from this, resulting in the inability to perform searches.

unfortunately the way the query is constructing in 1.2.0rc2 is different from what was posted by qhweb, so a different patch woudl need to be made.

To get around this seemingly unrecoverable error:
one workaround is to delete your mantis cookie.
another method is to change to a different project that doesn:t have that custom field and hit reset filter.

ghweb code works for me. There was an error happends on ma side while changing the code.

Thank you ghweb

Multiple LEFT JOIN's between the same tables sounds very inefficient to me. I think we should be using multiple WHERE clauses instead?

We just installed Mantis 1.2.1 and it seems to be fixed.

BUT: Now we get these SQL-errors when sorting for custom fields containing "special" characters:

• Fixed in Version (>=)
• Effort [d]
  As soon as we removed the "(>=)" or the "[d]" the sorting worked.

0011139 is another duplicate of this issue.

My patch for this problem (1.2.0 - 1.2.2) is to prevent multiple left joins:

--- mantis-1.2.0/core/filter_api.php 2010-02-24 10:34:19.642476000 +0100
+++ mantis/core/filter_api.php 2010-10-05 09:55:54.372631700 +0200
@@ -924,7 +924,13 @@

check to be sure this field wasn't already added to the query.

            if( !in_array( $t_cf_select, $p_query_clauses['select'] ) ) {
                $p_query_clauses['select'][] = $t_cf_select;

• $p_query_clauses['join'][] = "LEFT JOIN $t_custom_field_string_table $t_cf_table_alias ON $t_bug_table.id = $t_cf_table_alias.bug_id AND $t_cf_table_alias.field_id = $t_custom_field_id";

• be sure to add join clause only if it isn't already there

• WARNING: MUST be the same as in filter_get_bug_rows(), section custom field filters.

• if you change here, also change there

• $t_cf_join_clause = "LEFT JOIN $t_custom_field_string_table $t_cf_table_alias ON $t_cf_table_alias.bug_id = $t_bug_table.id AND $t_cf_table_alias.field_id = $t_custom_field_id ";
• if( !in_array( $t_cf_join_clause, $p_query_clauses['join'] ) ) {
• $p_query_clauses['join'][] = $t_cf_join_clause;

• }
  }

          $p_query_clauses['order'][] = $c_cf_alias . ' ' . $c_dir;

  @@ -1857,6 +1863,9 @@

  Each custom field will result in a exponential growth like Number_of_Issues^Number_of_Custom_Fields

          # and only after this process ends (if it is able to) the result query will be filtered
          # by the WHERE clause and by the DISTINCT clause

• WARNING: join clause MUST be the same as in filter_get_query_sort_data()

• if you change here, also change there

          $t_cf_join_clause = "LEFT JOIN $t_custom_field_string_table $t_table_name ON $t_table_name.bug_id = $t_bug_table.id AND $t_table_name.field_id = $t_cfid ";
  
          if( $t_def['type'] == CUSTOM_FIELD_TYPE_DATE ) {

It may be better to use the table name of the joined table as a key (i. e. $p_query_clauses['join'][$t_cf_table_alias] instead of just $p_query_clauses['join'][]), but this would mean to rewrite some code in filter_get_bug_rows() (don't use array_push()).

This patch doesn't address the "special character" problem.

filter api already handles duplicate selects, joins, and from clauses. The problem here was that one of the custom field joins was reversed and had extra whitespace. See commit below. The special character problem still exists as far as I know.

Ok I determined that placing backtics around the column name allows special chars in the field name...at least for mysql. I've attached a patch for this and will commit this if I can get some testers for other databases to verify that it works and doesn't break anything else. The patch is against latest master but should work just fine for master-1.2.x as well.

The original patch doesn't work for 1.2 so i uploaded an additional patch for the 1.2.x branch.

Backticks only work with MySQL, not with any other database managemant system I know. Why don't you just eliminate or replace special chars with preg_replace() or something like that before using it? (pregreplace('/^[a-zA-Z0-9]/', '_', $table_name))

This just fixes the duplicate alias problem not the special character problem
listed in the bug.

http://www.mantisbt.org/bugs/view.php?id=12691#c28509
shows a tweak to solve problem by removing a cookie named "MANTIS_VIEW_ALL_COOKIE"

Why it is so?

Guys, this is an old issue and I can't reproduce it. I guess it was already fixed.