Who needs ORDER BY DESC anyways

Skurry

Okay, maybe this was a fairly well known bug back then and I'm outing myself as the WTFer here for not knowing, but anyways:

A couple of years ago I was working for a company that produced an ERP software for mail order companies. We were using SQL Server 7.0 back then. The customer was moving into a newly built warehouse, and they were using our software to determine the best places to store their goods.

It turned out our software always chose the worst places, namely those at the top and the bottom of the storage racks, never in the preferred middle where it's much more convenient to pick the goods.

I was on site and assigned to look at the problem. The places were found by a stored procedure, which did some selects to find available places and had a fairly complicated logic to sort them by priority. I looked at the source for hours and hours, but was unable to find the problem.

I'm trying to recreate a minimal working version from memory (without the real business logic, of course) to show the problem:

CREATE PROC SP_WTF
AS
BEGIN

CREATE TABLE #foo (
i INT NOT NULL,
j INT
)

INSERT INTO #foo (i) VALUES (1)
INSERT INTO #foo (i) VALUES (3)
INSERT INTO #foo (i) VALUES (2)

CREATE NONCLUSTERED INDEX INDEX_foo ON #foo (i)

SELECT i FROM #foo ORDER BY i DESC, j ASC

END

The column i kind of represented place priority, and a higher number was better.
Finally, I decided to look into the database itself, to see if the correct version was installed. What I found was this:

CREATE PROC SP_WTF
AS
BEGIN

CREATE TABLE #foo (
i INT NOT NULL,
j INT
)

INSERT INTO #foo (i) VALUES (1)
INSERT INTO #foo (i) VALUES (3)
INSERT INTO #foo (i) VALUES (2)

CREATE NONCLUSTERED INDEX INDEX_foo ON #foo (i)

SELECT i FROM #foo ORDER BY i , j ASC

END

How long does it take you to see the difference? The original Stored Procedure was several pages long... (I know, a WTF by itself).

After the i, the keyword DESC was replaced by four spaces. WTF?! I recreated the stored procedure from CVS using the Query Analyzer, looked at it again, same problem.
Then I removed the index creation, which wasn't doing anything good, probably inserted by some other engineer to "improve performance". This time, the DESC remained. WTF?! Can you explain your client something like this?
By the way, using the Enterprise Manager also worked, so it had to be a bug in the Query Analyzer.

Not sure if this small example would actually reproduce on SQL Server 7.0 (I don't have one now to test this on), but they apparantly fixed it on later versions.

Champion

Ahhh yes, that infamous "Lets just randomly remove a word from the code here" bug.

It really makes you wonder about the sorts of people that design those sorts of things - it's almost enough to make you believe in those spoof "hidden menu" images that float around, the ones that invariably reflect the inherent irritation that Microsoft software incites.