View Issue Details

WikiRelated ChangesetsJump to Notes
IDProjectCategoryView StatusDate SubmittedLast Update
0012404mantisbtcustom fieldspublic2010-09-27 09:002014-09-23 18:05
Reportercodeslayer Assigned Todregad  
PrioritynormalSeveritycrashReproducibilityalways
Status closedResolutionfixed 
Target Version1.2.9Fixed in Version1.2.9 
Summary0012404: custom field sort issue on view issues page
Description

When a custom field with name "w2d-defect" (see definition below) is created it can't be used for sorting on the "view issues" page.

An SQL error is being thrown (and remains) when clicking on the title of the column:

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 '-defect FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_fie' at line 1 for the query: SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.last_updated, mantis_bug_table.date_submitted, mantis_custom_field_string_table_2.value w2d-defect FROM mantis_project_table, mantis_bug_table 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 WHERE mantis_project_table.enabled = ? AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id in (1, 2) ) ORDER BY w2d-defect DESC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC.

Steps To Reproduce
  1. Create a custom field name "w2d-defect" (numeric)
  2. Add it as a column-field to the "view issues" page
  3. Click on the title of the custom field's column to select it for sorting
Additional Information

The custom field has the following definition:
Name=w2d-defect
Type=numeric
Possible Values=
Default Value=0
Regular Expression=
Read Access=viewer
Write Access=reporter
Min. Length=0
Max. Length=0
Add to Filter=checked
Display When Reporting Issues=checked
Display When Updating Issues=checked
Display When Resolving Issues=checked
Display When Closing Issues=checked
Required On Report=unchecked
Required On Update=unchecked
Required On Resolve=unchecked
Required On Close=unchecked

TagsNo tags attached.

Relationships

related to 0015721 closedgrangeway Functionality to consider porting to master-2.0.x 
has duplicate 0013541 closedatrol Can't sort results by a custom field containing a '- ' in its name 
has duplicate 0013591 closedatrol Can't sort results by a custom field containing a '- ' in its name 
has duplicate 0013660 closeddregad New Custom Field (Due Date) Sort Error 
has duplicate 0011468 closedatrol can't sort custom field with date type 
has duplicate 0008323 closeddregad Spaces in custom fields 
related to 0012680 closeddregad Custom field name with paranthesis leads to db error message when sorting "view issues" 
related to 0012170 closeddregad SQL syntax error occurs when sorting it by the custom field where special character is included. 

Activities

atrol

atrol

2010-09-27 09:25

developer   ~0026871

This is caused by the "-" in name.
As a workaround change the name
csonnet

csonnet

2010-10-11 03:45

reporter   ~0027005

We have same error with custom fields :

"environnement" Possible values : Production|Test
"traitement"
"produit" Possible values : |H++|H+SCORE|H+PARAM
"responsabilite"
"tache_tm"
csonnet

csonnet

2011-12-18 16:25

reporter   ~0030649

After updating filter_api.php; still have same problem with my custom field traitement.

SELECT DISTINCT mantis_bug_table.*, mantis_custom_field_string_table_6.value custom_field_6 FROM mantis_project_table, mantis_bug_table LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_20 ON mantis_bug_table.id = mantis_custom_field_string_table_20.bug_id AND mantis_custom_field_string_table_20.field_id = 20 LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_6 ON mantis_bug_table.id = mantis_custom_field_string_table_6.bug_id AND mantis_custom_field_string_table_6.field_id = 6 WHERE mantis_project_table.enabled = 1 AND mantis_project_table.id = mantis_bug_table.project_id AND ( mantis_bug_table.project_id in (39, 48, 47, 13, 14, 43, 29, 51, 44, 15, 55, 17, 49, 45, 18, 25, 28, 19, 24, 20, 11, 57, 23, 54, 21, 12, 50, 22, 10, 16, 9, 33, 32, 31, 34, 35, 36, 37, 2, 3, 5, 7, 6, 42, 4, 1, 52, 46, 41, 30, 27, 58, 60, 59) ) AND (mantis_custom_field_string_table_20.value = 'Oui') ORDER BY mantis_bug_table.sticky DESC, custom_field_6 ASC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC

Error near custom_field_6
M.C.S.

M.C.S.

2012-02-03 10:28

reporter   ~0031113

Last edited: 2012-02-03 10:30

I have the same problem (sorting results in 0001064 MySQL error) for this custom field name:

Expected QA date [not later than]

This has been transformed to this (shortened) SQL statement:

SELECT DISTINCT mantis_bug_table.*,
mantis_bug_table.last_updated,
mantis_bug_table.date_submitted,
mantis_custom_field_string_table_29.value expected_qadate[not_later_than]
FROM mantis_project_table, mantis_bug_table
LEFT JOIN mantis_custom_field_string_table mantis_custom_field_string_table_29
ON mantis_bug_table.id = mantis_custom_field_string_table_29.bug_id
AND mantis_custom_field_string_table_29.field_id = 29
WHERE mantis_project_table.id = mantis_bug_table.project_id
ORDER BY expected_qadate[not_later_than] DESC,
mantis_bug_table.last_updated DESC,
mantis_bug_table.date_submitted DESC

This leads to the error message. If i remove the part "_[not_later_than]" on both occurances, the query runs fine.

Is there any specific target release where a possibly existing fix can be tested?
dregad

dregad

2012-02-03 11:40

developer   ~0031115

@M.C.S. 0012404:0031113: Please download a nightly build http://mantisbt.org/builds/

@csonnet 0012404:0030649: Did this occur on a customized Mantis ? If so, can you confirm the same error occurs on the nightly build also ?
grangeway

grangeway

2013-04-05 17:57

reporter   ~0036240

Marking as 'acknowledged' not resolved/closed to track that change gets ported to master-2.0.x branch

Related Changesets

MantisBT: master 170e9397

2011-12-07 04:55

dregad


Details Diff		 Define custom field alias using id instead of name

The old method of defining a custom field alias based on the field's
name, generated SQL syntax error when trying to sort on that field in
the view issues page, if the name contains characters not valid for an
SQL identifier (e.g. '-', '(', ')', etc).

Fixes 0012404, 0012680		 Affected Issues
0012404, 0012680
mod - core/filter_api.php Diff File

MantisBT: master-1.2.x 8fb27ae7

2011-12-07 04:55

dregad


Details Diff		 Define custom field alias using id instead of name

The old method of defining a custom field alias based on the field's
name, generated SQL syntax error when trying to sort on that field in
the view issues page, if the name contains characters not valid for an
SQL identifier (e.g. '-', '(', ')', etc).

Fixes 0012404, 0012680		 Affected Issues
0012404, 0012680
mod - core/filter_api.php Diff File