[ https://issues.apache.org/jira/browse/DERBY-2351?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12574842#action_12574842 ]
Bryan Pendleton commented on DERBY-2351: ---------------------------------------- In Derby 10.2, all of the following queries work: -- orders the rows by the first column (person.name): select person.name as name, pets.name as pet_name from person, pets order by name; -- orders the rows by the first column (person.name, aliased to 'age'): select person.name as age from person order by person.age; -- again appears to order the rows by the aliased column? select distinct person.name as age from person order by person.age; The first query is only ambiguous if we interpret an unqualified ORDER BY reference to refer to either an exposedName or to an underlying column name. The third query, of course, is inspired by the query that Yip noted as problematic in the comment on 16-Feb-2007. I think that the results from the second query in 10.2 are particularly disturbing, since even though the user specifically said to sort by 'person.age', the 10.2 code apparently sorted by the exposedName column 'age', since if we had really sorted the rows by person.age we'd have produced the result AGE ---------- john mary john So, how bad is it to break these queries? They did not throw errors before, but were they giving the correct results? The complete 10.2 script session is pasted below. ============================================================ ij version 10.2 ij> connect 'jdbc:derby:ten2db;create=true'; ij> create table person (name varchar(10), age int); 0 rows inserted/updated/deleted ij> create table pets (name varchar(10), age int); 0 rows inserted/updated/deleted ij> insert into person values ('john', 30), ('mary', 50), ('john', 10); 3 rows inserted/updated/deleted ij> insert into pets values ('Buster', 1), ('Fido', 3); 2 rows inserted/updated/deleted ij> select person.name as name, pets.name as pet_name from person, pets order by name; NAME |PET_NAME --------------------- john |Fido john |Fido john |Buster john |Buster mary |Fido mary |Buster 6 rows selected ij> select person.name as age from person order by person.age; AGE ---------- john john mary 3 rows selected ij> select distinct person.name as age from person order by person.age; AGE ---------- john mary > ORDER BY with expression with distinct in the select list returns incorrect > result > ---------------------------------------------------------------------------------- > > Key: DERBY-2351 > URL: https://issues.apache.org/jira/browse/DERBY-2351 > Project: Derby > Issue Type: Bug > Components: SQL > Affects Versions: 10.2.1.6, 10.2.2.0, 10.3.1.4 > Environment: Any > Reporter: Yip Ng > Assignee: Bryan Pendleton > Fix For: 10.3.2.2, 10.4.0.0 > > Attachments: d2351_aliasing.diff, d2351_aliasing.diff, > derby_2351.diff, derby_2351_v2.diff, reproTests.diff > > > 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 -- This message is automatically generated by JIRA. - You can reply to this email to add a comment to the issue online.