[
https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=12576926#action_12576926
]
Thomas Nielsen commented on DERBY-3373:
---------------------------------------
I agree it would probably be good to allow the legal queries once again, even
if the expense is allowing an illegal one.
That said we should ultimately reject the bad illegal ones.
If that's our understanding, the patch looks ok. The changed test run
successfully.
If I understand correctly what you're looking for in the removed if statement
is something like "if none of the columns in the orderby column list are in the
target.rcl, and the target is a distinct query, then throw exception" ?
Didn't the removed if statement really said the inverse?
"if there is a column in the orderby columns that isn't in the target.rcl, and
target is a distinct, then throw exception"
> 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
>
> Attachments: allowExpressions.diff
>
>
> 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_ <[EMAIL PROTECTED]> 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.
