[#DERBY-3373] SQL "distinct" and "order by" needed together

Derby

SQL "distinct" and "order by" needed together

Created: 01/Feb/08 11:04 AM Updated: 30/Jun/09 03:55 PM

Return to search

Component/s:

SQL

Affects Version/s:

10.3.2.1

Fix Version/s:

10.3.3.0, 10.4.1.3, 10.5.1.1

Time Tracking:

Not Specified

File Attachments:

File Name	Date Attached	Attached By	Size
allowExpressions.diff	2008-03-08 08:38 PM	Bryan Pendleton	6 kB
mergeWith2351.diff	2008-03-13 03:28 AM	Bryan Pendleton	6 kB

Environment:

Solaris Dev Express, Java 5

Issue Links:

Reference

This issue is related to:

~~DERBY-2351~~ ORDER BY with expression with distinct in the select list returns incorrect result

Urgency:	Blocker
Bug behavior facts:	Regression
Resolution Date:	22/Mar/08 01:39 AM

Description

« Hide

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.

Description

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.

Show »

All

Comments

Work Log

Change History

Subversion Commits

Sort Order:

Repository	Revision	Date	User	Message
ASF	#637529	Sun Mar 16 04:00:43 UTC 2008	bpendleton	~~DERBY-3373~~: SQL distinct and order by needed together. Derby was issuing the error message "The ORDER BY clause may not specify an expression, since the query specifies DISTINCT" in situations where the message was not appropriate. An example of a query that should have been accepted, but was being rejected, is: select name from person order by lower(name) This patch backs out part of the changes that went in with ~~DERBY-2351~~; specifically, this patch reverts the "if" test which was rejecting any DISTINCT query which had a pulled-up ORDER BY expression. Not all such expressions are invalid, as the above example shows. Since the "if" statement cannot distinguish between valid expressions and invalid ones, it is better to revert to the pre-~~DERBY-2351~~ behavior. A few new tests are also added to demonstrate the behavior.
				Files Changed
				MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql MODIFY /db/derby/code/trunk/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out MODIFY /db/derby/code/trunk/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java

Repository	Revision	Date	User	Message
ASF	#639013	Wed Mar 19 20:58:20 UTC 2008	bpendleton	~~DERBY-3373~~: SQL distinct and order by needed together. Merged change from the trunk via svn merge -r 637528:637529 ../trunk/
				Files Changed
				MODIFY /db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql MODIFY /db/derby/code/branches/10.4/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out MODIFY /db/derby/code/branches/10.4/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java

Repository	Revision	Date	User	Message
ASF	#639935	Sat Mar 22 01:37:46 UTC 2008	bpendleton	~~DERBY-3373~~: SQL distinct and order by needed together. Merged change from the 10.4 branch via svn merge -r 639012:639013 ../10.4/
				Files Changed
				MODIFY /db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/tests/lang/orderby.sql MODIFY /db/derby/code/branches/10.3/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out MODIFY /db/derby/code/branches/10.3/java/engine/org/apache/derby/impl/sql/compile/OrderByColumn.java

Repository	Revision	Date	User	Message
ASF	#650993	Wed Apr 23 18:33:26 UTC 2008	kmarsden	Update master for ~~DERBY-3373~~
				Files Changed
				MODIFY /db/derby/code/branches/10.3.2.1_test/java/testing/org/apache/derbyTesting/functionTests/master/orderby.out