Defect #23839

"Invalid query" (Error 500) message with MS SQL when displaying an issue from a list grouped and sorted by fixed version

Added by Olivier Houdas 7 months ago. Updated 7 months ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:Jean-Philippe Lang% Done:

0%

Category:Issues
Target version:3.1.7
Resolution:Fixed Affected version:3.1.3

Description

Steps:
Prerequisite: this happens only with MS SQL
  1. Filter a list of issues by grouping on the fixed version field, and showing the priority field
  2. Click on the Fixed version field to sort on it in descending order, click on the priority field to also sort on that field
    This should result in a query like this one for example
  3. Click on one bug of the list
    Result: Error 500, Invalid query. In the log, it is indicated that there are duplicated column names in an ORDER BY clause
    Note: the issue also happens on MySQL, but MySQL does not consider duplicated sort columns as an error.

The issue happens in the feature to be able to navigate with arrows to the next or previous bug in the list we came from.
As the Fixed_version field implements a call back with its own sortable columns, this comes into the group_by_sort_order statement, which then brings columns duplicated with the ones coming from the options.

Note that with a MySQL-backed Redmine site, the effect is that the order of the list can be incorrect in the previous/Next arrows on the displayed bug.

See attached patch for a suggested fix.

fix_duplicated_order_by_columns.patch Magnifier - proposed code fix (999 Bytes) Olivier Houdas, 2016-09-15 15:01

fix_duplicated_order_by_columns_v2.patch Magnifier (1.19 KB) Olivier Houdas, 2016-09-22 14:23

Associated revisions

Revision 15852
Added by Jean-Philippe Lang 7 months ago

Error with MS SQL when displaying an issue from a list grouped and sorted by fixed version (#23839).

Revision 15863
Added by Jean-Philippe Lang 7 months ago

Test failures.

History

#1 Updated by Toshi MARUYAMA 7 months ago

  • Description updated (diff)

#2 Updated by Toshi MARUYAMA 7 months ago

  • Target version set to 3.1.7

#3 Updated by Olivier Houdas 7 months ago

It's nicer when testing for non-nil object before calling a method on it (map)... sorry for the bugged previous patch.
... and forgot to save after adding missing spaces on last line...

#4 Updated by Jean-Philippe Lang 7 months ago

  • Status changed from New to Resolved
  • Assignee set to Jean-Philippe Lang
  • Resolution set to Fixed

Different fix applied in r15852 with a test. Thanks for pointing this out.

#5 Updated by Jean-Philippe Lang 7 months ago

  • Status changed from Resolved to Closed

Also available in: Atom PDF