|
If you were logged in you would be able to see more operations.
|
|
|
|
File Attachments:
|
|
|
Environment:
|
Any
|
|
Issue Links:
|
Reference
|
|
This issue relates to:
|
|
DERBY-3373
SQL "distinct" and "order by" needed together
|
|
|
|
|
DERBY-2085
Misleading error message for non-matching ORDER BY clause in queries with GROUP BY.
|
|
|
|
|
|
This issue 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.
|
|
|
|
|
|
|
| Issue & fix info: |
Release Note Needed
|
| Resolution Date: |
16/Mar/08 03:51 AM
|
|
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
|
|
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
|
Show » |
| Repository |
Revision |
Date |
User |
Message |
| ASF |
#636608 |
Thu Mar 13 03:01:14 UTC 2008 |
bpendleton |
DERBY-2351: ORDER BY with expression with distinct in SELECT list.
This is a follow-on patch for DERBY-2351, which addresses a number of
problems involving column aliasing that occurred with the initial
DERBY-2351 patch. After the initial DERBY-2351 patch, queries such as
the following one failed:
select distinct c1 as a1 from t order by c1
This patch modifies the ORDER BY column matching algorithm to
match column references using multiple rules, depending on
whether or not the ORDER BY reference is qualified or not:
- unqualified references are matched first against the alias name,
then against the underlying column name from the source table
- qualified references are matched only against the underlying
specified table
A number of additional test cases are added as part of this patch to verify
that the above query, and other similar queries, now work as intended.
This patch rejects at least one query known to have worked before:
select t1.id as idc1, t1.id as idc2 from t1 order by t1.idc1, t1.idc2;
After discussion with Derby developers, this query was felt to
be invalid, as there is no such column "idc1" in table "t1". The query
should *not* qualify the ORDER BY column references with table "t1":
select t1.id as idc1, t1.id as idc2 from t1 order by idc1, idc2;
|
| Files Changed |
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out
MODIFY
/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumn.java
MODIFY
/db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/ResultColumnList.java
MODIFY
/db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/synonym.out
|
|
Bug
Major
