ORDER BY on nested queries misbehaves because of server configuration?

Question

ORDER BY on nested queries misbehaves because of server configuration?

avi_a

Mr or Mrs. 500

Points: 527
More actions
November 4, 2004 at 2:33 am

#84761

HELP!
I have a series of nested queries that use ORDER BY on each other on an integer field
For some reason, running this query on one server works, but running on a different server misbehaves. Both servers have a restore of the same database from the same backup set, so I don't think this is an T-SQL issue.
I've made sure that I'm sorting by an integer value, so this should not be a server collation issue, unless collation affects integers? (From what I know, collation shoud only affect sorting by character data).
The query looks like this:
select top 10 * from (
select top 20 * from (
select intField from mytable
) a order by intfield
) a order by intfield desc -- this ORDER BY clauese misbehaves.
I've played around with the query trying to pinpoint the problem, and for some reason the following change solves the problem on the prooblematic server:
select top 10 * from (
select top 20 * from (
select intField from mytable
) a order by intfield
) a order by cast(intfield as integer) desc -- this fixes things
Again - the first query works fine one one server and only misbehaves on a different server.
Any help would be appreciated,
- Avi

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Francis Apel SSC Eights! Points: 918 More actions · Answer 1

For what it is worth, both statements work just fine on my servers. are both of your servers at the same version? (select @@version). What do you mean "misbehaves"? Are you getting an error, or just "incorrect" results?

Francis
-----------------
SQLRanger.com

avi_a Mr or Mrs. 500 Points: 527 More actions · Answer 2

As a matter of fact, both servers are not EXACTLY the same version - seems to be a change of build number and date.

the OK one is:

Microsoft SQL Server 7.00 - 7.00.1063 (Intel X86)

Apr 9 2002 14:18:16

Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

while the problematic one is:

Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)

Nov 27 1998 22:20:07

Desktop Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

Now I checked some other servers that are OK with the query and they all seem to be build 7.00.1063, so maybe this is the issue?

The misbehaviour is that the second ORDER BY clause sorts ASCENDING instead of DESCENDING. Once I use explicit typecasting (i.e. cast(... as integer)) for the ORDER BY it sorts DESCENDING as it should.

Maybe we're up on something with the build numbers - I'll try and follow up on this.

- Avi

avi_a Mr or Mrs. 500 Points: 527 More actions · Answer 3

Ha!

This seem to be a known Microsoft bug - see article at http://support.microsoft.com/kb/246201/EN-US/

I tried tha sample statement they provide in the article and it indeed seems to be it - on my problematic server it yields the problematic result described by Microsoft, and on the OK server it yields the proper results as described by microsoft.

According to Microsot this has been fixed in SP2 (see fixlist in http://support.microsoft.com/kb/254369/EN-US/, our iisue is

56519

246201

FIX: Subquery with ORDER BY may Return Results in Wrong Order

So I guess I'm using SQL7.0 with no service pack applied on the problematic server, while my good servers are service-packed and thus OK!

I guess this wraps it up - thanks for the advice.

- Avi