View Issue Details

IDProjectCategoryView StatusLast Update
0023241mantisbtfilterspublic2017-10-08 23:52
ReportermchendlAssigned Tocproensa 
PrioritynormalSeveritymajorReproducibilityalways
Status closedResolutionfixed 
Product Version2.1.0 
Target Version2.7.0Fixed in Version2.7.0 
Summary0023241: Error when changing sort order in filters, due date field only
Description

application error 0000401 when changing sort_order for due date in "view issues" table header or filter settings

Steps To Reproduce

1) enable "due date" field
2) enable column view for "due date" in "view issues" table
3) click table header "due date" to change the sort order
-- OR ---
3) Change sort order manually in the filter selection area and choose "sort by due date asc" ("desc" works!)

Additional Information

special Situation: Windows Server with IIS and MSSQL (ODBC Driver 13).
complete error message:
APPLICATION ERROR 0000401
Datenbankabfrage fehlgeschlagen. Die Reportmeldung lautet 0000102: SQLState: 42000
Error Code: 102
Message: [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]Incorrect syntax near '='.
für die Abfrage: SELECT DISTINCT mantis_bug_table.*, mantis_bug_table.due_date = 1 FROM mantis_bug_table JOIN mantis_project_table ON mantis_project_table.id = mantis_bug_table.project_id WHERE mantis_project_table.enabled = ? AND ( mantis_bug_table.project_id in (4, 6, 8, 9, 10, 5, 11) ) AND ( ( mantis_bug_table.handler_id=11 ) ) ORDER BY mantis_bug_table.sticky DESC, mantis_bug_table.status ASC, mantis_bug_table.due_date = 1, mantis_bug_table.due_date ASC, mantis_bug_table.last_updated DESC, mantis_bug_table.date_submitted DESC.

TagsNo tags attached.

Relationships

related to 0016259 closeddregad When sorting issues by due_date, unset values should be listed at the end 
related to 0023260 closedcproensa Custom fields of type date are not sorted correctly 

Activities

atrol

atrol

2017-08-18 07:52

developer   ~0057498

Seems to be a IIS and/or MSSQL issue.
I was not able to reproduce it using Apache and MySQL.

mchendl

mchendl

2017-08-23 11:38

reporter   ~0057537

@atrol: any idea to improve the mssql support? It's not my first issue (e.g. no attachment upload into database). As I understand (I'm not a coder) the mssql support is generally weak. It's a technical requirement to use mssql in my current company. The tool is great and fits our requirements perfectly, but no chance for mysql. We are ready to pay for it.

atrol

atrol

2017-08-23 12:09

developer   ~0057538

Last edited: 2017-08-23 12:09

View 2 revisions

It's not my first issue (e.g. no attachment upload into database)

Might be fixed in latest version, see 0022208

As a first step I recommend to upgrade to latest stable version (2.5.1. at the moment), as there are some bug fixes in it, some of them also related to MSSQL, e.g. 0022904.

I did not have a deeper look at your current issue, maybe it's also fixed after the upgrade.

I myself do you not use MSSQL for MantisBT and will not be able to invest any time in MSSQL support.

We are ready to pay for it.

You could search for a developer at one of the freelancer developer portals.
Skills should be: PHP development in general, MSSQL and in best case knowledge of the underlying ADODB database library and Mantis.

All contributions are welcome and greatly appreciated.
The best way for it is to send us a Pull Request on our Github repository [1]
Please make sure that your submissions adhere to our Coding Guidelines [2], if they don't your patch might be rejected.

[1] https://github.com/mantisbt/mantisbt
[2] http://www.mantisbt.org/wiki/doku.php/mantisbt:coding_guidelines

cproensa

cproensa

2017-08-23 16:27

developer   ~0057539

Last edited: 2017-08-23 16:28

View 2 revisions

See: mantis_bug_table.due_date = 1in the ORDER BY section of the query.
I think that's not syntactically correct, and by some reason, mysql does not complain

atrol

atrol

2017-08-23 16:58

developer   ~0057540

See: mantis_bug_table.due_date = 1in the ORDER BY section of the query.

Introduced when fixing 0016259
https://github.com/mantisbt/mantisbt/commit/044133b367234e0e46976dff6d4602b9e8338e72

atrol

atrol

2017-08-23 17:02

developer   ~0057541

@mchendl

Make a copy of file core/filter_api.php and try if removing the following lines (should start in line 1090 if you are using version 2.1.0) from the file fixes your issue.

            if( 'due_date' == $c_sort && 'ASC' == $c_dir ) {
                $t_sort_due_date = $t_sort_col . ' = 1';
                $p_query_clauses['select'][] = $t_sort_due_date;
                $t_sort_col = $t_sort_due_date . ', ' . $t_sort_col;
            }
cproensa

cproensa

2017-08-23 17:50

developer   ~0057542

See: mantis_bug_table.due_date = 1in the ORDER BY section of the query.
I think that's not syntactically correct, and by some reason, mysql does not complain

OK, i wasn't thinking enough.
It's a comparison, in an attempt to sort the "1" values last
However, seems like most databases don't like that syntax (mssql, oracle...)

maybe this is safer, and portable:
SORT BY [...] coalesce( nullif( due_date,1), {maxint} ) ASC
SORT BY [...] coalesce( nullif( due_date,1), 0 ) DESC

to replace the "1" with a suitable integer (reasonable max or min)

cproensa

cproensa

2017-08-23 18:20

developer   ~0057543

@atrol
regarding 0016259, I have opened 0023260 to track this issue for custom fields, but the logic applies the same

Please, review especially the part about what is the expected result, to work on a fix.

cproensa

cproensa

2017-08-24 20:36

developer   ~0057547

This is the change i made:
https://github.com/cproensa/mantisbt/commit/7e075325ff7e1739f5fd21cc1db2f76a8da10049
and has tested fine on mysql, pgsql, mssql ,oracle

It's on a branch i'm working with more changes to filter_api. If you consider the fix is urgent it can be submitted as separate PR. Otherwise it will be bundled with other future filter changes

mchendl

mchendl

2017-08-25 03:07

reporter   ~0057548

Thanks for the quick bugfix. I used the code snippet from @cproensa in my 2.1.0 release code. A separate release would be fine for our upcoming release update, but our IT ops team hasn't named a specific date for that.

cproensa

cproensa

2017-10-08 11:31

developer   ~0057909

@mchendl

I used the code snippet from @cproensa in my 2.1.0 release code

note that the final fix has been implemented slightly different. What you used works ok, but the original approach (with corrected syntax) has been finally used.

Related Changesets

MantisBT: master da11963b

2017-08-23 20:22:26

cproensa


Committer: dregad Details Diff
Fix sorting of due date for undefined values

Previously fixed in 0016259, the order clause still has error with some
databases.
Rewrite the sort expression in a protable syntax.

Fixes: 0023241
mod - core/filter_api.php Diff File

Issue History

Date Modified Username Field Change
2017-08-18 07:25 mchendl New Issue
2017-08-18 07:52 atrol Note Added: 0057498
2017-08-23 11:38 mchendl Note Added: 0057537
2017-08-23 12:09 atrol Note Added: 0057538
2017-08-23 12:09 atrol Note Edited: 0057538 View Revisions
2017-08-23 16:27 cproensa Note Added: 0057539
2017-08-23 16:28 cproensa Note Edited: 0057539 View Revisions
2017-08-23 16:54 atrol Relationship added related to 0016259
2017-08-23 16:58 atrol Note Added: 0057540
2017-08-23 17:02 atrol Note Added: 0057541
2017-08-23 17:50 cproensa Note Added: 0057542
2017-08-23 18:20 cproensa Note Added: 0057543
2017-08-24 03:34 dregad Relationship added related to 0023260
2017-08-24 20:36 cproensa Note Added: 0057547
2017-08-25 03:07 mchendl Note Added: 0057548
2017-08-26 13:13 cproensa Status new => assigned
2017-08-26 13:13 cproensa Assigned To => cproensa
2017-10-07 12:45 dregad Changeset attached => MantisBT master da11963b
2017-10-07 12:45 cproensa Status assigned => resolved
2017-10-07 12:45 cproensa Resolution open => fixed
2017-10-07 12:45 cproensa Fixed in Version => 2.7.0
2017-10-07 13:32 atrol Target Version => 2.7.0
2017-10-08 11:29 cproensa Summary error when changing sort order in filters, due date field only => Error when changing sort order in filters, due date field only
2017-10-08 11:31 cproensa Note Added: 0057909
2017-10-08 23:52 vboctor Status resolved => closed