The Daily WTF: Curious Perversions in Information Technology

2010-06-29 Reply Admin

SQLDave:
Jaime:
Tony Janusky:
BrainSlugs83:
Unless you're doing math on a value you shouldn't store or process it as a number. Numbers are for math, and counting, and statistics and such... Not zip codes. Just the fact that you have to add the extra zeros in at display time is wrong, and also, fully qualified zip codes contain non-numeric characters such as the hyphen.

I once wrote a geographic distance approximation server that determines mileage based on zip code deltas. This is how I worked out the formula:

Beverly Hills - Boston: 90210 - 02043 = 88167

Zip distance = 88167 (That's 90210 - 02043) Actual distance = 2605 miles. Ratio = 33.84

That's a pretty good sample to base all other possibilities on, so the function goes:

int distanceBetweenZipcodes( int zip1, int zip2 ) { int zipDist = abs(zip1 - zip2); return (int)((float)zipDist / 33.84F); }

You'd be amazed how accurate it is.
Buffalo, NY (14201) to NYC, NY (10101) -- Calculates to 121 miles. Actual distance is about 350 miles.

Beverly Hills to Ft. Lauderdale (33301) calculates to 1681 miles. Actual distance: 2700 miles.

I'm amazed.

I'm amazed anyone thought I was serious.

2010-06-29 Reply Admin

anonymous:
Rootbeer:
Wizou:
something like (ch >= '0') And (ch <= '9')

What if the database uses a character encoding where the set of Arabic numerals is non-contiguous, like ITA2 Baudot code?

Your version could break!
if if's and but's were candy and nuts, we'd all have a merry christmas.

What if there were no hypothetical questions?

2010-06-29 Reply Admin

Nope, the dev was absolutely right, because ISNUMERIC, in the case of a simple decimal string, will return FILENOTFOUND.

2010-06-30 Reply Admin

ammoQ:
Anonymous:
ammoQ:

On the other hand, it's trivially easy to formaly prove that program B is an 1:1 translation of program A (exactly the same steps in order to perform the same function), while it is generally speaking difficult to impossible to prove that a differently written program B gives the same result like program A. To be on the safe side, requiring an 1:1 translation doesn't seem unreasonable to me.
I see exactly where you're coming from and I agree with your sentiment. But testing a function/program/system should be a complete task in and of itself and it should be possible to do this without ever seeing the code (ie. as a black box). If you're porting a legacy system, for example, you should not be forced to write logically identical code to the old system just because it makes it easier to prove that the new functions the same as the old. You should write the code in the most effective manner provided by your language and then identify any differences through a thorough test schedule.
This assumes that a test for each function/program/system already exists for the old system. Let's say it doesn't. So, while you translate the system from the old language to the new language, you are also writing unit tests along the way. But obviously to write those unit tests, you need a good understanding what exactly the old function did. Chances are, you misunderstand the old program every now and then, and write the new functions and their tests accordingly. Your unit tests perfectly work with the new program, which unfortunately behaves slightly different like the old one. It's up to the integration tests or acceptence tests to find out something is wrong. It could be in any of those 1000s of pesky functions, all of which satisfy their unit tests.

You got it exactly. This is one of the reasons software projects become overdue & over budget.

The quirky stuff will really get to you. Unless you're doing a forklift upgrade, you have to bring the quirks forward or risk^H^H^H^Hguarantee integration problems.

Note: I'm talking biggish projects here. If it's a small standalone, then maybe it's fine. It depends on the scale (and resources, importance, and all that other stuff too).

2010-06-30 Reply Admin

The Real WTF is, of course, asking the question. JFDI.

2010-06-30 Reply Admin

Despite the sheer number of comments, I'm disappointed to see only a few folks mentioning unit tests.

I once was tasked with translating large amounts of TSQL to PL/SQL and (like an idiot) started to do just that - translate. After a few "successful" translations had produced PL/SQL which was a pile of steaming ordure, I learned better. Line by line translation is the kiss of death, as it prevents the use of best methods in the target language (in my case PL/SQL) by simply duplicating what is efficient in TSQL. For example, always opening explicit cursors when an implicit will do, is an excellent way to waste memory and CPU, and remove the inherent checking of an implicit cursor.

Can someone tell me how to resolve this one? My translated code is right, and I corrected a bug in the original in the translation - see attached output from unit tests, demonstrating original bug and new corrected version. Since nobody has unit tests for the original, who (from a position of information and knowledge) is correct? Or, as the supposedly shrewd manager, do you want me to deliberately write buggy code, and are you willing to sign off on any and all liability arising therefrom? Since when has translating a bug been deemed "good programming"?

Answers on a post card to a British postal code involving both letters and digits.

2010-06-30 Reply Admin

Alas, this was not correctly coded if you are at all interested in efficiency. I have needed to perform similar numeric filtering in the past, and this works out to be the fastest, most efficient way:

-- If any character is not in zero to nine IF @barcodeValueSequence LIKE '%[^0-9]%' -- it is WRONG, WRONG, WRONG! BEGIN Select 'Non-Numeric Sequence Provided' Return End

2010-06-30 Reply Admin

Please, allow me.

Your comment seems to assume that the SQL Server is only running labels. If it is performing anything more than this trivial task, then optimization is called for.

Sloppy code kills servers when they should still be capable of handling the load.

2010-06-30 Reply Admin

Tharg:
...snip...
Since when has translating a bug been deemed "good programming"?

Good post. On this point I'd say probably never, but it has often made good business sense. As an example, Wintel was stuck with 16bit code for a long time. If they had gone through and cleaned up all that crusty code, it would have caused all kinds of legacy apps to quit working.

Companies tend to re-evaluate platforms when they are required to replace all their apps on an upgrade. While the buggy code is and was incredibly frustrating, Wintel was able to retain market share.

In your or any particular case, though, the answer is "it depends".

2010-07-01 Reply Admin

Casting a varchar to int is not so straightforward in sql2000 see http://stackoverflow.com/questions/2088642/sql2000-safely-cast-a-varchar256-to-int

excepted solution still jas edge case where it crashes.

i had thought someone would mention the ISNUMERIC(@str+'.0e0') trick to check for integers only.

ammoQ · 2010-07-01 Reply Admin

Tharg:
I once was tasked with translating large amounts of TSQL to PL/SQL and (like an idiot) started to do just that - translate. After a few "successful" translations had produced PL/SQL which was a pile of steaming ordure, I learned better. Line by line translation is the kiss of death, as it prevents the use of best methods in the target language (in my case PL/SQL) by simply duplicating what is efficient in TSQL. For example, always opening explicit cursors when an implicit will do, is an excellent way to waste memory and CPU, and remove the inherent checking of an implicit cursor.
Can someone tell me how to resolve this one? My translated code is right, and I corrected a bug in the original in the translation - see attached output from unit tests, demonstrating original bug and new corrected version. Since nobody has unit tests for the original, who (from a position of information and knowledge) is correct? Or, as the supposedly shrewd manager, do you want me to deliberately write buggy code, and are you willing to sign off on any and all liability arising therefrom? Since when has translating a bug been deemed "good programming"?

Answers on a post card to a British postal code involving both letters and digits.

Unless budget or time contraints say otherwise, I'd propose those steps: a) create a 1:1 translation, stick as close as possible to the original (even when you think this really suuuucks) b) create unit tests for the translated version. For unit tests that fail, use the original program as a reference to verify which one is wrong: the translation or the unit test. c) when all unit tests are satisfied, run the acceptance tests. make sure the new program behaves like it should, i.e. like the old one d) once c) is done, start to refactor the code to make it better (i.e. more like "real" PL/SQL). Of course, the new code must satisfy the unit tests, too e) re-run the acceptance tests to make sure you haven't broken something that the unit tests didn't notice

2010-07-03 Reply Admin

Here's an improved implementation in TSQL.

IF @barcodeValueSequence LIKE '%[^0-9]%' BEGIN SELECT 'Non-Numeric Sequence Provided' RETURN END

2010-07-30 Reply Admin

Mike D.:
Ignoring the whole "ISNUMERIC is the wrong tool" issue (let's be nice and assume there's an ISINTEGER (or a regexp) and it would be used instead), the conversation should have ended like this:
"We just can't run the risk of the new Oracle code behaving any differently than the SQL Server version does. However, you should put your version in there as well, just as comments, so the next guy knows what's going on and doesn't resubmit this to TDWTF."

Shouldn't the original coder have added comments such as "isNumeric isn't precise enough since ...." or "we have to iterate through the individual digits because ...."

That makes me think of....: I know a girl who was a developer for a company. She told me that comments in their code were FORBIDDEN. Why? Probably because some developer might modify the code and not the comments, and thus the comments would confuse more than they'd help.

2012-03-15 Reply Admin

There is no WTF here. This is good practice on behalf of the manager, and highlights Andy's inexperience in the real world. To minimize risk, the code should be ported as close as possible. This validates that SQL Server behaves the same as Oracle. Improvements/refactoring should then be made, and that version compared to the originally ported SQL Server version - this proves the refactoring has worked. If Andy ports and refactors simultaneously and the code doesn't work, there is nothing for the project to fall back on if time constraints dictate.

Warts and All

Leave a comment on “Warts and All”