0007849: filters on custom date field causes SQL error - MantisBT - Signup temporarily disabled due to spam

ID	Project	Category	View Status	Date Submitted	Last Update

0007849	mantisbt	custom fields	public	2007-03-22 07:35	2013-11-11 10:16

Reporter	renfrowl	Assigned To	giallu
Priority	normal	Severity	minor	Reproducibility	always
Status	closed	Resolution	fixed
Product Version	1.1.0a2
Target Version	1.1.0	Fixed in Version	1.1.0a4

Summary	0007849: filters on custom date field causes SQL error
Description	I have a custom field called 'Requested Review Date', and it's set to a Date type. If i goto the View Issues page and attempt to do a query on a specific date i am thrown back with a SQL error. I'm currently running mysql-client-5.1.16 Multithreaded SQL database (client) mysql-server-5.1.11 Multithreaded SQL database (server) Database query failed. Error received from database was 0001064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 4 for the query: SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_12 ON mantis_custom_field_string_table_12.bug_id = mantis_bug_table.id AND mantis_custom_field_string_table_12.field_id = 12 WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id = 22 ) AND ( mantis_bug_table.status in (10, 20, 30, 40, 50) ) AND (( mantis_custom_field_string_table_12.value+0) BETWEEN 1174536000 AND 1174622398))
Tags	No tags attached.
Attached Files	bug7849-1.patch (1,177 bytes) Index: core/filter_api.php =================================================================== RCS file: /cvsroot/mantisbt/mantisbt/core/filter_api.php,v retrieving revision 1.152 diff -u -r1.152 filter_api.php --- core/filter_api.php 18 Apr 2007 06:35:01 -0000 1.152 +++ core/filter_api.php 18 Apr 2007 23:02:04 -0000 @@ -1049,11 +1049,11 @@ break ; case CUSTOM_FIELD_DATE_AFTER: array_push( $t_join_clauses, $t_cf_join_clause ); - $t_custom_where_clause = '(( ' . $t_table_name . '.value+0) > ' . ($t_filter['custom_fields'][$t_cfid][1]+1) . ')' ; + $t_custom_where_clause = '( (' . $t_table_name . '.value+0) > ' . ($t_filter['custom_fields'][$t_cfid][1]+1) ; break ; default: array_push( $t_join_clauses, $t_cf_join_clause ); - $t_custom_where_clause = '(( ' . $t_table_name . '.value+0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2] . ')' ; + $t_custom_where_clause = '( (' . $t_table_name . '.value+0) BETWEEN ' . $t_filter['custom_fields'][$t_cfid][1] . ' AND ' . $t_filter['custom_fields'][$t_cfid][2]; break ; } } else { bug7849-1.patch (1,177 bytes)

thorin 2007-04-18 08:16 reporter ~0014361	I have exactly the same problem.

giallu 2007-04-18 10:50 reporter ~0014362	does any of the other filters type on date works? For example, can you filter bugs with the custom field set before or after a certain date?

renfrowl 2007-04-18 10:59 reporter ~0014363	Any date filter type causes this error. By selecting On, Between, On or before, pretty much anything other than Any or None will produce this error. The error does not appear if you are using the standard date filters, but always appears if you use a date filter on a custom field.

giallu 2007-04-18 11:32 reporter ~0014364	Assuming you are using CVS HEAD, could you open core/filter_api.php and remove the last: . ')' from lines 1048, 1052 and 1056 then verify if the three filters works as expected?

renfrowl 2007-04-18 12:09 reporter ~0014365	That did the trick! thanks very much

giallu 2007-04-18 17:58 reporter ~0014366	Ok. So it seems we are composing a query with unbalanced parentheses; the strange thing is that I could not track down with confidence how/when the problem was generated. I found the addition of a closing ")" in revision 1.128, but that was from 10 Dec 2005, so if the problem was not somewhere else, this feature is broken since more than a year... anyway, I am going to attach a (trivial) patch

thorin 2007-04-19 09:48 reporter ~0014368	After removing the three ')' I god this. I have two custom date fields and I tried to apply before filter to the first and after filter to the second. APPLICATION ERROR 0000401 Database query failed. Error received from database was 0001064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4 for the query: SELECT DISTINCT mantis_bug_table.id AS id FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_6 ON mantis_custom_field_string_table_6.bug_id = mantis_bug_table.id AND mantis_custom_field_string_table_6.field_id = 6 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_1 ON mantis_custom_field_string_table_1.bug_id = mantis_bug_table.id AND mantis_custom_field_string_table_1.field_id = 1 WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id=2 ) AND ( mantis_bug_table.status in (10, 20, 30, 40, 50, 80) ) AND (( mantis_custom_field_string_table_6.value != 0 AND (mantis_custom_field_string_table_6.value+0) < 1176930000) AND (( mantis_custom_field_string_table_1.value+0) > 1176065999)

giallu 2007-04-19 09:56 reporter ~0014369	thorin. Do not remove the first of the ')' (it was an overlook on my side) BTW the submitted patch is correct

thorin 2007-04-20 01:35 reporter ~0014375	Awesome! Thank you.

MantisBT: master 1faaa8b4 2007-05-27 18:31 giallu Details Diff	Fix 7849: filters on custom date field causes SQL error git-svn-id: http://mantisbt.svn.sourceforge.net/svnroot/mantisbt/trunk@4373 <a class="text" href="/?p=mantisbt.git;a=object;h=f5dc347c">f5dc347c</a>-c33d-0410-90a0-b07cc1902cb9		Affected Issues 0007849
	mod - core/filter_api.php		Diff File