Who needs ORDER BY DESC anyways



  • 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:

    <FONT face="Courier New">CREATE PROC SP_WTF
    AS
    BEGIN </FONT>

    <FONT face="Courier New">CREATE TABLE #foo (
     i INT NOT NULL,
     j INT
    )</FONT>

    <FONT face="Courier New">INSERT INTO #foo (i) VALUES (1)
    INSERT INTO #foo (i) VALUES (3)
    INSERT INTO #foo (i) VALUES (2)</FONT>

    <FONT face="Courier New">CREATE NONCLUSTERED INDEX INDEX_foo ON #foo (i)</FONT>

    <FONT face="Courier New">SELECT i FROM #foo ORDER BY i DESC, j ASC</FONT>

    <FONT face="Courier New">END</FONT>

    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:

    <FONT face="Courier New">CREATE PROC SP_WTF
    AS
    BEGIN</FONT>

    <FONT face="Courier New">CREATE TABLE #foo (
     i INT NOT NULL,
     j INT
    )</FONT>

    <FONT face="Courier New">INSERT INTO #foo (i) VALUES (1)
    INSERT INTO #foo (i) VALUES (3)
    INSERT INTO #foo (i) VALUES (2)</FONT>

    <FONT face="Courier New">CREATE NONCLUSTERED INDEX INDEX_foo ON #foo (i)</FONT>

    <FONT face="Courier New">SELECT i FROM #foo ORDER BY i     , j ASC</FONT>

    <FONT face="Courier New">END</FONT>

    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.



  • 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.


Log in to reply
 

Looks like your connection to What the Daily WTF? was lost, please wait while we try to reconnect.