The Daily WTF: Curious Perversions in Information Technology

John Bigboote · 2006-01-20 Reply Admin

nobody:
> Sybase ASE does not support nullable BIT datatypes. > Go ahead, ask me how I know this.
how do you know this?

Because I have been working with Sybase, the RC Cola of Enterprise RDBMS, for the last 5 1/2 years. From writing code that I am ashamed of in order to get around driver bugs to accepting fundamental weaknesses in the DB engine itself (an INT identity column? Who would ever want that?!?), Sybase ASE has crushed my spirit.

Hey, you asked.

Luckily, we're in the middle of a full conversion to SQL Server.

Phill · 2006-01-20 Reply Admin

Alex Papadimoulis:

JuJuBe:

By this logic, most programming languages are tri-value, but that doesn't excuse the fact that a boolean is T/F (on or off). To say that NULL is a valid boolean value is to miss the point entirely.

What does this evaluate to?:

boolean myBool;

Does that make this a valid boolean from a systems perspective? That is why this triv-value stuff is bunk. There are always constraints you need to put on a system to make it work. In this simple case, you need to define myBool to some initial value to prevent nulling out. In the above case, a simple NOT NULL does the trick.

Depending on the language, this would evaluate to the default value (.NET, Java, etc), or a random value, based on whatever bits happened to be flipped in the memory space allocated for the variable (C, etc). The same holds true with ...

int myInt;
cout >> myInt;

... prints an possibly random, but still a valid integer value.

Think of Nullable datatypes as ones where the IsNull bit is stored in the symbol table and not represented as some combination of bits in the variables memory space. Very languages have Null represented this way and have to resort to ugly "hacks" to implement the logic: creating a IntClass with an internal integer and a getter/setter for IsNull and Value, boxing all value-types and having a separate object to represent null, and so on ...

You've just reminded me of the main reason I hate C. You are quite right about what you say about the boolean being set to a default value ( false in Java and C#). However, if the variable is defined in a local scope any attempt to use it without assigning a value will result in a compiler error (in Java and C#).

C# version 2 has come up with this syntax for nullable ints:

int? myInt;

This allows the full range of valid integer values and null to be assigned to the variable.

Anonymoose · 2006-01-20 Reply Admin

Anonymous:
Anonymous:

Are you serious? A boolean is now a tri-value? Wow.

Precisely to prevent that kind of confusions, that's the reason I always use quaternary boolean logic: true, false, null and undefined.

I prefer to use a float for my booleans:

Court martial in progress:
Judge: Are you positive about that statement?
Cmdr Zap Brannigan:Absolutely, positively, 99.9% sure!

2006-01-20 Reply Admin

LuciferSam:
Boolean values are over used anyways. The flexibility of a character or string type is much preferred.

example: Why use just True or False when one could use, 1, 0, T, F, Y, N, null, FileNotFound, and Purple all as valid values!

/sarcasim

FileNotFound is always a good candidate for any solution :-))

2006-01-20 Reply Admin

You know, that was the first thing that popped into my mind too...

ferrengi · 2006-01-20 Reply Admin

emptyset:
Alex Papadimoulis:
"green"/"red".

strings are more expressive than puny, weak bits. this is what happens when you demote a comptroller. never demote a comptroller. revenge assumes many chloroforms.

Hey! You're back and as cryptic as ever!
Where've you been?

2006-01-20 Reply Admin

dhromed:

I do not fully understand why null is not UNequal to a value such as 'N' or 'Y' or 'Cucumber', since it is obviously not equal to them.

If the field can hold:

N
Y
null

Then why does [WHERE field <> 'N'] only select those where [field = 'Y'] and ignores the nulls as though they were equal to 'N'?

It's because a comparison to a null is null since null means there is no value and you cannot compare it to any other value. Ergo, null <> 'N' is null. null is not true, so the comparison fails.

WTF Batman · 2006-01-20 Reply Admin

dhromed:

If the field can hold:

N
Y
null

Then why does [WHERE field <> 'N'] only select those where [field = 'Y'] and ignores the nulls as though they were equal to 'N'?

Because NULL is not a value. It's an "absence of value". You'll notice that if you instead query for field=N, the NULL fields will also be excluded, "as though" they were equal to Y.

Notice the flaw in the "as though" logic. I'll use numbers, but remember that NULL is not a value, but the same logical principle applies:

values are:

1
2
3

where field = 1 -- ignores 2 and 3
where field = 2 -- ignores 1 and 3

This does not mean that 3 == 1, nor that 3 == 2. 3 is its own value. NULL is also its own "value", but in the sense that it has no value at all. In your example, just because NULL "values" are excluded from your query does not imply equality with the opposite.

Furthermore, note that you will get no return values if you query for field=NULL. This is because NULL != NULL. This is like saying "an unknown value == an unknown value", which is impossible to determine. You must use the "is null" keywords to detect NULL values.

2006-01-20 Reply Admin

John Bigboote:
nobody:
> Sybase ASE does not support nullable BIT datatypes. > Go ahead, ask me how I know this.
how do you know this?

Because I have been working with Sybase, the RC Cola of Enterprise RDBMS, for the last 5 1/2 years. From writing code that I am ashamed of in order to get around driver bugs to accepting fundamental weaknesses in the DB engine itself (an INT identity column? Who would ever want that?!?), Sybase ASE has crushed my spirit.

Hey, you asked.

Luckily, we're in the middle of a full conversion to SQL Server.

I feel your pain. We used Sybase 11 (yes, 11.0.0!) for 4 or 5 years before moving to postgres 8.1. Nothing is more frustrating than working around Sybase's quirks and lack of features. Want row locking? Sorry, not in syb 11. Want ansi joins? Nope. Want CASE statements. Nope. Fulltext searching? Not in 11. functions? no way. isql sucks. Its ability to help you find performance bottlenecks sucks. Its lack of a varchar larger than 255 bytes sucks. Its requirement to eat a whole page for any non-null text column sucks. 2kb to store ('hi!') in a text column. Insane. IDENTITY BURNING SET FACTOR (aaargh!!). Ad nauseum.

We've generated some WTFs of our own out of necessity because of Sybase.

2006-01-20 Reply Admin

> the RC Cola of Enterprise RDBMS

Huh ?

rholliday · 2006-01-20 Reply Admin

OneFactor:

So it would only be a problem for the original Green Lanterns?

In varest char, in one-bit byte
No column shall escape my sight
Let those who worship first-normal's might
Beware my power, Green AppForm's light!

Gene Wirchenko:

...
I doubt it. If he were, given the atrocities committed with DBMSs, someone would have attached a generator to him, and we would have heard of this near-infinite source of electrical power.
...

mrprogguy:

...

It's not really a WTF when someone does SQL badly, because it's not possible to do SQL well (along the lines of, "there's no such thing as playing the oboe well")
...

OneFactor:

Young grasshopper, observe the stored procedure.

...

And that, young grasshopper is why you need to place a W in front of the sacred letters of Boolean Algebra: TF.

You guys are in rare form today. My coworkers are staring at me now.

2006-01-20 Reply Admin

It also allows Nulls. (Just looked at it today)

--The funny thing is that my confirming image today is "Green"

warispeace · 2006-01-20 Reply Admin

lukas:

imho Adams is one of the most overrated writers (among nerds, that is) out there. yes, i've read more than one of his books. no, i did not think they were brilliant; i thought thay were boring and plotless.

have you ever read books by other authors?

Burn!

I like rereading the hitchhickers guide every now and again for some mindless comedy, but I generally agree with you.

Could you recommend some good comedy reading? The only other thing that comes to mind is Catch-22....

John Bigboote · 2006-01-20 Reply Admin

Anonymous:
> the RC Cola of Enterprise RDBMS

Huh ?

A distant third in market-share, and not likely to make up ground anytime soon.

2006-01-20 Reply Admin

And just be be certain everyone knows you're handy:

[HasQuad] VARCHAR(10) NOT NULL,

2006-01-20 Reply Admin

I remmber a long time ago I had to write my first application. You guys sound like you never did!

dhromed · 2006-01-20 Reply Admin

WTF Batman:
dhromed:

If the field can hold:

N
Y
null

Then why does [WHERE field <> 'N'] only select those where [field = 'Y'] and ignores the nulls as though they were equal to 'N'?

Because NULL is not a value. It's an "absence of value". You'll notice that if you instead query for field=N, the NULL fields will also be excluded, "as though" they were equal to Y.

Notice the flaw in the "as though" logic. I'll use numbers, but remember that NULL is not a value, but the same logical principle applies:

values are:

1
2
3

where field = 1 -- ignores 2 and 3
where field = 2 -- ignores 1 and 3

This does not mean that 3 == 1, nor that 3 == 2. 3 is its own value. NULL is also its own "value", but in the sense that it has no value at all. In your example, just because NULL "values" are excluded from your query does not imply equality with the opposite.

Furthermore, note that you will get no return values if you query for field=NULL. This is because NULL != NULL. This is like saying "an unknown value == an unknown value", which is impossible to determine. You must use the "is null" keywords to detect NULL values.

I understand all of that. Also note that consciously using the AsThough operator provided Tentative status to the subsequent statement I made as to the equality of 'N' and null.

Still, I wasn't querying for = N, I was querying for <> N.
So to keep with your int example, would make:

where field <> 1 -- should logically select 2 and 3
where field <> 2 -- should logically select 1 and 3

Yet, it doesn't, because null is apparently outside of reality as experienced by the DB. null can be compared for neither equality nor inequality with any value. Viz:

'N' = null -> false
'N' <> null -> false

yay

AJR · 2006-01-20 Reply Admin

Alex Papadimoulis:
Gene Wirchenko:
Alex Papadimoulis:
Relational databases don't have Boolean data types for good reason: the inherent nature of tri-value logic (i.e., true, false, and unknown/null) makes it a logical impossibility.

This statement is a WTF itself ... Some DBMSs do not have boolean. Some do.

All Relational Databases that I know of support NULL. When the concept of NULL is introduced, you have tri-value logic. The concept of tri-value is a bit foreign since the overwhelming majority of languages are Boolean-logic based.

With Boolean logic, "x || !x" (where x is a boolean expression, such as "y == z") is a tautology. In tri-value logic, it is TRUE or NULL. Even the expresion "m == m" is could be TRUE or NULL in tri-value.

This is why tri-value languages will have an IsUnknown or IsNull test, which is the only thing that can be used to compare such values.

Hopefully you now understand why it is therefore impossible to have a Boolean datatype (two values) within a tri-value system.

It seems to me that you are basically saying here "boolean has two values, therefore it can't have three", which seems to me to be a slightly silly way of arguing the point. It is entirely reasonable to have a boolean data type with three value (TRUE, FALSE, and NULL), and have definitions of the standard logical operations (AND, OR, and NOT) that allow for NULL valued inputs. Treating NULL as meaning "unknown value", we can define the normal operations by keeping the traditional definitions and adding NULLs, as shown in the following truth tables:

x | !x
--+---
T | F
F | T
N | N

(If x is unknown, then !x is unknown)

AND:
 \y
 x\| T | F | N
==============
 T|| T | F | N
-------+---+--
 F|| F | F | F
-------+---+--
 N|| N | F | N

(Short-circuit logic gives FALSE AND anything == FALSE, but TRUE AND NULL is unknown (i.e. NULL.) NULL AND NULL == NULL, obviiously.)

OR:
 \y
 x\| T | F | N
==============
 T|| T | T | T
-------+---+--
 F|| T | F | N
-------+---+--
 N|| T | N | N

(Short-circuit logic at work again, TRUE OR anything == TRUE, but FALSE OR NULL is unknown.)
(I could remove the short-circuit logic and say that is any input is NULL then the output is NULL, a convention which can be used in asynchronous hardware and interpreting NULL as "data not ready yet". If you're bored, have a look at this paper on NULL-convention logic. I will be utterly amazed if my ascii-art tables come out correctly, but I tink I've got more chane than getting the forum software to do HTML tables right.)

Looking at the example expression you give, "x || !x", which is a tautology in traditional boolean logic, using the definitions I have just given this expression is TRUE when x is TRUE or when x is FALSE, and is NULL when x is NULL. There's nothing wrong with that, as long as you can cope with "x || !x" no longer being a tautology.

As for your other point, about what happens with NULLs and equality, that is just as much of an issue whether you're looking at a nullable boolean or a nullable integer or float or string or any other data type you care to name.

In short, other than the obvious semantic argument (that a boolean data type has two values so it's not allowed to have the possibility of a third) I see no reason why not to have a nullable (i.e. tri-valued) "boolean" type.

AJR · 2006-01-20 Reply Admin

AJR:
(...I will be utterly amazed if my ascii-art tables come out correctly, but I tink I've got more chane than getting the forum software to do HTML tables right.)

And clearly, I should have more faith in my ability to force the forums to do my bidding, and less faith in my ability to spell...

2006-01-20 Reply Admin

"Relational databases don't have Boolean data types for good reason: the inherent nature of tri-value logic (i.e., true, false, and unknown/null) makes it a logical impossibility."

This is wrong in just about every way. For one thing, a few dbs do have an explicit Bollean type, but it's still not needed. You can just use a TINYINT(1) or whatever you database has. It's exactly the same. On top of that True and False is not Null. Null is a completely different thing. If False is like 0 and true like 1, then Null is like the Empty Set. The only illogical thing is when you start getting the two confused.

MikeTheBike · 2006-01-21 Reply Admin

This normalisation malarky is vastly overrated[:D]

2006-01-21 Reply Admin

Anonymous:

Are you serious? A boolean is now a tri-value? Wow.

In SQL Server, bit column, values = 1, 0 or null.

2006-01-21 Reply Admin

Anonymous:

Anonymous:

Are you serious? A boolean is now a tri-value? Wow.

In SQL Server, bit column, values = 1, 0 or null.

Sorry, guess I should have read all of the posts before giving a reply!

Interestingly though, M$ SQL allows bit with null (1/0/null) though .net only allows boolean true/false (until v2 which allows bool?)

dhromed · 2006-01-23 Reply Admin

Once again, my knowledge has been incremented. By 11, no less.

Thanks, dailyWTF!

TheRider · 2006-01-23 Reply Admin

Anonymous:
This is fantastic!

True: #00ff00
False: #ff0000
Null: #0000ff
Half-Truths: #008800
False Positive: #ffff00
Pessimistic false: #88ff00
Intangible false: #ff0088
Indecisive: #888800
Who cares: #888888
Purple: #C000C0

This multistate logic is brilliant.

And: Intangible False & Pessimistic False= #880000 (Barely false)

Or: Half-Truth | Purple = #C088C0 (Rather gay looking indetermiate truth)

Xor: False-Positive ^ Half Truth= #887700 (Kinda leaning towards false)

Not: !Intangible False = #00ff77 (Slightly less tangible true)

Its brilliant! I should patent it!

This kind of logic actually exists, is well researched and has been put to good use. Plese refer to this explanation of Fuzzy Logic: http://en.wikipedia.org/wiki/Fuzzy_logic.

2006-01-23 Reply Admin

Surely there's no useful difference between a type and a constrained domain? They're both just sets, and can both be used as the concrete representation of the abstract Boolean type. After all, what is a type except the set of values of that type?

vasco · 2006-01-23 Reply Admin

Gene Wirchenko:
Alex Papadimoulis:
Relational databases don't have Boolean data types for good reason: the inherent nature of tri-value logic (i.e., true, false, and unknown/null) makes it a logical impossibility.

This statement is a WTF itself. If you require tristate logic, then do not use binary logic. This is much the same as selecting a fixed decimal type when representing currency instead of using integers. Pick the appropriate type.

Some DBMSs do not have boolean. Some do.

Sincerely,

Gene Wirchenko

I can't post a new WTF because I don't have access to the code, but sometimes binary logic get's pushed to some incredible highs.
This is in a appl from an insurance company, the field SEX there have not the usual 2 states but 3 (no, not the obvious Y (yes) answer to the SEX? question).
In this case sex can be:

'M' for "masculino" (male)
'F' for "feminino" (female)

but it can be 'H' (no, it's not what you're thinking).
'H' here is for 'homem' (men) [homo in Latin... LOL], in wich case 'M' will mean "mulher" (woman).

After a few records, they have to start looking into the name to know what a 'M' in sex means...

But then they upgraded the appl.
The 'H' is still supported, but now you can have 'E' for "empresa" (company), 'P' for "empresa publica" (state own company), 'B' for "banco" (bank)... and so on, and so on, and so on... I'd love to look into this code...

mugs · 2006-01-23 Reply Admin

Alex Papadimoulis:

Gene Wirchenko:
Alex Papadimoulis:
Relational databases don't have Boolean data types for good reason: the inherent nature of tri-value logic (i.e., true, false, and unknown/null) makes it a logical impossibility.

This statement is a WTF itself ... Some DBMSs do not have boolean. Some do.

All Relational Databases that I know of support NULL. When the concept of NULL is introduced, you have tri-value logic. The concept of tri-value is a bit foreign since the overwhelming majority of languages are Boolean-logic based.

With Boolean logic, "x || !x" (where x is a boolean expression, such as "y == z") is a tautology. In tri-value logic, it is TRUE or NULL. Even the expresion "m == m" is could be TRUE or NULL in tri-value.

This is why tri-value languages will have an IsUnknown or IsNull test, which is the only thing that can be used to compare such values.

Hopefully you now understand why it is therefore impossible to have a Boolean datatype (two values) within a tri-value system.

Using your logic (or lack thereof), no database that supports null could possibly have an integer data type, because null is not an integer.

mugs · 2006-01-23 Reply Admin

mugs:

Using your logic (or lack thereof), no database that supports null could possibly have an integer data type, because null is not an integer.

To clarify this statement, you can have a boolean data type - a data type that supports the values true and false (not "true" and "false"). But it will have the additional possibility of null. I don't think that makes it any less a boolean field than an integer is an integer.

2006-01-24 Reply Admin

Anonymoose:
Anonymous:
Anonymous:

Are you serious? A boolean is now a tri-value? Wow.

Precisely to prevent that kind of confusions, that's the reason I always use quaternary boolean logic: true, false, null and undefined.

I prefer to use a float for my booleans:

Court martial in progress:
Judge: Are you positive about that statement?
Cmdr Zap Brannigan:Absolutely, positively, 99.9% sure!

Don't use float! You need a lot of precission when dealing with booleans. Use double instead. :p

helebor · 2006-01-26 Reply Admin

Port, Starboard and Lost?

2006-01-31 Reply Admin

Sadly, it seems you have no sense of humor. Bummer for you. A friendly word of advice; keep your low opinion of great works of modern literature to yourself. It generally is a bummer to learn that someone lacks an appreciation for humor.

Is there a funny book that you do like?

The Green, the Whole Green, and Nothing But the Green

Leave a comment on “The Green, the Whole Green, and Nothing But the Green”