db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Bryan Pendleton (JIRA)" <j...@apache.org>
Subject [jira] Commented: (DERBY-3373) SQL "distinct" and "order by" needed together
Date Sat, 08 Mar 2008 14:41:46 GMT

    [ https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576546#action_12576546
] 

Bryan Pendleton commented on DERBY-3373:
----------------------------------------

I think that these lines in OrderByColumn, which were added by DERBY-2351,
are the focus of this problem:

                        if (addedColumnOffset >= 0 &&
                                        target instanceof SelectNode &&
                                        ( (SelectNode)target ).hasDistinct())
                                throw StandardException.newException(SQLState.LANG_DISTINCT_ORDER_BY_EXPRESSION);

The problem is that these lines should have an additional
condition, along the lines of:

  AND, there exists at least 1 column reference among the
  column references in this ORDER BY expression, which
  does not appear as one of the columns which are being
  selected DISTINCT.

The problem is, I'm not sure how to code that AND test I'll have
to study the SelectNode data structure to see if I can easily see
how to tell "the columns which are being selected DISTINCT".


> SQL "distinct" and "order by" needed together
> ---------------------------------------------
>
>                 Key: DERBY-3373
>                 URL: https://issues.apache.org/jira/browse/DERBY-3373
>             Project: Derby
>          Issue Type: Bug
>          Components: SQL
>    Affects Versions: 10.3.2.1
>         Environment: Solaris Dev Express, Java 5
>            Reporter: Thomas Vatter
>            Assignee: Bryan Pendleton
>            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