Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1058595317.1205941704731.JavaMail.jira@brutus>
Date: Wed, 19 Mar 2008 08:48:24 -0700 (PDT)
From: "Dyre Tjeldvoll (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Assigned: (DERBY-3373) SQL "distinct" and "order by" needed
 together
In-Reply-To: <463993.1201863908181.JavaMail.jira@brutus>
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


     [ https://issues.apache.org/jira/browse/DERBY-3373?page=3Dcom.atlassia=
n.jira.plugin.system.issuetabpanels:all-tabpanel ]

Dyre Tjeldvoll reassigned DERBY-3373:
-------------------------------------

    Assignee: Bryan Pendleton  (was: Dyre Tjeldvoll)

Sorry. I changed the wrong issue. Changing the assignee back to Bryan.=20

> 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, 10.4.0.0
>
>         Attachments: allowExpressions.diff, mergeWith2351.diff
>
>
> I am pasting here the communication from the mailinglist. I am having a b=
locking and large problem with it because I have to make a release that nee=
ds the specified SQL query.=20
> tom_ wrote:
> > The errormessage is=20
> >=20
> > The ORDER BY clause may not specify an expression, since the query spec=
ifies=20
> > DISTINCT=20
> > [Error Code: 20000]=20
> > [SQL State: 4287A]=20
> >=20
> > The statement is=20
> >=20
> > select distinct=20
> > t1.t1_id, t2.t2value1, t2.t2value2, t2.t2value3=20
> > from=20
> > t1, t2, t3  =20
> > where=20
> > ...=20
> > order by lower(t2.t2value2) , lower(t2.t2value1) , lower(t2.t2value3)=
=20
> >=20
> >=20
> >=20
> >=20
> > Dyre.Tjeldvoll wrote:=20
> >  =20
> >> tom_ <tom12167@...> writes:=20
> >>=20
> >>    =20
> >>> I am using "disctinct" because of some self-joins and also needed to =
add=20
> >>> an=20
> >>> "order by" clause. An error is shown. Is it not possible to use disti=
nct=20
> >>> and=20
> >>> order by together?=20
> >>>      =20
> >> I think it is allowed. Executing=20
> >>=20
> >> select distinct * from sys.systables order by tablename;=20
> >>=20
> >> in ij works just fine. Could you show the error message you get, and=
=20
> >> perhaps what the table looks like?=20
> >>=20
> >> --=20
> >> dt=20
> >>=20
> >>=20
> >>   =20
> =C2=AB  [hide part of quote]
> Hi Tom -=20
> I see what you mean using the demo DB toursDB:=20
> ij> select * from airlines order by lower(airline_full);=20
> A&|AIRLINE_FULL            |BASIC_RATE            |DISTANCE_DISCOUNT    =
=20
> |BUSINESS_LEVEL_FACTOR=20
> |FIRSTCLASS_LEVEL_FACT&|ECONOMY_SE&|BUSINESS_S&|FIRSTCLASS&=20
> -------------------------------------------------------------------------=
---------------------------------------------------------------------------=
-------=20
> AA|Amazonian Airways       |0.18                  |0.03                  =
=20
> |0.5                   |1.5                   |20         |10         |5=
=20
> US|Union Standard Airlines |0.19                  |0.05                  =
=20
> |0.4                   |1.6                   |20         |10         |5=
=20
> 2 rows selected=20
> ij> select distinct * from airlines order by lower(airline_full);=20
> ERROR 4287A: The ORDER BY clause may not specify an expression, since=20
> the query specifies DISTINCT.=20
> ij> select distinct airline_full from airlines order by lower(airline_ful=
l);=20
> ERROR 4287A: The ORDER BY clause may not specify an expression, since=20
> the query specifies DISTINCT.=20
> ij>=20
> I didn't find a JIRA enhancement to remove this restriction.  I suggest=
=20
> you file an Enhancement request to remove the restriction reported by=20
> ERROR 4287A.=20

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