The Daily WTF: Curious Perversions in Information Technology

2014-01-30 Reply Admin

inserto into conv (uid, type, subtype, convtext, convvalue) values (1, 0, 0, 'frist', 0);

2014-01-30 Reply Admin

Please tell me there's another table which contains an ASCII look-up table, so you can de-normalize entire paragraphs of text...

henke37 · 2014-01-30 Reply Admin

The issue wasn't so much the existence of the table, but the implied use of it.

Such a pity that the article didn't actually tell us how it was being used.

2014-01-30 Reply Admin

In my experience, asking app devs to do something differently for the sake of the dB rarely gets anywhere fast. So he was working around a bad situation. Given the initial implication that not all the queries are SPs, I was expecting the story to end with "and after he removed the table, thinking he knew better, all the adhoc queries broke".

2014-01-30 Reply Admin

Wait....that table is an integral part of multilanguage support...

2014-01-30 Reply Admin

Sadly, Bill died suddenly of a heart attack. Unlike Gandalf, Bill did not return in white, and Alan was promoted to DBA.

Eerily, that happened in one of my project once. In that case the good wizard died and the forces of evil (the other DBA's) took over.

2014-01-30 Reply Admin

Keep your fingers crossed that Bill didn't build a many-coloured clone of his personality into the database that will crawl out of its digital coffin a couple of months after he touched the system the last time!

2014-01-30 Reply Admin

Yea, but what if the words for some of those numbers changes some time later, in the English language? Then what are you going to do?!? Programs, as you know, are unmaintainable and untestable. And you can't rely on programmers to do anything right. So you're really up a creek unless you put everything in the database!!!

;->

2014-01-30 Reply Admin

the article:
It took Alan the better part of the day to remove all references to the table before deleting it

So, from now on, "20" will remain "20" instead of "twenty" and "fortytwo" will remain "fortytwo" instead of "42"?

Or did he write a hard-coded lookup table in whatever language the front-end development team used?

IMHO the existence of the table alone is not necessarily a wtf; it would be interesting to see how it was used.

A simple "SELECT ConvValue FROM conversions WHERE ConvText = 'twenty';" would probably be ok; a "SELECT * FROM conversions" and then programmatically extracting the conversions would be a major wtf.

2014-01-30 Reply Admin

Not to forget: what do Type and SubType mean? Did they have any meaning? If yes, how did Alan work around that when he dropped the table?

TGV · 2014-01-30 Reply Admin

I'm curious how he filled the table in the first place.

2014-01-30 Reply Admin

Usually it's the Dev complaining about being forced to used stored procedures, rather than the DB Admin complaining that it's too slow.

values (1, 0, 0, 'frist', 0)

I'm Frist-er than you!

[image]

2014-01-30 Reply Admin

TGV:
I'm curious how he filled the table in the first place.

Developer: "Hey, I've stumbled across this new word, 'nine-hundred-and-forty-seven'. Do we already have a number for it?" Bill: *does a quick select on the console* "Nope! Hang on, I'll just insert it! Done, there you are!" Developer: "Thank you, Bill!"

TheCPUWizard · 2014-01-30 Reply Admin

faoileag:
TGV:
I'm curious how he filled the table in the first place.
Developer: "Hey, I've stumbled across this new word, 'nine-hundred-and-forty-seven'. Do we already have a number for it?" Bill: *does a quick select on the console* "Nope! Hang on, I'll just insert it! Done, there you are!" Developer: "Thank you, Bill!"

Looking at the small snippet (And the clue of sub-type) it was probably 3 lookups into the table...(at least I hope so)

2014-01-30 Reply Admin

So nine-hundred would be a type, forty a sub-type and seven a mere whatever. So that's three queries and some parsing of '-' and 'and' and afterwards some adding of queried values? It would be nice to localize this to Germany, where forty-two is 'zwei-und-vierzig' which makes sub-types somewhat impossible (even though types would work).

2014-01-30 Reply Admin

Just a day? That's the WTF -- unless it's a super-small trivial application, that's barely any time at all, which is worrying. Where's his due diligence?

2014-01-30 Reply Admin

Jens:
So nine-hundred would be a type, forty a sub-type and seven a mere whatever. So that's three queries and some parsing of '-' and 'and' and afterwards some adding of queried values? It would be nice to localize this to Germany, where forty-two is 'zwei-und-vierzig' which makes sub-types somewhat impossible (even though types would work).

Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW). Don't blame me, I didn't invent French. But hey! this similar problem can be found in some other areas, like time and date to word string conversion.

2014-01-30 Reply Admin

Jens:
So nine-hundred would be a type, forty a sub-type and seven a mere whatever.

Not necessarily. We don't know for what number "Type" might be something other than 0. Roman numbers? Could be possible...

Also that multiples of 10 qualify for SubType doesn't hold since 10 has the SubType 0.

Jens:
where forty-two is 'zwei-und-vierzig' which makes sub-types somewhat impossible (even though types would work).

No, not really. Both systems are easy enough: English: 1000s-100s-10s-1s German: 1000s-100s-1s-10s And both schemes are consistent through natural numbers (with the ususal exceptions of 11 and 12).

More problematic are languages that still work on the old (babylonian?) system based on 20: Danish: 90 ("halvfems", basically "halv between 4 times 20 and 5 times twenty") French: 90 ("Quattre vingt dis", basically "4 times 20 and ten")

The msystery of the Type and SubType... will we ever solve it?

2014-01-30 Reply Admin

ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW)

That wouldn't be hard (simply have 80 = 'quatre-vingt'). The trickiness might be with 70s and 90s (eg 72 is "sixty twelve"), but depending on how that table is used, that might be OK if you omit the subtype=1 entry for 70, and specify what 14-19 are as well.

As long as the ordering is like English (big-endian) it should be OK. It is German that would mess it up because that's counter-middle-endian)

2014-01-30 Reply Admin

faoileag:
Also that multiples of 10 qualify for SubType doesn't hold since 10 has the SubType 0.

I reckon subtype 1 are simply numbers which can have subtype 0 numbers after them. (You don't say 'ten three', thus 10 is a subtype 0).

So, if your number is X:

look for a subtype 1 number A at least as big as X
use the text associated with A
look for a subtype 0 number B at least as big as (X-A)
append the text associated with B

As hundreds/thousands/etc are regular (in languages like English, French, German etc), then you work those out using the above scheme, rather than having more types/subtypes.

One problem is that 14-19 aren't in the table, so I presume those must be worked out algorithmically as well (thirteen is the exceptional 'teen' because the 'thir' is not the same as 'three', but fourteen to nineteen can use the X-10 number before the 'teen')

2014-01-30 Reply Admin

QJo:
Just a day? That's the WTF -- unless it's a super-small trivial application, that's barely any time at all, which is worrying.

Actually, even with a super-large application, this could be done in a couple of hours (say: two), if (very big if) the frontside developers had been clever enough to wrap the database call in a "int translate(string word)" function.

But obviously they didn't.

2014-01-30 Reply Admin

This really isn't an established WTF until we find out how the table was being used in the first place. Were numbers being converted from their written form to their decimal form, or vice-versa? Were numbers being stored fully written out, and then converted back and forth when calculations were required?

Without some important facts there's reasonable doubt that this is a WTF at all.

2014-01-30 Reply Admin

Rudolf:
One problem is that 14-19 aren't in the table, so I presume those must be worked out algorithmically as well (thirteen is the exceptional 'teen' because the 'thir' is not the same as 'three', but fourteen to nineteen can use the X-10 number before the 'teen')

Fiveteen says hi.

2014-01-30 Reply Admin

Pock Suppet:
Rudolf:
One problem is that 14-19 aren't in the table, so I presume those must be worked out algorithmically as well (thirteen is the exceptional 'teen' because the 'thir' is not the same as 'three', but fourteen to nineteen can use the X-10 number before the 'teen')
Fiveteen says hi.

Eightteen gives Fiveteen a high five.

2014-01-30 Reply Admin

Heart attacks are TRWTF, right?

2014-01-30 Reply Admin

frist for 1ce

2014-01-30 Reply Admin

Anonymous:
frist for 1ce

Since you are late anyway, you could at least have exploited the theme of today's article: "frist for " + (SELECT ConvText FROM conversions WHERE Uid = 2;) + "ce"

boh · 2014-01-30 Reply Admin

ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW).

Or localize to the English language, where "four score and three" means 83.

Zecc · 2014-01-30 Reply Admin

ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW). Don't blame me, I didn't invent French. But hey! this similar problem can be found in some other areas, like time and date to word string conversion.

And 92 is "quatre vingt douze", or "four twenties and twelve".

TGV · 2014-01-30 Reply Admin

Zecc:
ZoomST:
Don't blame me, I didn't invent French. But hey! this similar problem can be found in some other areas, like time and date to word string conversion.
And 92 is "quatre vingt douze", or "four twenties and twelve".

That's why French will be slowly assimilated. First, you'll start hearing "novente deux" for 92, and before you know it, the British will have control over the force de frappe.

2014-01-30 Reply Admin

boh:
ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW).
Or localize to the English language, where "four score and three" means 83.

And 36 must be rendered as "3 dozen".

PedanticCurmudgeon · 2014-01-30 Reply Admin

henke37:
Such a pity that the article didn't actually tell us how it was being used.

That falls in the category of questions you don't bother asking because you know in advance that the answer will be a WTF.

JimM · 2014-01-30 Reply Admin

A naive SELECT statement took five minutes, returning the following.

A SELECT statement on one table took five minutes? What were they running it on, an Atari 2600? Did someone have to fetch the right wax memory drum from the basement?

I call BS ... without even getting on to the table itself...

zelmak · 2014-01-30 Reply Admin

PedanticCurmudgeon:
henke37:
Such a pity that the article didn't actually tell us how it was being used.
That falls in the category of questions you don't bother asking because you know in advance that the answer will be a WTF.

I'll bet they were doing something like printing checks (checques(?) for you Euro's)

$1344.56 => ONE-THOUSAND, THREE-HUNDRED, FORTY-FOUR and 56/100ths US Dollars

2014-01-30 Reply Admin

Zecc:
ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW). Don't blame me, I didn't invent French. But hey! this similar problem can be found in some other areas, like time and date to word string conversion.
And 92 is "quatre vingt douze", or "four twenties and twelve".

The Real WTF is the French, amirite?

slavdude · 2014-01-30 Reply Admin

In English, only 31-99 are hyphenated when spelled out, as are fractions.

[/pedantry]

Thrillho · 2014-01-30 Reply Admin

TGV:
That's why French will be slowly assimilated. First, you'll start hearing "novente deux" for 92, and before you know it, the British will have control over the force de frappe.

The Swiss French already use "septante" and "nonante" for 70 and 90, but they don't seem to have taken over anything yet.

2014-01-30 Reply Admin

zelmak:
PedanticCurmudgeon:
henke37:
Such a pity that the article didn't actually tell us how it was being used.
That falls in the category of questions you don't bother asking because you know in advance that the answer will be a WTF.

I'll bet they were doing something like printing checks (checques(?) for you Euro's)

$1344.56 => ONE-THOUSAND, THREE-HUNDRED, FORTY-FOUR and 56/100ths US Dollars

Sorry, what's a check (or checque)?

Nutster · 2014-01-30 Reply Admin

Anom nom nom:
inserto into conv (uid, type, subtype, convtext, convvalue) values (1, 0, 0, 'frist', 0);

Error: Key conflict attempting to insert record 1. Transaction cancelled.

2014-01-30 Reply Admin

I'm with the people betting on localization. Type would be language, SubType would be locale. In which case you would likely have mapping tables: either a Type and SubType table, or just a Language table. eg:

Type SubType i18nCode Description
---- ------- -------- ---------------------------
0    0       EN_US    American English
0    1       EN_CA    Canadian English
0    2       EN_GB    British English
1    1       FR_CA    Canadian French
1    3       FR_FR    French (France)

etc...

As others have said, these days I would rely on the application layer for internationalization requirements, but I would point out that i18n and l10n standards are relatively recent (mid-90s.) They didn't get into major languages and common usage for a while after that. That might seem like a long time ago, but non-IT companies look at legacy software with the attitude of "If it ain't broke, don't fix it." (Which is why there's still lots of Cobol code from the 80s still running.)

Also, if you have multiple languages, or even language versions, in your app layer, there's no guarantee they'll all provide the same level of localization support. If you centralize it in your database, at least you know every app will get the same localization results. (My coworker is shaking his head at my multiple languages comment. I'll type out the example I'm giving him: J2EE providing web services + a few Sharepoint sites + some PHP or ColdFusion or ASP websites + some desktop applications in C++ or VB or C# or whatever. It's a more common situation than a company with every single system running on only one version of one language.)

DCRoss · 2014-01-30 Reply Admin

Young Euro Guy:
Sorry, what's a check (or checque)?

It's what the Americans call a cheque when they don't have any balances around.

So it's a Freedom Cheque.

2014-01-30 Reply Admin

PS: If any developer on my team created a TEXT column where a VARCHAR would do, I would give them grief too. TEXT columns trade capacity for efficiency - you can store a couple of GB worth of text in there, but they can't be indexed, require custom code for searching, and require extra lookups when retrieving (due to the row just holding a pointer to the TEXT location rather than the actual value - like it does for all other columns.)

Re-reading this post, it sounds more like a junior DBA, with no knowledge of the history of a database, complaining that the previous guy had the temerity to die before he fully documented the system. It's more sad than funny.

cellocgw · 2014-01-30 Reply Admin

TGV:
before you know it, the British will have control over the force de frappe.

What's that: a milkshake for Darth Vader?

2014-01-30 Reply Admin

slavdude:
In English, only 31-99 are hyphenated when spelled out, as are fractions.
[/pedantry]

Forgotten you about twenty-one, twenty-two, ...?

[email protected] · 2014-01-30 Reply Admin

Rick:
PS: If any developer on my team created a TEXT column where a VARCHAR would do, I would give them grief too. TEXT columns trade capacity for efficiency - you can store a couple of GB worth of text in there, but they can't be indexed, require custom code for searching, and require extra lookups when retrieving (due to the row just holding a pointer to the TEXT location rather than the actual value - like it does for all other columns.)
Re-reading this post, it sounds more like a junior DBA, with no knowledge of the history of a database, complaining that the previous guy had the temerity to die before he fully documented the system. It's more sad than funny.

That really depend on the database server you use. Most don't have any of the problems you mention and treat varchar and text the same.

Nutster · 2014-01-30 Reply Admin

zelmak:
PedanticCurmudgeon:
henke37:
Such a pity that the article didn't actually tell us how it was being used.
That falls in the category of questions you don't bother asking because you know in advance that the answer will be a WTF.

I'll bet they were doing something like printing checks (checques(?) for you Euro's)

$1344.56 => ONE-THOUSAND, THREE-HUNDRED, FORTY-FOUR and 56/100ths US Dollars

I did something like this for a project I did for one of my Canadian clients (actually all my clients are Canadian), where both the numeric value and the value words in English words had to printed on a contract, so I made a function that took the calculated numeric value, read the "Number_Names" table and determined what the words were. When they started a branch in Quebec, where everything had to be en français, I just put in the number names and it worked great.

Yes, I did have to give all the numbers from 0 to 20. After that it knew to just combine the digit places. 123 = 100 + 20 + 3 = One Hundred Twenty Three. I do not remember if I had to do anything different for 70's in French.

locallunatic · 2014-01-30 Reply Admin

boh:
ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW).

Or localize to the English language, where "four score and three" means 83. And 36 must be rendered as "3 dozen".

Would that mean that 39 should be "3 baker's dozen"?

2014-01-30 Reply Admin

[email protected]:
Rick:
PS: If any developer on my team created a TEXT column where a VARCHAR would do, I would give them grief too. TEXT columns trade capacity for efficiency - you can store a couple of GB worth of text in there, but they can't be indexed, require custom code for searching, and require extra lookups when retrieving (due to the row just holding a pointer to the TEXT location rather than the actual value - like it does for all other columns.)

That really depend on the database server you use. Most don't have any of the problems you mention and treat varchar and text the same.

Well, MS SQL Server, Sybase, MySQL and Oracle (they call it CLOB) all work like I described. What database server are you thinking of? Or perhaps you're confusing varchar(max) with regular varchar?

2014-01-30 Reply Admin

locallunatic:
boh:
ZoomST:
Or localize to French language, where 83 is "quatre-vingt-trois", that means "4 times 20 and 3". And no, 62 is not "trois-vingt-deux" (3 times 20 and 2), is "soixante-deux" (sixty-two BTW).

Or localize to the English language, where "four score and three" means 83. And 36 must be rendered as "3 dozen".
Would that mean that 39 should be "3 baker's dozen"?

And 42 as 9 * 6.

2014-01-30 Reply Admin

The wizardly nature of Bill is clearly evidenced by his ability to speak in monospaced font.

CAPTCHA: abico - An abacus having all the beads replaced with a carving of the number they represent.

Thanks Bill!

Leave a comment on “Thanks Bill!”