Mailing-List: contact derby-dev-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: <derby-dev@db.apache.org>
Message-ID: <1780690465.1205467044328.JavaMail.jira@brutus>
Date: Thu, 13 Mar 2008 20:57:24 -0700 (PDT)
From: "Bryan Pendleton (JIRA)" <jira@apache.org>
To: derby-dev@db.apache.org
Subject: [jira] Commented: (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.atlassian=
.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=3D1257=
8590#action_12578590 ]=20

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

Hi Thomas, thanks for looking at the patch. I'm sorry I didn't get back to =
you sooner.

The "if" statement that I wish I could write is something like:

   if the query specified DISTINCT, and if this ORDER BY expression=20
   references any column which is not one of the DISTINCT
   columns, reject the query, for there may be multiple possible values of
   that column and we don't know which one to use for ordering the results.

Here's a great explanation of the problem:
https://issues.apache.org/jira/browse/DERBY-2351?focusedCommentId=3D1247387=
1#action_12473871
The only difference in this case is that we're concerned with ORDER BY *exp=
ressions*,
not simple column references, and so an example query might be:

  SELECT DISTINCT name FROM person ORDER BY age * 2

and we wouldn't know whether to order the results as:
  John (age 10*2), then Mary (age 20*2)
or
  Mary (age 20*2), then John (age 30*2)

What Derby *actually* does, with this patch in place, is to implicitly incl=
ude (age*2)
into the DISTINCT list, so it sees *both* John records and produces the res=
ults:
  John (age 10*2), Mary (age 20*2), John (age 30*2)

That is, even though the user specified "DISTINCT name", Derby produces two
rows with the same name.


> 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, 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.