The default bug view has changed. See this FAQ.

[Oracle] Oracle crashes if a column listed in ORDER BY appears twice in SELECT

RESOLVED FIXED in Bugzilla 4.2

Status

()

Bugzilla
Query/Bug List
--
critical
RESOLVED FIXED
5 years ago
5 years ago

People

(Reporter: Frédéric Buclin, Assigned: Frédéric Buclin)

Tracking

({regression})

4.2.2
Bugzilla 4.2
regression
Bug Flags:
approval +
blocking4.4 +
approval4.2 +
blocking4.2.3 +

Details

Attachments

(1 attachment)

(Assignee)

Description

5 years ago
Bug 753688 comment 8:
> as far as i can tell it should be safe to 'uniq' display_columns here

I'm doing some tests with Oracle right now, and I see that Oracle crashes if the SQL query contains a column in ORDER BY which appears twice in SELECT. So for instance

  "SELECT bug_id, assigned_to, assigned_to FROM bugs ORDER BY assigned_to"

crashes in Oracle (because it complains that the assigned_to column in ORDER BY is ambiguous), but it works correctly in MySQL and PostgreSQL. If I explicitly specify the table name in ORDER BY, i.e. "ORDER BY bugs.assigned_to", then Oracle is happy.

So to fix this problem, we have 2 solutions:
a) remove duplicated columns in _display_columns, but this may confuse the caller which expects some list order. So if the caller wants "bug_id, assigned_to, assigned_to", we would return "bug_id, assigned_to" only, which is unexpected.
b) prefix all columns in ORDER BY with the table they belong to, but how do we get this information? This doesn't seem to be a viable solution, unless someone knows how to do this.
Flags: blocking4.4+
Flags: blocking4.2.3+
(Assignee)

Comment 1

5 years ago
Actually, solution a) doesn't work, because in tabular and graphical reports, you can have the same field twice for the x, y or z axis, and removing duplicated columns from the SQL query means we get only one field back instead of two. I tried, and report.cgi is unable to display any data anymore. Looks like we have to go with b), but I have no idea how to know which table a column belongs to. Any idea?
Assignee: LpSolit → query-and-buglist
Status: ASSIGNED → NEW
(Assignee)

Comment 2

5 years ago
Solution b) won't work either, because some columns are the computation of several columns and so are not real columns and so cannot be prefixed. This leaves us with another solution:
c) if duplicated columns are detected in _display_columns() (or maybe in _input_columns() only), use a different alias for them. But this probably involves a lot of complexity.

/me hates Oracle.
(Assignee)

Comment 3

5 years ago
Created attachment 648586 [details] [diff] [review]
patch, v1

OK, this patch is a good compromise between not altering duplicated columns passed by the caller, removing extra columns which are already present, and avoiding code complexity.

buglist.cgi explicitly excludes duplicated columns, so we are sure this problem can never happen there as this patch now stops blindly adding extra columns even if they are already present. And report.cgi doesn't sort the data, so we are safe there too.
Assignee: query-and-buglist → LpSolit
Status: NEW → ASSIGNED
Attachment #648586 - Flags: review?(glob)
Comment on attachment 648586 [details] [diff] [review]
patch, v1

r=glob
by inspection - i don't have oracle to test
Attachment #648586 - Flags: review?(glob) → review+
Flags: approval?
Flags: approval4.2?
(Assignee)

Updated

5 years ago
Flags: approval?
Flags: approval4.2?
Flags: approval4.2+
Flags: approval+
(Assignee)

Comment 5

5 years ago
Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/trunk/
modified Bugzilla/Search.pm
Committed revision 8328.

Committing to: bzr+ssh://lpsolit%40gmail.com@bzr.mozilla.org/bugzilla/4.2/
modified Bugzilla/Search.pm
Committed revision 8112.
Status: ASSIGNED → RESOLVED
Last Resolved: 5 years ago
Resolution: --- → FIXED
(Assignee)

Updated

5 years ago
Duplicate of this bug: 781549
(Assignee)

Updated

5 years ago
Duplicate of this bug: 783873
You need to log in before you can comment on or make changes to this bug.