[DERBY-5005] Error when fully qualifying a field from a view in an ORDER BY clause

• Type: Bug
• Status: Closed
• Priority: Minor
• Resolution: Fixed
• Affects Version/s: 10.0.2.0, 10.0.2.1, 10.1.1.0, 10.1.2.1, 10.1.3.1, 10.2.1.6, 10.2.2.0, 10.3.1.4, 10.3.2.1, 10.3.3.0, 10.4.1.3, 10.4.2.0, 10.5.1.1, 10.5.2.0, 10.5.3.0, 10.6.1.0, 10.6.2.1, 10.7.1.1
• Fix Version/s: 10.5.3.2, 10.6.2.4, 10.7.1.4, 10.8.1.2
• Component/s: SQL
• Labels: order sql syntax views
• Environment: Windows 7
• Urgency: Normal
• Issue & fix info: Repro attached, Workaround attached
• Bug behavior facts: Deviation from standard
• Assignee: Dag H. Wanvik
• Reporter: Lukas Eder
• Created: 05/Feb/11 09:04
• Updated: 14/May/11 22:36
• Resolved: 11/Feb/11 02:08

Description

I have a strange issue that can be reproduced easily with the following objects in schema "test":

create table a (a integer);
insert into a (a) values(1);
create view v as select * from a;

This works:
select test.a.a from test.a where test.a.a <> 2 order by test.a.a asc;

This doesn't work:
select test.v.a from test.v where test.v.a <> 2 order by test.v.a asc;

But this does:
select test.v.a from test.v where test.v.a <> 2 order by v.a asc;

This is the error I get:
Error: 'TEST.V' is not an exposed table name in the scope in which it appears.
SQLState: 42X10
ErrorCode: -1

I've tried quite a few SELECT clauses, and I think the ORDER BY clause is the only one having this issue.