db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dyre Tjeldvoll (JIRA)" <j...@apache.org>
Subject [jira] Updated: (DERBY-3373) SQL "distinct" and "order by" needed together
Date Thu, 06 Mar 2008 15:15:02 GMT

     [ https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Dyre Tjeldvoll updated DERBY-3373:
----------------------------------

    Derby Info: [Regression]

I'm marking this as a regression since it worked before the following fix was checked in:
 
------------------------------------------------------------------------
r555096 | bpendleton | 2007-07-11 00:06:02 +0200 (Wed, 11 Jul 2007) | 19 lines

DERBY-2351: Certain ORDER BY clauses should be rejected as invalid

This change modifies the ORDER BY clause so that it rejects certain
queries as invalid: specifically, queries which:
a) specify the set quantifier DISTINCT,
b) and also contain an ORDER BY clause which refers to a column
or expression which is not in the query result.

The problem with such queries is that we are told to return only
a single instance of the DISTINCT columns, but since the ORDER BY
clause refers to columns which are not in the DISTINCT set, if there
should be multiple candidate rows from which we choose the DISTINCT
result, we don't know which of those rows to use for the ORDER BY
processing.

When the DISTINCT and ORDER BY clauses are in conflict, Derby should
reject the query. This change modifies Derby to do so.


------------------------------------------------------------------------


> SQL "distinct" and "order by" needed together
> ---------------------------------------------
>
>                 Key: DERBY-3373
>                 URL: https://issues.apache.org/jira/browse/DERBY-3373
>             Project: Derby
>          Issue Type: Improvement
>          Components: SQL
>    Affects Versions: 10.3.2.1
>         Environment: Solaris Dev Express, Java 5
>            Reporter: Thomas Vatter
>            Priority: Blocker
>             Fix For: 10.3.2.2
>
>
> I am pasting here the communication from the mailinglist. I am having a blocking and
large problem with it because I have to make a release that needs the specified SQL query.

> tom_ wrote:
> > The errormessage is 
> > 
> > The ORDER BY clause may not specify an expression, since the query specifies 
> > DISTINCT 
> > [Error Code: 20000] 
> > [SQL State: 4287A] 
> > 
> > The statement is 
> > 
> > select distinct 
> > t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3 
> > from 
> > t1, t2, t3   
> > where 
> > ... 
> > order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3) 
> > 
> > 
> > 
> > 
> > Dyre.Tjeldvoll wrote: 
> >   
> >> tom_ <tom12167@...> writes: 
> >> 
> >>     
> >>> I am using "disctinct" because of some self-joins and also needed to add

> >>> an 
> >>> "order by" clause. An error is shown. Is it not possible to use distinct

> >>> and 
> >>> order by together? 
> >>>       
> >> I think it is allowed. Executing 
> >> 
> >> select distinct * from sys.systables order by tablename; 
> >> 
> >> in ij works just fine. Could you show the error message you get, and 
> >> perhaps what the table looks like? 
> >> 
> >> -- 
> >> dt 
> >> 
> >> 
> >>    
> «  [hide part of quote]
> Hi Tom - 
> I see what you mean using the demo DB toursDB: 
> ij> select * from airlines order by lower(airline_full); 
> A&|AIRLINE_FULL            |BASIC_RATE            |DISTANCE_DISCOUNT     
> |BUSINESS_LEVEL_FACTOR 
> |FIRSTCLASS_LEVEL_FACT&|ECONOMY_SE&|BUSINESS_S&|FIRSTCLASS& 
> -----------------------------------------------------------------------------------------------------------------------------------------------------------

> AA|Amazonian Airways       |0.18                  |0.03                   
> |0.5                   |1.5                   |20         |10         |5 
> US|Union Standard Airlines |0.19                  |0.05                   
> |0.4                   |1.6                   |20         |10         |5 
> 2 rows selected 
> ij> select distinct * from airlines order by lower(airline_full); 
> ERROR 4287A: The ORDER BY clause may not specify an expression, since 
> the query specifies DISTINCT. 
> ij> select distinct airline_full from airlines order by lower(airline_full); 
> ERROR 4287A: The ORDER BY clause may not specify an expression, since 
> the query specifies DISTINCT. 
> ij> 
> I didn't find a JIRA enhancement to remove this restriction.  I suggest 
> you file an Enhancement request to remove the restriction reported by 
> ERROR 4287A. 

-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message