ASE Database forum - why can distinct and order by NOT work together ?

Author

why can distinct and order by NOT work together ?

Albert

2005-12-08, 1:25 pm

Hello, Sybase experts,

I have a simple and strange question :

I create a table:
create table mytest (
c1 smallint,
c2 varchar(10))

insert some data:
insert mytest values(1,'abcd')
insert mytest values(2,'abcd')
insert mytest values(3,'efgh')

select result:
select * from mytest

c1 c2
------------
1 abcd
2 abcd
3 efgh

ok, if I run :
select distinct c2
from mytest
order by c1

c2
----
abcd
abcd
efgh

The "distinct" does NOT work, why ? it's a sybase bug ?
I am using Sybase 11.9 and 12.5

Thank you

Albert

---== Posted via the PFCGuide Web Newsreader ==---
http://www.mcse.ms/_newsgroups/group_list.asp

Bret Halford

2005-12-08, 8:25 pm

Sounds like a bug, and I think I recall something similar a
few years back. What *exact* versions are you running
(select @@version) - are you on a reasonably current ebf?
If not, try applying the current rollup, then let me know if
you still see the issue.

Cheers,
-bret

Albert wrote:

> Hello, Sybase experts,
>
> I have a simple and strange question :
>
> I create a table:
> create table mytest (
> c1 smallint,
> c2 varchar(10))
>
> insert some data:
> insert mytest values(1,'abcd')
> insert mytest values(2,'abcd')
> insert mytest values(3,'efgh')
>
> select result:
> select * from mytest
>
> c1 c2
> ------------
> 1 abcd
> 2 abcd
> 3 efgh
>
> ok, if I run :
> select distinct c2
> from mytest
> order by c1
>
> c2
> ----
> abcd
> abcd
> efgh
>
> The "distinct" does NOT work, why ? it's a sybase bug ?
> I am using Sybase 11.9 and 12.5
>
> Thank you
>
> Albert
>
> ---== Posted via the PFCGuide Web Newsreader ==---
> http://www.mcse.ms/_newsgroups/group_list.asp

Albert

2005-12-08, 8:25 pm

The verson is :

select @@version
Adaptive Server Enterprise/12.5.2/EBF 12053 ESD#2/P/Sun_svr4/OS
5.8/ase1252/1844/32-bit/FBO/Thu Aug 12 23:07:03 2004

and

select @@version
Adaptive Server Enterprise/11.9.2.6/1290/P/EBF 10491 ESD1/Sun_svr4/OS
5.5.1/FBO/Mon Jul 15 06:54:22 2002

The issue is still there.

Thanks

Albert

On 8 Dec 2005 11:54:42 -0800,
in sybase.public.ase.general
Bret Halford <bret@sybase.com> wrote:
>Sounds like a bug, and I think I recall something similar a
>few years back. What *exact* versions are you running
>(select @@version) - are you on a reasonably current ebf?
>If not, try applying the current rollup, then let me know if
>you still see the issue.
>
>Cheers,
>-bret
>
>
>Albert wrote:
>
>

Mark A. Parsons

2005-12-08, 8:25 pm

Well, if it's a bug then it also exists in 12.5.3 (ESD #3) and 15.0 (ESD
#1).

This is probably somewhere in the manauls (Reference manual? TSQL
manual?) ...

Since the c1 column (in the 'order by' clause) is not part of the
'select disctint' list, Sybase will add c1 as a hidden column in the
'select disctint' list in order to allow for the 'order by' clause to
function properly. This in turn means that the 'distinct' operation is
performed on the pair of columns. In other words, the original query
gets rewritten internally like such:

select disctinct c1, c2
from mytest
order by c1

This generates an internal result set that looks like:

c1 c2
------- ---------
1 abcd
2 abcd
3 efgh

But prior to displaying the result set Sybase removes the 'hidden' c1
from the result set leading to:

c2
---------
abcd
abcd
efgh

-------------------

If an additional record of (3,'efgh') is added to the table you'll see
that you get the same exact result set because the duplicate (3,'efgh')
is removed.

Bret Halford wrote:
> Sounds like a bug, and I think I recall something similar a
> few years back. What *exact* versions are you running
> (select @@version) - are you on a reasonably current ebf?
> If not, try applying the current rollup, then let me know if
> you still see the issue.
>
> Cheers,
> -bret
>
>
> Albert wrote:
>
>
>
>

Eugene Korolkov

2005-12-08, 8:25 pm

Mark,

ASE 12.0 - the same story.
Why Oracle produce ORA-01791:not a SELECTed expression for that query?
select distinct c2
from mytest
order by c1
/

Regards,
Eugene

Mark A. Parsons wrote:
[color=darkred]
> Well, if it's a bug then it also exists in 12.5.3 (ESD #3) and 15.0
> (ESD #1).
>
> This is probably somewhere in the manauls (Reference manual? TSQL
> manual?) ...
>
> Since the c1 column (in the 'order by' clause) is not part of the
> 'select disctint' list, Sybase will add c1 as a hidden column in the
> 'select disctint' list in order to allow for the 'order by' clause to
> function properly. This in turn means that the 'distinct' operation
> is performed on the pair of columns. In other words, the original
> query gets rewritten internally like such:
>
> select disctinct c1, c2
> from mytest
> order by c1
>
> This generates an internal result set that looks like:
>
> c1 c2
> ------- ---------
> 1 abcd
> 2 abcd
> 3 efgh
>
> But prior to displaying the result set Sybase removes the 'hidden' c1
> from the result set leading to:
>
> c2
> ---------
> abcd
> abcd
> efgh
>
> -------------------
>
> If an additional record of (3,'efgh') is added to the table you'll see
> that you get the same exact result set because the duplicate
> (3,'efgh') is removed.
>
>
>
> Bret Halford wrote:
>

Mark A. Parsons

2005-12-08, 8:25 pm

If you're asking why Oracle fails to run the query ... *shrug* ... you
may need to find an Oracle oracle to definitively answer that one.

Actually, I wouldn't be surprised if this particular query is not
compliant with the plain vanilla SQL standard. It may be that Oracle is
attempting to enforce a SQL standard (hence the failure) while Sybase is
allowing this (possibly) non-SQL standard behaviour (hence the success
of the query but the 'odd' results).

Eugene Korolkov wrote:

> Mark,
>
> ASE 12.0 - the same story.
> Why Oracle produce ORA-01791:not a SELECTed expression for that query?
> select distinct c2
> from mytest
> order by c1
> /
>
> Regards,
> Eugene
>
> Mark A. Parsons wrote:
>
>

Bret Halford

2005-12-09, 11:24 am

Ok, did a little looking. This behavior is actually documented, see
the TSQL Manual:

order by and group by used with select distinct
A select distinct query with order by or group by can return duplicate values
if the order by or group by column is not in the select list. For example:

select distinct pub_id
from titles
order by type
pub_id
------
0877
0736
1389
0877
1389
0736
0877
0877

(8 rows affected)

If a query has an order by or group by clause that includes columns not in
the select list, Adaptive Server adds those columns as hidden columns in the
columns being processed. The columns listed in the order by or group by
clause are included in the test for distinct rows. To comply with ANSI
standards, include the order by or group by column in the select list. For
example:

select distinct pub_id, type
from titles
order by type
pub_id type
------ ------------
0877 UNDECIDED
0736 business
1389 business
0877 mod_cook
1389 popular_comp
0736 psychology
0877 psychology
0877 trad_cook

(8 rows affected)

Albert

2005-12-09, 8:25 pm

Thank you, both Mark A. Parsons and Bret Halford, that makes sense now.

On 9 Dec 2005 08:11:33 -0800,
in sybase.public.ase.general
Bret Halford <bret@sybase.com> wrote:
>Ok, did a little looking. This behavior is actually documented, see
>the TSQL Manual:
>
>order by and group by used with select distinct
>A select distinct query with order by or group by can return duplicate values
>if the order by or group by column is not in the select list. For example:
>
>select distinct pub_id
>from titles
>order by type
>pub_id
>------
>0877
>0736
>1389
>0877
>1389
>0736
>0877
>0877
>
>(8 rows affected)
>
>If a query has an order by or group by clause that includes columns not in
>the select list, Adaptive Server adds those columns as hidden columns in the
>columns being processed. The columns listed in the order by or group by
>clause are included in the test for distinct rows. To comply with ANSI
>standards, include the order by or group by column in the select list. For
>example:
>
>select distinct pub_id, type
>from titles
>order by type
>pub_id type
>------ ------------
>0877 UNDECIDED
>0736 business
>1389 business
>0877 mod_cook
>1389 popular_comp
>0736 psychology
>0877 psychology
>0877 trad_cook
>
>(8 rows affected)
>
>

Sponsored Links