[DERBY-2351] ORDER BY with expression with distinct in the select list returns incorrect result

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 10.2.1.6, 10.2.2.0, 10.3.1.4
• Fix Version/s: 10.3.3.0, 10.4.1.3, 10.5.1.1
• Component/s: SQL
• Environment: Any
• Issue & fix info: Release Note Needed
• Assignee: Bryan Pendleton
• Reporter: Yip Ng
• Created: 17/Feb/07 00:36
• Updated: 21/Jan/11 17:49
• Resolved: 16/Mar/08 03:51

Description

When distinct is in the select list and the query has order by with expression, the resultset produced contains an additional column.

ij> create table t1 (c1 int, c2 varchar(10))
0 rows inserted/updated/deleted
ij> insert into t1 values (1,'a'),(2,'b'),(3,'c');
3 rows inserted/updated/deleted
select distinct c1, c2 from t1 order by c1;
C1 |C2
----------------------
1 |a
2 |b
3 |c

3 rows selected
ij> select distinct c1, c2 from t1 order by c1+1;
C1 |C2 |3 <=====returns 3 columns, incorrect result returned
----------------------------------
1 |a |2
2 |b |3
3 |c |4

3 rows selected

Issue Links

• is related to: DERBY-2805 ASSERT failure with sane build if DISTINCT and ORDER BY are present in a query that selects from a diagnostic table function.
• relates to: DERBY-3373 SQL "distinct" and "order by" needed together
• relates to: DERBY-2085 Misleading error message for non-matching ORDER BY clause in queries with GROUP BY.