"SELECT DISTINCT ... ORDER BY" is broken in Sybase ASE 12.5.3
So, last night I was running into broken behavior in Sybase 12.5.3 with regards to its behavior with SELECT COUNT(DISTINCT columnname) ... which surprised me, but I could at least rationalize why it was happening. Today, I found an outright bug, where mixing SELECT DISTINCT ... with an ORDER BY clause gives the wrong behavior. Here's a quick session that demonstrates what I'm talking about:
1> SELECT @@version; -m bcp
Adaptive Server Enterprise/12.5.3/EBF 12331 ESD#1/P/Sun_svr4/OS 5.8/ase1253/1900/64-bit/FBO/Tue Jan 25 08:52:58 2005|
1> CREATE TABLE #test (x int, y int);
1> INSERT INTO #test (x, y) VALUES (1, 2);
(1 row affected)
1> INSERT INTO #test (x, y) VALUES (1, 10);
(1 row affected)
1> INSERT INTO #test (x, y) VALUES (2, 1);
(1 row affected)
1> INSERT INTO #test (x, y) VALUES (2, 5);
(1 row affected)
1> INSERT INTO #test (x, y) VALUES (3, 4);
(1 row affected)
1> INSERT INTO #test (x, y) VALUES (3, 7);
(1 row affected)
1> SELECT DISTINCT x FROM #test ORDER BY y DESC;
x
-----------
1
3
2
3
1
2
Has this been fixed in a newer version of Sybase? I can't imagine anyone actually thinks this the right behavior.
I tried reproducing this on SQL Server 2000 and SQL Server won't even allow you to do that type of DISTINCT/ORDER BY mix. I got the following error:
ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
Clearly, there is something with this type of mix that Sybase didn't handle properly if Microsoft disabled ordering by columns that did not appear in a distinct resultset.
SQL can be ambiguous, and this is one of those situations. PostgreSQL mandates an ORDER BY or GROUP BY in DISTINCT-styled queries.
havard=# SELECT distinct a from test order by b desc;
ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list
jhavard=# SELECT distinct on (a) a from test order by b desc;
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
Take a look at the query optimizer and query planner output if possible. I'm willing to bet the query is executed as if it were SELECT DISTINCT ON (b) a FROM test ORDER BY b. After all, you are supplying an ordered field.
Steve:
Interestingly, MySQL 4.1.11 handles this "correctly" as I would have expected:
mysql> SELECT DISTINCT x FROM test ORDER BY y DESC;
+------+
| x |
+------+
| 3 |
| 1 |
| 2 |
+------+
3 rows in set (0.00 sec)
The ASE Transact-SQL User's Guide addresses this on page 105 of the 12.5.1 edition.
"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."
