[DERBY-4371] Non-selected columns for SELECT DISTINCT allowed in ORDER BY clause if ordered by expression

• Type: Bug
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 10.5.1.1
• Fix Version/s: 10.7.1.1
• Component/s: SQL
• Issue & fix info: Repro attached
• Bug behavior facts: Wrong query result
• Assignee: Nirmal Fernando
• Reporter: Bernt Johnsen
• Created: 07/Sep/09 11:33
• Updated: 17/Jun/13 09:19
• Resolved: 25/Aug/10 12:11

Description

How to repeat:

ij> create table t (i integer, j integer);;
0 rows inserted/updated/deleted
ij> insert into t values (1,2),(1,3);
2 rows inserted/updated/deleted
ij> select distinct i from t order by j;
ERROR 42879: The ORDER BY clause may not contain column 'J', since the query specifies DISTINCT and that column does not appear in the query result.
ij> select distinct i from t order by j*2;
I
-----------
1
1

2 rows selected

Issue Links

• relates to: DERBY-4406 Wrong order when using ORDER BY on non-deterministic function