[
https://issues.apache.org/jira/browse/DERBY-3373?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]
Bryan Pendleton updated DERBY-3373:
-----------------------------------
Attachment: allowExpressions.diff
Attached is 'allowExpressions.diff', a patch proposal.
This proposal backs out the line of code added by DERBY-2351
which was rejecting DISTINCT queries with ORDER BY expressions
which did not appear explicitly in the SELECT list.
With this patch, Derby once again allows a range of legitimate
queries, such as:
select distinct name from person order by lower(name)
select distinct * from person order by age + 10
Unfortunately, with this patch, Derby now also allows the invalid query:
select distinct name from person order by age*2
I haven't been able to quickly find an easy way to distinguish the
valid queries from the invalid ones, and so as a short term measure
I think it would be good to restore the previous (10.2 and prior)
behavior for DISTINCT queries involving ORDER BY expressions.
It was good that the DERBY-2351 patch rejected some invalid
queries, but it was far worse that the patch rejected some valid ones.
I added a number of new test cases to illustrate the behavior.
Please have a look at the patch and let us know what you think!
> 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.
