View Issue Details

IDProjectCategoryView StatusLast Update
0019670mantisbtfilterspublic2015-09-06 17:37
ReportervboctorAssigned Tovboctor 
PrioritynormalSeveritymajorReproducibilityhave not tried
Status closedResolutionfixed 
Product Version1.3.0-beta.2 
Target Version1.3.0-beta.3Fixed in Version1.3.0-beta.3 
Summary0019670: View Issues page fails when a custom field is used for filtering and sorting
Description

If the same custom field is used for filtering to a value that is not "any" and is also used for sorting, the generated query defines the same table alias twice causing a SQL error. The fix is to use different aliasing prefix for filtering vs. sorting.

This generates invalid queries like ones quoted in 0019667

Tagsmantishub

Relationships

related to 0019667 closedatrol Application Error 0000401 When Clicking View Issues Menu 
has duplicate 0020043 closedatrol APPLICATION ERROR 0000401 Db query failed. Error received from database was 0001063 Not unique table/alias 

Activities

vboctor

vboctor

2015-04-27 23:05

manager   ~0050642

Reminder sent to: atrol, dregad

Interesting bug for 1.3 relating to filter API creating invalid queries.

vboctor

vboctor

2015-04-27 23:34

manager   ~0050643

Error message:

APPLICATION ERROR # 401
Database query failed. Error received from database was #1066: Not unique table/alias: 'mantis_custom_field_string_table' for the query: ..."

Here is a sample broken query:

SELECT Count( DISTINCT mantis_bug_table.id ) as idcnt FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_12 ON mantis_bug_table.id = mantis_custom_field_string_table_12.bug_id AND mantis_custom_field_string_table_12.field_id = 12 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_5 ON mantis_bug_table.id = mantis_custom_field_string_table_5.bug_id AND mantis_custom_field_string_table_5.field_id = 5 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_3 ON mantis_bug_table.id = mantis_custom_field_string_table_3.bug_id AND mantis_custom_field_string_table_3.field_id = 3 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_2 ON mantis_bug_table.id = mantis_custom_field_string_table_2.bug_id AND mantis_custom_field_string_table_2.field_id = 2 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_13 ON mantis_bug_table.id = mantis_custom_field_string_table_13.bug_id AND mantis_custom_field_string_table_13.field_id = 13 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_14 ON
mantis_bug_table.id = mantis_custom_field_string_table_14.bug_id AND mantis_custom_field_string_table_14.field_id = 14 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_5 ON
mantis_bug_table.id = mantis_custom_field_string_table_5.bug_id AND mantis_custom_field_string_table_5.field_id = 5 WHERE mantis_project_table.enabled = ? AND ( mantis_bug_table.project_id = 22 ) AND ( ( mantis_bug_table.status in (?, ?, ?, ?, ?, ?) ) AND (mantis_custom_field_string_table_12.value = ?) AND ((mantis_custom_field_string_table_5.value LIKE ?)) AND (mantis_custom_field_string_table_3.value = ?) AND (mantis_custom_field_string_table_2.value = ?) AND (mantis_custom_field_string_table_13.value = ?) )

vboctor

vboctor

2015-04-28 00:04

manager   ~0050644

The tablename_id is used for aliasing, such table names are not expected.

I suspect that mantis_custom_field_string_table_5 is the cause of the issue, since it is referenced twice.

vboctor

vboctor

2015-04-28 00:10

manager   ~0050645

I've revised the issue title and description to reflect the root cause. I'll submit a pull request shortly.

vboctor

vboctor

2015-04-28 00:13

manager   ~0050646

Here is the pull request:
https://github.com/mantisbt/mantisbt/pull/602

Related Changesets

MantisBT: master 53ee0c74

2015-04-28 00:12:26

vboctor

Details Diff
Fix SQL error when custom field is in filter/sort

If the same custom field is used for filtering to a value that is not "any" and is also used for sorting,
the generated query defines the same table alias twice causing a SQL error.
The fix is to use different aliasing prefix for filtering vs. sorting.

Fixes 0019670
mod - core/filter_api.php Diff File

Issue History

Date Modified Username Field Change
2015-04-27 23:01 vboctor New Issue
2015-04-27 23:01 vboctor Relationship added related to 0019667
2015-04-27 23:05 vboctor Note Added: 0050642
2015-04-27 23:05 vboctor Severity minor => major
2015-04-27 23:34 vboctor Note Added: 0050643
2015-04-28 00:04 vboctor Note Added: 0050644
2015-04-28 00:10 vboctor Assigned To => vboctor
2015-04-28 00:10 vboctor Status new => assigned
2015-04-28 00:10 vboctor Summary Filter API appends custom field id to custom field table name => View Issues page fails when a custom field is used for filtering and sorting
2015-04-28 00:10 vboctor Description Updated View Revisions
2015-04-28 00:10 vboctor Note Added: 0050645
2015-04-28 00:13 vboctor Note Added: 0050646
2015-04-28 02:51 vboctor Tag Attached: mantishub
2015-04-28 10:42 vboctor Changeset attached => MantisBT master 53ee0c74
2015-04-28 10:42 vboctor Status assigned => resolved
2015-04-28 10:42 vboctor Resolution open => fixed
2015-04-28 10:42 vboctor Fixed in Version => 1.3.0-beta.3
2015-08-19 07:03 atrol Relationship added has duplicate 0020043
2015-09-06 17:37 vboctoradmin Status resolved => closed