0011764: View Issues gives "ORDER BY id DESC" error - MantisBT

ID	Project	Category	View Status	Date Submitted	Last Update
0011764	mantisbt	db mssql	public	2010-04-07 17:07	2014-05-16 15:00

Reporter	makafre	Assigned To	dhx
Priority	normal	Severity	block	Reproducibility	always
Status	closed	Resolution	fixed
Product Version	1.2.0
Target Version	1.2.2	Fixed in Version	1.2.2

Summary	0011764: View Issues gives "ORDER BY id DESC" error
Description	This should be a really simple one to correct for somebody that knows MantisBT. I keep getting this error when I click on View Issues: Database query failed. Error received from database was 0000209: Ambiguous column name 'id'. for the query: SELECT DISTINCT mantis_bug_table., mantis_bug_table.last_updated, mantis_bug_table.date_submitted FROM mantis_project_table, mantis_bug_table WHERE mantis_project_table.enabled = ? AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id = 1 ) ORDER BY id DESC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC. ..but this only happens whenever Preferences->Default Project is not* set to "All Projects". When All Projects is selected, the page shows OK. As suggested by czema, the "order by" clause should be: mantis_bug_table.id DESC Anyone can help with this? it would be really appreciated.
Tags	No tags attached.

makafre 2010-04-08 09:06 reporter ~0025071	I also just noticed that this happens only when Administrator is used.

gonfva 2010-04-27 13:56 reporter ~0025307	I just hit what seems to be this issue. I got a blank page when trying to filter for date_submitted for a group of projects. I just solved it doing something like if ( 'date_submitted' == $c_sort ) { $c_sort = "$t_bug_table.date_submitted"; } in line 954 of filter_api.php. But now I've discovered that the bug is when filtering by ID. Maybe what should be changed is the line $p_query_clauses['order'][] = $c_sort . ' ' . $c_dir; by the line $p_query_clauses['order'][] = $t_bug_table . '.' . $c_sort . ' ' . $c_dir; But then it would crash when trying to filter by a field not in $t_bug_table. I'm not a true PHP developer. Maybe someone can improve the fix.

dhx 2010-04-29 01:47 reporter ~0025346	Thanks for all the help debugging this issue. I've pushed a patch which should hopefully resolve this problem. Please reopen this issue if my patch didn't quite work for you.

grangeway 2014-05-16 15:00 reporter ~0040389	MantisBT currently supports Mysql and has support for other database engines. The support for other databases is known to be problematic. Having implemented the current database layer into Mantis 10 years ago, I'm currently working on replacing the current layer. If you are interested in using Mantis with non-mysql databases - for example, Oracle, PGSQL or MSSQL, and would be willing to help out testing the new database layer, please drop me an email at paul@mantisforge.org In the meantime, I'd advise running Mantis with Mysql Only to avoid issues. Thanks Paul

MantisBT: master 8be869af 2010-04-29 01:44 dhx Details Diff	Fix 0011764: Avoid collision of column names when ordering fields When filtering issues, the ORDER BY clause doesn't distuingish between custom fields and built-in fields. Thus it is possible for a custom field named "id" to collide with the built-in bug "id" field when creating the ORDER BY clause.		Affected Issues 0011764
	mod - core/filter_api.php		Diff File
MantisBT: master-1.2.x 3b1a5b87 2010-04-29 01:44 dhx Details Diff	Fix 0011764: Avoid collision of column names when ordering fields When filtering issues, the ORDER BY clause doesn't distuingish between custom fields and built-in fields. Thus it is possible for a custom field named "id" to collide with the built-in bug "id" field when creating the ORDER BY clause.		Affected Issues 0011764
	mod - core/filter_api.php		Diff File