The Daily WTF: Curious Perversions in Information Technology

2005-09-06 Reply Admin

Nah, you can do this neatly as well: First construct a single-digit BCD adder with carry. Then chain two of these together. If the MSD adder carries, set both digits to zero. Trivial.

2005-09-06 Reply Admin

Bustaz Kool:
JRG:
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

As far as I can discern, all of the one liners throw an error on invalid input.

Not knowing the application or the database, I can't say what is invalid input. Perhaps (by design) values other than two digits have special meaning and must be preserved. If we assume we should preserve current behavior, suggested replacement code should be tested with strings like '2', '-6', '3D', 'NA', etc. to ensure that the original value is preserved.

2005-09-06 Reply Admin

SET @sequenceCode = CASE

  WHEN (@sequenceCode LIKE '[0-9][1-9]')
           or (@sequenceCode LIKE '[1-9][0-9]')

    THEN replace(replace(str(@sequenceCode+1, 2),' ','0'),'*','0')

    ELSE @sequenceCode

END

Everyone knows that the costliest part of this is the conditionals, and I only have one!

joost · 2005-09-06 Reply Admin

msumerano:

Handles anything in the SequenceNumber and properly "wraps" around when the passed in sequence number is 99.

declare @SequenceNumber char(2)
select @SequenceNumber = '95'

declare @sn int

if (isnumeric(@SequenceNumber) = 1)
	select @sn = convert(int, @SequenceNumber)
else
	select @sn = 0

if (@sn < 0 or @sn >= 99)
	select @SequenceNumber = '00'
else
	select @SequenceNumber = right('0' + convert(varchar, (@sn + 1)), 2)

select @SequenceNumber

Way to handle boolean function results! Only, the second test should be:

# I only trust the = operator

if (($sn < 0) = 1 or ($sn >= 99) = 1)

And maybe the first should be...

if (((isnumeric(@sequenceNumber) = 1) > 0) = 1)

Looks kinda cheery. (And (Ever (So (Lispy (.))))) But hey, we can always blame the T-SQL "programming" "language" for things like this ;-)

Disclaimer: I don't know (how a Boolean is represented in) T-SQL

tw000 · 2005-09-06 Reply Admin

Damn, if I didn't laugh out loud reading that. My brain managed to "censor" the "second special case."

Maurits · 2005-09-06 Reply Admin

joost:
I don't know (how a Boolean is represented in) T-SQL

Either
CAST(1 AS BIT) -- true
CAST(0 AS BIT) -- false

or
DECLARE @True bit, @False bit
SELECT @True = 1, @False = 0

JRG · 2005-09-06 Reply Admin

Bustaz Kool:

JRG:
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

As far as I can discern, all of the one liners throw an error on invalid input.

You do filter your input before you use it don't you?

2005-09-06 Reply Admin

How did this get in here?

2005-09-06 Reply Admin

That is exactly what I was thinking.

OneFactor · 2005-09-06 Reply Admin

Considering how many "improvements" fail to leave '00' and non-numeric inputs alone, I'd be tempted to not fix the WTF if I ran across the code. An elegant yet erroneous change does not count as "fixing" the WTF.

OneFactor · 2005-09-06 Reply Admin

Bustaz Kool:

JRG:
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

As far as I can discern, all of the one liners throw an error on invalid input.

Ok let me try:

set @sequenceCode = case when (CAST(@sequenceCode as int) > 0) then right( cast 101 + cast( @sequenceCode as int) as char(3)) , 2) else @sequenceCode end

JRG · 2005-09-06 Reply Admin

OneFactor:
Bustaz Kool:

JRG:
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

As far as I can discern, all of the one liners throw an error on invalid input.

Ok let me try:

set @sequenceCode = case when (CAST(@sequenceCode as int) > 0) then right( cast 101 + cast( @sequenceCode as int) as char(3)) , 2) else @sequenceCode end

The cast will fail on non-numeric input.


DECLARE @sequenceCode  VARCHAR(2)
SET @sequenceCode  = '99'
-- Do your input validation somewhere here...
SET @sequenceCode =
CASE
WHEN  ISNUMERIC(@sequenceCode) = 0 THEN @sequenceCode
WHEN  @sequenceCode = '00' THEN '00'
ELSE  RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)
END

SELECT @sequenceCode

JRG · 2005-09-06 Reply Admin

OneFactor:
Bustaz Kool:

JRG:
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

As far as I can discern, all of the one liners throw an error on invalid input.

Ok let me try:

set @sequenceCode = case when (CAST(@sequenceCode as int) > 0) then right( cast 101 + cast( @sequenceCode as int) as char(3)) , 2) else @sequenceCode end

(Stupid forum editor)

The cast will fail on non-numeric input.

DECLARE @sequenceCode VARCHAR(2) SET @sequenceCode = '99' -- Do your input validation somewhere here... SET @sequenceCode = CASE WHEN ISNUMERIC(@sequenceCode) = 0 THEN @sequenceCode WHEN @sequenceCode = '00' THEN '00' ELSE RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2) END SELECT @sequenceCode

2005-09-06 Reply Admin

Volmarias:

For your viewing pleasure, I give you: WTFGenerator, in Java!
    public class WTFGenerator
    {
        public static void main(String[] args)
        {
            for(int i = 1; i!=100; ;)
            {
                System.out.println("IF @sequenceCode = '" + prettyMethod(x++) + "'");
                System.out.println("SET @sequenceCode = '" + prettyMethod(x) + "'");
             }
        }

    public String prettyMethod (int x)
    {
        if(x < 10) return "0" + x;
        return new String(x);
    }
}

Copy + Paste and there you go! Now, I haven't actually tried to compile this, so it probably doesn't work, but I think that I've solved today's WTF!

Dude, as WTFGenerators go, this is a great one. The fact that you're incrementing x while looping over i is fairly obvious, but may lead to some severe crashes before someone finds out. All that's fairly trivial... But I especially love the "i != 100" (as opposed to the "i < 100") which is always garanteed to have the next person maintaining your code searching for at least a day before he finds the conditional "i += 10".

Brillant!

Fabian

2005-09-06 Reply Admin

Has anyone here ever remarked on the quality of the forum software?

Jus' kiddin'...

What was I was trying to say was:
"(as opposed to the "i < 100") which is guaranteed to have 'the next guy' searching for at least a day before he finds the conditional "i += 10".

(...praying this works...)

2005-09-06 Reply Admin

In Ada:
inc num and watch out for that num > 99, ok?

md2perpe · 2005-09-06 Reply Admin

If it had been MySQL, I would have set the column type to TINYINT(2) UNSIGNED ZEROFILL and used modulus (MOD 100) when incrementing it.

2005-09-06 Reply Admin

Couldn't you just do a Case statement and if case is equal to 99 set it to 0 otherwise add 1?
Codename: Mushoo

Quinnum · 2005-09-06 Reply Admin

Anonymous:
JRG:

Anonymous:
JRG:
Slightly shorter, uses implicit casting...

SELECT RIGHT('0'+CONVERT(VARCHAR,@sequenceCode+1),2)

Were we told what the database product was?

com.sybase.jdbc2.jdbc.SybSQLException: Implicit conversion from datatype 'VARCHAR' to 'INT' is not allowed. Use the CONVERT function to run this query.
Error code: 257
SQL state: 42000

Actually, it's there in the OPs message... T-SQL.

Sybase invented T-SQL. Microsoft, having acquired rights to use it, has modified it in their product. "T-SQL" by itself doesn't tell you if the implicit cast will work.

Yeah, but using Sybase is a WTF in itself. It's like running a real old version of SQL Server.

Sybase almost makes baby Jesus cry, although not as much as Oracle.

2005-09-06 Reply Admin

It's a trick question, right? The WTF isn't (only) in this code. If a record might need to be incremented, then it shouldn't have been a decimal string in the first place. And if this string really does need to be incremented, then I'm not writing a line of code until I've gathered requirements and you've signed off on a definition of "increment" for char(2)s. Then I'll write the unit tests (I don't know how wide chars are here, if they're small enough these could be exhasutive), and your nominated technical reviewer can sign off on those. Then I'll write the code. But only if you're sure you don't need a design document.

2005-09-06 Reply Admin

travisowens:
Ok let's dive into the original coder's mind and assume that there's no way to casting or type conversion in T-SQL.

This code is the worst of all the repititve code I've seen on here in a long time, this guy really hand typed 100 conversions?!

Would it have been better to reverse the string and add 1 to the first digit by doing his conversion, but only typed 0-9, and if we're at 9 and adding one, add one to the next digit, and so forth. While it would have required *gasp* a loop, at least he could have done his conversion in 10 checks instead of 100.

So what happens when his database needs to support more than 0-100, what about 100,000? That's a whole month of copying and pasting.

i wouldn't hand type all those conversions - i would write a perl script to generate the code for me, then copy and paste the result!!

2005-09-06 Reply Admin

What people doesn't seem to get is that he kept the numbers on the interval [00..99] because he wasn't going to cover the range [00..4294967295].

Imagine if the column where this is inserted was an integer 64 bits!

III · 2005-09-06 Reply Admin

If a record might need to be incremented, then it shouldn't have been a decimal string in the first place.

i have a sneaking suspicion that this was a y2k fix, and that it eliminated mod100 year problems by storing the 2 digits as a string...

Foon · 2005-09-06 Reply Admin

set @sequenceCode = right(cast(cast( @sequenceCode as int) + 101 as varchar(3)), 2)

or possibly even

set @sequenceCode = cast(cast( @sequenceCode as int) + 101 as varchar(2))

dance2die · 2005-09-06 Reply Admin

All right. I have come to a conclusion that, some of you guys are having some problem understanding the problem of this topic.

To ease the pain of reading If statements, i have come up with even more readable solution

SET @sequenceCode = (
        CASE
            WHEN @sequenceCode = '01' THEN '02'
            WHEN @sequenceCode = '02' THEN '03'
            WHEN @sequenceCode = '03' THEN '04'
            WHEN @sequenceCode = '04' THEN '05'
            WHEN @sequenceCode = '05' THEN '06'
            WHEN @sequenceCode = '06' THEN '07'
        -- snip --
            WHEN @sequenceCode = '94' THEN '95'
            WHEN @sequenceCode = '95' THEN '96'
            WHEN @sequenceCode = '96' THEN '97'
            WHEN @sequenceCode = '97' THEN '98'
            WHEN @sequenceCode = '98' THEN '99'
            WHEN @sequenceCode = '99' THEN '00'
        END
    )

diGriz · 2005-09-07 Reply Admin

Senior programmer? Looks more like a senile programmer shiver

2005-09-07 Reply Admin

Hey Bill,

Write me a stored proc that does this ..

Now was that fast or what - 15 secs. :)

2005-09-07 Reply Admin

All of you are trying to actually change the algorithm, and use casting to an integer or something. Assume that casting is not available, or is very expensive (otherwise he would have used it, right?)
Besides, the goal is to "Fix the WTF", so it must still remain a WTF (albeit fixed) when done.
So, here's my solution. (I don't know the syntax for T-SQL arrays, so fix it further yourself.)
Array[100] = {'00','01','02','03', ... , '99'}
for(i=0;i<100;++i)
IF (@sequenceCode = Array[i])
    {
       SET @sequenceCode = Array[(i+1)%100];
       break;
    }
How's that?

Dylan · 2005-09-07 Reply Admin

-- Best to use a temporary table for this job. Much quicker.
Declare @i int
Declare @c char(2)
Declare @t table (GoFrom char(2), Go_To char(2))

Set @i = 0
While (@i < 100)
Begin
   If @i = 0
       Set @c = '00'
   Else
       Set @c = right('0' + cast(@i+1 as varchar(3)),2)
   Insert Into @t (GoFrom, Go_To) values (right('0' + cast(@i as varchar(3)),2), @c)
   Set @i=@i+1
End
Select @SequenceCode = isnull(Go_To,@SequenceCode) From @t Where GoFrom = @SequenceCode

2005-09-07 Reply Admin

Maurits:
Prepwork..

CREATE TABLE sequencecodemap
(
this char(2),
    next char(2)
)

INSERT INTO sequencecodemap (this, next)
VALUES ('00', '01')

INSERT INTO sequencecodemap (this, next)

VALUES ('01', '02')

...

INSERT INTO sequencecodemap (this, next)

VALUES ('99', '00')

Finally...

SELECT
    @SequenceCode = Next
FROM
    SequenceCodeMap
WHERE
    @SequenceCode = This

You Forgot to Build INDEX!!

2005-09-07 Reply Admin

dance2die:
All right. I have come to a conclusion that, some of you guys are having some problem understanding the problem of this topic.

To ease the pain of reading If statements, i have come up with even more readable solution

SET @sequenceCode = (
        CASE
            WHEN @sequenceCode = '01' THEN '02'
            WHEN @sequenceCode = '02' THEN '03'
            WHEN @sequenceCode = '03' THEN '04'
            WHEN @sequenceCode = '04' THEN '05'
            WHEN @sequenceCode = '05' THEN '06'
            WHEN @sequenceCode = '06' THEN '07'
        -- snip --
            WHEN @sequenceCode = '94' THEN '95'
            WHEN @sequenceCode = '95' THEN '96'
            WHEN @sequenceCode = '96' THEN '97'
            WHEN @sequenceCode = '97' THEN '98'
            WHEN @sequenceCode = '98' THEN '99'
            WHEN @sequenceCode = '99' THEN '00'
        END
    )

That would be:

SET @sequenceCode = (
        CASE @sequenceCode
            WHEN '01' THEN '02'
            WHEN '02' THEN '03'
            WHEN '03' THEN '04'
            WHEN '04' THEN '05'
            WHEN '05' THEN '06'
            WHEN '06' THEN '07'
        -- snip --
            WHEN '94' THEN '95'
            WHEN '95' THEN '96'
            WHEN '96' THEN '97'
            WHEN '97' THEN '98'
            WHEN '98' THEN '99'
            WHEN '99' THEN '00'
        END
    )

:)

2005-09-07 Reply Admin

This is surely the correct answer (as md2perpe also pointed out). The WTF is using a
CHAR for a counter in the first place.

So given this piece of arse-wipingly bad database design, what should one do?
a) refactor the database
b) do what the wtf guy did and write 100 conditionals
or
c) use some moderately intelligent programming with casting or a map to do it in
fewer lines.

In real life I might well have ended up doing (b).

dmitriy · 2005-09-07 Reply Admin

The WTFGenerator class given by Volmarias does not compile:

dmitriy@si[202]$ javac WTFGenerator.java
WTFGenerator.java:3: illegal start of expression
for(int i = 1; i!=100; ;) {
^
WTFGenerator.java:3: illegal start of expression
for(int i = 1; i!=100; ;) {
^
WTFGenerator.java:7: ';' expected
}
^
3 errors

Once I remove the errors in the code (those above and others), it prints out lines to set the value to 2 if it is 1, to 3 if it is 2, and so on. Similarly to some of the other submissions, it increments 99 to 100.

2005-09-07 Reply Admin

<font>The obvious WTF is that he doesn't handle wrap around case properly. It should be:

ELSE</font> <font>IF</font> @sequenceCode = <font>'99'</font>
	<font>SET</font> @sequenceCode = <font>'ZZ'

</font>

Chili Joe · 2005-09-07 Reply Admin

Foon:
set @sequenceCode = right(cast(cast( @sequenceCode as int) + 101 as varchar(3)), 2)

or possibly even

set @sequenceCode = cast(cast( @sequenceCode as int) + 101 as varchar(2))

The 1st one would be the solution I would have come up with. For fixed-length-char numeric sequences. Easily extendable for larger lengths:

3 digits:
set @sequenceCode = right(cast(cast( @sequenceCode as int) + 1001 as varchar(3)), 3)
4 digits:
set @sequenceCode = right(cast(cast( @sequenceCode as int) + 10001 as varchar(3)), 4)

Chili Joe · 2005-09-07 Reply Admin

Chili Joe:
Foon:
set @sequenceCode = right(cast(cast( @sequenceCode as int) + 101 as varchar(3)), 2)

or possibly even

set @sequenceCode = cast(cast( @sequenceCode as int) + 101 as varchar(2))

The 1st one would be the solution I would have come up with. For fixed-length-char numeric sequences. Easily extendable for larger lengths:

3 digits:
set @sequenceCode = right(cast(cast( @sequenceCode as int) + 1001 as varchar(3)), 3)
4 digits:
set @sequenceCode = right(cast(cast( @sequenceCode as int) + 10001 as varchar(3)), 4)

WTF, should be

set @sequenceCode = right(cast(cast( @sequenceCode as int) + 1001 as varchar(4)), 3)

set @sequenceCode = right(cast(cast( @sequenceCode as int) + 10001 as varchar(5)), 4)

2005-09-07 Reply Admin

Everyone knows that you should be using lookup tables and select statements in SQL. And why on earth would we want to waste disk space by storing the lookup table in the database when we can generate it on the fly?

select @sequencenum=isnull(b,@sequencenum) from
	(select 
		convert(varchar,a)+convert(varchar,b) a,
		right('0'+convert(varchar,convert(int,convert(varchar,a)+convert(varchar,b))+1),2) b
		from
		(select 0 a union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) a,
		(select 0 b union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) b
		where a != 0 or b != 0
	) c
	where a=@sequencenum

RayS · 2005-09-07 Reply Admin

I've had enough of these things that are supposed to be simple being difficult. I'm going to invent something to make it easier to make simple changes to numbers according to a basic rule.

I shall call it 'maths'.

Vicky · 2005-09-07 Reply Admin

Anonymous:

<font>The obvious WTF is that he doesn't handle wrap around case properly. It should be:

ELSE</font> <font>IF</font> @sequenceCode = <font>'99'</font>
	<font>SET</font> @sequenceCode = <font>'ZZ'

</font>

Now *that* is what I call brillant!

2005-09-07 Reply Admin

dmitriy:
The WTFGenerator class given by Volmarias does not compile:

dmitriy@si[202]$ javac WTFGenerator.java
WTFGenerator.java:3: illegal start of expression
for(int i = 1; i!=100; ;) {
^
WTFGenerator.java:3: illegal start of expression
for(int i = 1; i!=100; ;) {
^
WTFGenerator.java:7: ';' expected
}
^
3 errors

Once I remove the errors in the code (those above and others), it prints out lines to set the value to 2 if it is 1, to 3 if it is 2, and so on. Similarly to some of the other submissions, it increments 99 to 100.

Here is a Python (but not pythonic at all) version that does handle 99>00 increment, and does in fact generate any random length incrementing suite behaving as today's WTF does: increment by 1, replace maximum value by "00", pad to 2, anything out of the (]0, upper[) intact.

def WTFGenerator(maxIndice):
    try:
        maxIndice=int(maxIndice)
    except ValueError:
        while 1:
            print "SET @sequenceCode='ZZ'"
for i in xrange(1,maxIndice):
    if not i == 1:
        print "ELSE IF @sequenceCode='%02d'"%(i)
    else:
        print "IF @sequenceCode='%02d'"%(i)
    if not i==maxIndice-1:
        print "\tSET @sequenceCode='%02d'"%(i+1)
    else:
        print "\tSET @sequenceCode='00'"</pre></blockquote>

Please do notice the graceful error handling, and the fact that the output code IS indented as required

2005-09-07 Reply Admin

And a version a slight bit more pythonic (but much more moronic):

def WTFGenerator(maxIndice):
    try:
        maxIndice=int(maxIndice)
    except ValueError:
        while 1:
            print "SET @sequenceCode='ZZ'"
brillant = False
for i in ["IF @sequenceCode='%02d'\n\tSET @sequenceCode='%02d'"%(i,(i+1)%100) for i in xrange(1,maxIndice)]:
    print "%s%s"%(brillant and "ELSE " or "", i)
    if not brillant:
        brillant=True</PRE></BLOCKQUOTE>

For the non-python guys: Python doesn't provide the ternary operator (a?b:c) but the "a and b or c" structure provides a close enough emulation as long as "b" is never computed to False.

2005-09-07 Reply Admin

Anonymous:
In order to prove that I do too much in T-SQL, how about this?:

set @sequenceCode = right( '0' + cast( cast( @sequenceCode as int) + 1 as varchar(3) ), 2)

I think we have a winner.

2005-09-07 Reply Admin

Bustaz Kool:
As far as I can discern, all of the one liners throw an error on invalid input.

And that's a good thing, Mr. Spolsky.

ammoQ · 2005-09-07 Reply Admin

in Oracle, a brillant solution would look like that:

select ltrim(to_char(nvl(min(num),0),'00'))
into sequenceCode
from (select rownum as num from all_objects where rownum<100)
where num>sequenceCode;

The sad part of the story: I've seen programs that really did similar select statements...

2005-09-07 Reply Admin

Anonymous:
Has anyone here ever remarked on the quality of the forum software?

Remarked on what?

2005-09-07 Reply Admin

As people continued to complain that the preview didn't always look like the final result, the author of the forum software just dropped the preview button entirely (at least for unregistered users). What a brillant solution!

Frankly, by now I'm surprised that the URL in my browser's location bar does not contain snippets of SQL.

bullestock · 2005-09-07 Reply Admin

Anonymous:
As people continued to complain that the preview didn't always look like the final result, the author of the forum software just dropped the preview button entirely (at least for unregistered users).

I can confirm that it is also gone for registered users. "If you can't fix it, kill it".

2005-09-07 Reply Admin

kipthegreat:
travisowens:
So what happens when his database needs to support more than 0-100, what about 100,000? That's a whole month of copying and pasting.

That's what interns are for!

And if the intern had an ounce of sense he'd at least write a code generator in [perl|python|VB|whatever] to write out all the cases for him!

2005-09-07 Reply Admin

Anonymous:
Maurits:
Prepwork..

CREATE TABLE sequencecodemap
(
this char(2),
next char(2)
)

INSERT INTO sequencecodemap (this, next)
VALUES ('00', '01')

INSERT INTO sequencecodemap (this, next)
VALUES ('01', '02')

...
snip

You're just trying to get into next week's WTF, right?

hahahha...

Sometimes my boss and I have conversations like this...

me: It's a complicated program, there is a lot of business logic

him: A lot of if/else's, hunh?

me: yes

this (and similiar wtf's) make me wonder how many programmers such as this one he is come into contact with over the years. A little scary.[:^)]

azaris · 2005-09-07 Reply Admin

Mung Kee:
I cannot overstate how truly amazed I am at the number of big stupid switch statements or if...else statements we see here. Once the number of comparisons exceeds a handful, it may be time to start thinking about a more pragmatic solution. I think a lot of developers lack this process of graduated thought.

The problem is, their thought process goes like this:

// Do I have too many cases?

switch (numberOfCases) {
    case 1:
       // not too many
    case 2:
       // not too many
    case 3:
       // not too many
...

Fix The WTF

Leave a comment on “Fix The WTF”