The Daily WTF: Curious Perversions in Information Technology

2006-01-05 Reply Admin

Anonymous:
Anonymous:
Oh Yeah? Tell it to the register!

This software truly is a WTF

Actually, it's "left as an excercise to the reader to figure out which posts I'm responding to."

olddog · 2006-01-05 Reply Admin

To me, NULL is No.
i.e. . . . '\0' is the the same as 0
Hasn't failed me yet, in 20 years of C

To me: division by zero = NaN. an error. Which is different than NULL. But could equate to zero, based on context. Which might equate to False, Not Specified, Other, or Unknown or anything else that resolves numerically to an insignificant value within the context of a number.

That said, it's still not NULL. NULL is a test for existence, not numeric value.

2006-01-05 Reply Admin

what happens when you want a positive or negative null?

olddog · 2006-01-05 Reply Admin

Anonymous:
what happens when you want a positive or negative null?

LOL...then you'd have a NULL tendency.

2006-01-05 Reply Admin

I thought that was "There 11 kinds of people in the world, those that know how to count in binary and those that don't."

No, No, No

It's:
There are 10 types of people in the world: those that know how to count in binary, those that don't, and those that know real gurus start counting at 0.

2006-01-05 Reply Admin

Well, since the specification says produce a report of all those that _are_ TPS_Reportable, then any value but 1/Yes can be ignored. Anything that's not 'Yes' must be 'No', even if it's NULL or 4/NULL. Life can't be that easy, though, I bet...

Erm, it can be unknown, can it? I guess the data comes from a multiple choice form, with two checkboxes indicating whether it is a tps_reportable...

2006-01-05 Reply Admin

Javelin:
Anonymous:
GoatCheez:
Anonymous:
This reminds me of my favorite IRC quote:

Person A: You can't tell me that there's one bit of difference between a space and a null.
Person B: That's precisely the difference between a space and a null.

not getting the 1 bit difference thing... in ascii at least (what i thought it was refering to) 0 is null and 32 is space... not a bit, but like a bit and then some. technically the only difference is the one bit that is turned on at that spot...i'm probably thinking too hard on this. anyone care to help me out?

There are 10 types of people in this world. Those that understand binary jokes, and those who don't.

Actually, there are 10 types of people:

Those who don't understand binary jokes

Those who understand binary jokes, and think they're clever, because they always assume that any numeric string containing only 0s and 1s must be a base-two representation

Those who understand binary jokes, but think they're flawed, because they realize that a numeric string containing digits from 0 through k could be in any base as long as it's greater than k

Membership in the latter 10 types also corresponds pretty closely to these 10 types:

Those who think the "1 + 1 = 3, for very large values of 1" joke is clever

Those who get the whole "treating a numeric string as a variable" joke, and do think that's clever, but are bothered by the "very large values" part. (No: for a very large value of 1, like, say, a million, the expression equals two million, not 3 (unless we're also using a very large value of 3); it only equals three for one specific value of 1.)

I guess the last type could be further broken down into 10 types, making a total of 10 types:

Those who are perfectly aware of the issues I'm complaining about, but think the jokes are clever anyway (and that I'm being annoyingly pedantic)

Those who understand that nerd jokes only work when stated correctly (and that pedantry can itself be one of the highest forms of humor)

Oh yeah -- self-introduction: been reading for a few months and posted anonymously a couple times; registered a few weeks ago, but didn't actually post until now. Is this where I'm supposed to make my obligatory complaint about the crappy forum software and its lack of a preview button?

There's another type of person you forgot about:

Those who are normal.

brazzy · 2006-01-05 Reply Admin

cconroy:
Anyone else think we should get Paul and Paula together? Maybe she can take over his contract for him?

Hey, hey, Paula...

You want them to BREED??? Are you out of your f*ing MIND?????

2006-01-05 Reply Admin

Actually, it's not as silly as you think. I agree with you that NULL should be NULL, but the people over at Oracle do not. I figure their design team had a whole bunch of open cans of paint laying around when they though that one up. Anyways, if they guy wanted his code to work on Oracle, or a database that does that, then having a separate value for NULL might make sense.

bullestock · 2006-01-05 Reply Admin

GoatCheez:
not getting the 1 bit difference thing... in ascii at least (what i thought it was refering to) 0 is null and 32 is space

No, 0 is NUL in ASCII. NUL is not the same as NULL.

trollable · 2006-01-05 Reply Admin

t-bone:
could someone explain the difference between "not specified", NULL and "unknown"?

Sure ;)
NULL is a well-defined value meaning a lack of value.
Not Specified means something has not yet been specified resulting in a lack of value.
Unknown means the value is unknown at this time and by so lacking.

ammoQ · 2006-01-05 Reply Admin

Anonymous:
Actually, it's not as silly as you think. I agree with you that NULL should be NULL, but the people over at Oracle do not. I figure their design team had a whole bunch of open cans of paint laying around when they though that one up. Anyways, if they guy wanted his code to work on Oracle, or a database that does that, then having a separate value for NULL might make sense.

What do you mean? The implicit "primary key is not null" constraint in Oracle?

2006-01-05 Reply Admin

The real WTF was that the data was trashed because the FK constraint was not in place so there is no integrity, which means I could never be sure if I was getting the correct results to the right question. It looked like that, as the applications using this data progressed over the years different developers had different thoughts on how to deal with 'missing' or unknown values, and never went back and fixed the data up to comply with their new way of dealing with the two extra states. Hence the -1 values found and the weird values in the lookup table. I'm guessing that several other apps which used this same database also had their own unique way of dealing with the same problem, thus the seemingly duplicate logic.

The secondary WTF and a very common one, is that the TPS_Reportable attribute was nullable.. nulls are just a WTF in general TBH, as they do not comply with the relational model (which is based on 2 values logic, not 3), and tables with nullable attributes don't meet 1NF. SQL Standards say that Null is not a value, but a flag to indicate that the value is unknown, 1NF says that all values for a given attribute must be of the same type. Null is not a value (as indicated by the SQL standards), and even if it was it would not be of the same type as the attribute (Null is not an integer, char or anything else)

My only thoughts with the weird lookup table was at some point someone decided that they wanted to be able tell the difference between unknown value. unspecified and not applicable (I believe Codd has a few proposals for the relational model many years back for 3, 4 valued logic, but he rejected them himself and C.Date has since always specified that the relational model deals only with 2vl). The unknown, not applicable problem can simply be got around with a good logical design.

As it turned out, if the TPS_Reportable was 'yes', then a series of other fields were also populated (about 10). so if the case was TPS_Reportable, then it wasn't just a plain old 'case' but a TPSReportableCase, and should have been 'promoted' to it's own table, with a 1...0 constraint back to the Case table.

This would have then made the query really simple, just select all tuples / rows from the ReportableTPSCase and join back onto the case table to get any necessary attributes.

ohh well, for the record this was not a goverment agency, but a financial one...After three weeks I vanished.

masklinn · 2006-01-05 Reply Admin

chrismcb:
hotzenplotz:
p.s.: the ASCII 'NUL' also is not NULL -- e.g. I can store an ASCII 'NUL' into a char or varchar field. That field would NOT be NULL! And if you think you're smart, just try it.

I have a donut hole

Rumor has it quotes work in IE...

Fact has it quotes work in Firefox, for I do use Firefox and quotes do usually work for me (Murphy's Law will probably bork that one though, just to prove me wrong)

2006-01-05 Reply Admin

Anonymous:
Erm, it can be unknown, can it? I guess the data comes from a multiple choice form, with two checkboxes indicating whether it is a tps_reportable...

Depends on which app the user is using. Some apps it is a button, others display the full list of possible values from the lookup table, others displayed Yes, No...

I found that all the applications populated the field with diffrent values, (somce had hardcoded values... eg tps_reportable = 1, others used the values in the lookup table..)

Also, if no was selected, the status of the Case was not backtracked, so if you used the status field for any reporting it would still yell you it had been submitted to the TPS organisation, and the remaining tps_reportable fields were not wiped (eg, the TPS contact etc)

haveworld · 2006-01-05 Reply Admin

Anonymous:
It's so simple. The numbers: 0, 1, 2, 4 are all powers of 2

2 to the power of what equals 0?

Sabalon · 2006-01-05 Reply Admin

Anonymous:
As long as he remembers the new cover sheets for the TPS Reports, he'll be fine.

That was the first thing I thought when I started reading this one.

For some reason this one reminds me of "2+2=5 for very large values of 2"

darjien · 2006-01-05 Reply Admin

haveworld:
Anonymous:
It's so simple. The numbers: 0, 1, 2, 4 are all powers of 2

2 to the power of what equals 0?

Why, minus infinity of course :)

A fine number, with very few drawbacks.

Then again, if we're being that loose, 10 is also a power of 2. Though this has the ability to be true for integral powers for some bases.

darj

hank miller · 2006-01-05 Reply Admin

You all do realize that the table TN_Lookups is a foreign key for many different tables, not just the cases given here. Paul just hadn't has time in the contract to look at the rest of the schema to see them.

At one time I was naive enough to believe the above statement can only be made as a joke. I'm now convinced that it is fully correct, even though I have never worked there.

haveworld · 2006-01-05 Reply Admin

darjien:
haveworld:
Anonymous:
It's so simple. The numbers: 0, 1, 2, 4 are all powers of 2

2 to the power of what equals 0?

Why, minus infinity of course :)

A fine number, with very few drawbacks.

Then again, if we're being that loose, 10 is also a power of 2. Though this has the ability to be true for integral powers for some bases.

darj

You can find the limit of 2^-X as X approaches INFINITY but I don't think that you could say that 2^-INFINITY = 0.

2006-01-05 Reply Admin

I know

null is sorta

2006-01-05 Reply Admin

dsadas

Satanicpuppy · 2006-01-05 Reply Admin

hank miller:
You all do realize that the table TN_Lookups is a foreign key for many different tables, not just the cases given here. Paul just hadn't has time in the contract to look at the rest of the schema to see them.
At one time I was naive enough to believe the above statement can only be made as a joke. I'm now convinced that it is fully correct, even though I have never worked there.

I've worked as a DBA for long enough to have seen every conceivable bit of crap, but I'm not sure this one IS crap. Had one guy call me up screaming because a lookup table I'd made had a zillion duplicate values...I suppose he'd have liked it better if I'd had a different lookup table for every tiny dataset I needed to use in a comparison or dropbox. If he'd taken the time to actually look at the table, he'd have noticed that they were all arranged in distinct sets, so that while "SELECT DISTINCT value FROM tlookup" would give a ton of garbage, "SELECT * FROM tlookup ORDER BY group_id" would give a long list of intelligent, discrete, lists. If he'd wanted a particular one, all he had to do was use the group_id to get the list he needed.

In my experience, this is the most common way to deal with a lookup table in a large project...It's a little more time consuming to pull your data from the lookup table, but the convenience of always knowing which table to pull FROM makes up for it.

I'd want to see the actual data on this one. It could be that the programmer is just assuming that the lookup table is only being used for one purpose when it's being used for several, and a better formed query would give a more rational dataset.

intchanter · 2006-01-05 Reply Admin

Yes, the byte is different, but only by one bit:

NULL = 0x00 (0 decimal) or 00000000
Space = 0x20 (32 decimal) or 00100000

This holds for most modern systems using a 7- or 8- bit character set, but is an arbitrary convention.

01001001 01101110 01110010 01100011 01101000 01100001 01101110 01110100 01100101 01110010

2006-01-05 Reply Admin

Satanicpuppy:
hank miller:
You all do realize that the table TN_Lookups is a foreign key for many different tables, not just the cases given here. Paul just hadn't has time in the contract to look at the rest of the schema to see them. At one time I was naive enough to believe the above statement can only be made as a joke. I'm now convinced that it is fully correct, even though I have never worked there.

I've worked as a DBA for long enough to have seen every conceivable bit of crap, but I'm not sure this one IS crap. Had one guy call me up screaming because a lookup table I'd made had a zillion duplicate values...I suppose he'd have liked it better if I'd had a different lookup table for every tiny dataset I needed to use in a comparison or dropbox. If he'd taken the time to actually look at the table, he'd have noticed that they were all arranged in distinct sets, so that while "SELECT DISTINCT value FROM tlookup" would give a ton of garbage, "SELECT * FROM tlookup ORDER BY group_id" would give a long list of intelligent, discrete, lists. If he'd wanted a particular one, all he had to do was use the group_id to get the list he needed.

In my experience, this is the most common way to deal with a lookup table in a large project...It's a little more time consuming to pull your data from the lookup table, but the convenience of always knowing which table to pull FROM makes up for it.

I'd want to see the actual data on this one. It could be that the programmer is just assuming that the lookup table is only being used for one purpose when it's being used for several, and a better formed query would give a more rational dataset.

no, really.. it is crap. for many reason.

The text field in the lookup table is just that a field displayed to the user and is thus changeable (say for example, the marketing people don't like 'yes' and 'no' and want 'true', 'false' or 'affirmative', 'negative'. The Id attribute is an integer and auto generated and so cannot be relied upon.

Secondly, the Id attribute on the lookup relation is meaningless and should not exist, and it forces a pointless join. If there was a 'lookup code' field on the table, which was the PK, which held values such as TRUE, FALSE, UKNOWN, UNSPECIFIED then selecting all reportable cases would be just a case of saying where field='TRUE', and not having to a pointless join on a meaningless key .. (Surrogate keys seem to be another common and accepted WTF for some reason).

Lookups tables are a general WTF in database design, as the model looses meaning and clarity because we loose the semantics of the field having a specific user defined domain (type). If the attribute TPS_REPORTABLE was defined as domain BOOL for example, and BOOL was defined as a domain, with a pool of values constrained to Yes and No, then we bring clarity and meaning to the model.

As tps_reportable was an FK(not enforced) to a general lookup table, what we are saying is that the allowable values for tps_reportable can be anything from 'yes', 'no' to File Not Found and any other value found in the lookup table.

Lookup tables break 1NF because what you are saying is that values from different domains are stored against the same 'generic' type are and can only be distinguished by looking at another attribute called 'group_id'

the only way to know is to have some special knowledge about the system (and this is normally in some application layer, and not with the data)

2006-01-05 Reply Admin

> You can find the limit of 2^-X as X approaches INFINITY but I don't think that you could say that 2^-INFINITY = 0.

No i'm pretty sure that 2^-INFINITY = 0 for very large values of INFINITY

2006-01-05 Reply Admin

Anonymous:

Produce a report that will list the Case Number, Officer Name, and Days Open for all remote offices where the case is TPS Reportable. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

This is just a thought that I came up within 30 seconds when I first looked at the problem.<o:p></o:p>

SELECT * From OfficeDetails where Is_TPS_Reportable = 1; or<o:p></o:p>

SELECT * From OfficeDetails where Is_TPS_Reportable = "yes"; <o:p></o:p>

Depends on value that is actually stored there. Who cares if someone placed Null in text field in some other part of the data abase. If you think there is a problem, mention it to them but I fail to see how this is complicated. <o:p></o:p>

You haven't worked on many projects with trash databases like this one, have you? While you're answer may be the correct one, you really don't know since the contstraint has obviously not been enforced and they obviously aren't using the defined values. There is a good chance that there are other values in the column that also mean 'yes' or that 1 doesn't mean 'yes' at all. To further complicate things, the meaning of the column could depend upon any number of things like other columns in the table, when it was entered, what office it was entered by, or what person entered it. Depending upon what the values in the column actually mean, a problem like this can get quite complicated. Depending upon the number of records one is dealing with, and the amount of non-uniformity in what the values mean, the programmer could spend hours or days just trying to determine what the customer means by a case being 'TPS Reportable.'

Although this problem may be as trivial as you say, don't underestimate its potential to create chaos. Besides, the values defined in their code table is a WTF in and of its self.

2006-01-05 Reply Admin

Other than the nullability, this is an excellent design. Evidently, determining whether the case is reportable is very important to this organization. Report the information and call it a day.

Javelin · 2006-01-05 Reply Admin

Anonymous:
There's another type of person you forgot about:

Those who are normal.

No, I included them:

Javelin:

Those who don't understand binary jokes

2006-01-05 Reply Admin

Anonymous:

Produce a report that will list the Case Number, Officer Name, and Days Open for all remote offices where the case is TPS Reportable. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

This is just a thought that I came up within 30 seconds when I first looked at the problem.<o:p></o:p>

SELECT * From OfficeDetails where Is_TPS_Reportable = 1; or<o:p></o:p>

SELECT * From OfficeDetails where Is_TPS_Reportable = "yes"; <o:p></o:p>

Depends on value that is actually stored there. Who cares if someone placed Null in text field in some other part of the data abase. If you think there is a problem, mention it to them but I fail to see how this is complicated. <o:p></o:p>

Wow, I'm sure the seasoned professional wasn't aware he could use multiple SELECT statements.... I think you're missing the wtf -

A) Is_TPS_Reportable is an unconstrained foreign key
B) Is_X is usually indicative of a boolean... or at worst, a boolean column which allows NULL.
C) 6 values does not a boolean make.

sao · 2006-01-05 Reply Admin

Anonymous:
> You can find the limit of 2^-X as X approaches INFINITY but I don't think that you could say that 2^-INFINITY = 0.

No i'm pretty sure that 2^-INFINITY = 0 for very large values of INFINITY

hehe, just as i had lost all hope in this thread - something that made me actually laugh.!! good one!

2006-01-06 Reply Admin

Anonymous:
As long as he remembers the new cover sheets for the TPS Reports, he'll be fine.

Um. Yeah. Well, I'm going to have to go ahead and forward you a copy of that memo.

olddog · 2006-01-06 Reply Admin

Anonymous:
Anonymous:
As long as he remembers the new cover sheets for the TPS Reports, he'll be fine.

Um. Yeah. Well, I'm going to have to go ahead and forward you a copy of that memo.

Initech has had this problem for some time - as we all know. The only way to solve this, is to work the weekend.

2006-01-09 Reply Admin

He probably meant null as in 0 (zero)

Eks:

00110000 compared to
00100000

2006-01-09 Reply Admin

Anonymous:
OK, this is F'd up, but its not really difficult - just take the rows where the value is 1 ('Yes") and ignore everything else. Sometimes a little judgement goes a long way, Paul! ;-)

But some of the "unknown" rows might actually be "Yes"--the person who keyed it in just wasn't sure and set it to unknown. So it need to take all the rows where the value is Yes, plus a random selection of the unknown rows.

Otto · 2006-01-09 Reply Admin

EvanED:
Otto:

"Unknown" clearly implies that the value which should be in the field is not known by any mortal agency. While "not specified" implies that the value is known, it is simply not given here, possibly because the user could not possibly comprehend it.

It's not a matter of comprehension, because if you can't comprehend something, it can hardly be said that you know it and, hence, it would be unknown, so that falls into the previous category.

I only said that the value is in fact known by some mortal agency. It is not specified for, oddly enough, unspecified reasons. One of those reasons is possibly that the user making the query would not comprehend the answer they clearly seek.

I do, however, agree with your suggestion that a new table of reasons needs to be added along with a foriegn, or possibly even domestic (given national security and all), key by which the unspecified "not specified" reason can be specified in greater detail.

hotzenplotz · 2006-01-09 Reply Admin

>> coincidentally, "Null" is the German word for Zero. <<

No, it's no coincident. "NULL" reads "NULL" _because_ Null is the german word for zero.

And to the NULL != NULL thingy -- that makes perfect sense. Of course it doesn't apply everywhere, but that again makes perfect sense. After all you wouldn't want NULL != NULL for DISTINCT or IN...

2006-01-11 Reply Admin

olddog:
Anonymous:
Anonymous:
As long as he remembers the new cover sheets for the TPS Reports, he'll be fine.

Um. Yeah. Well, I'm going to have to go ahead and forward you a copy of that memo.

Initech has had this problem for some time - as we all know. The only way to solve this, is to work the weekend.

Wow thank god, I thought somebody totally missed the office space hints on here.. LoL !

2006-01-25 Reply Admin

 | 2  | No            |
 | 3  | Not Specified |
 | 4  | NULL          |
 | 5  | Other         |
 | 7  | Unknown       |

Yes, it may seem somewhat overly nuanced, but I'm sure they had a rigorous procedure for deciding when to use which.

2006-01-25 Reply Admin

Alex Papadimoulis:
 | 3  | Not Specified |

I like how you have to specify a value when you want to say "not specified".

Yessssssss! Noooooooo!

Leave a comment on “Yessssssss! Noooooooo!”