kasia in a nutshell

Oracle should take a cue from Mysql? ROLF! That's like asking K&R to take a cue from Visual Basic. I use Postgresql - it has booleans (that returns t|f, at least via PHP anyway).

What a helpful comment, thanks.. that really clarifies the issue.

Sure, no problem! I became curious why Oracle doesn't have a boolean type and did some Googling - couldn't find any answer (and Oracle's docs appear to be oh so helpfully restricted to members). Somebody else shares the sentiments about booleans:

"Just when you are thinking that life with Booleans is great, I have some bad news: The Oracle RDBMS does not support a Boolean datatype. I do not understand why Oracle has not taken this step. But the fact is that you cannot define a column in a table with type BOOLEAN. Instead, you can (must) create a table with a column of datatype CHAR(1) and store either "Y" or "N" in that column to indicate TRUE or FALSE, or create a numeric column and store either 1 or 0 for TRUE or FALSE. That is a poor substitute, however, for a datatype that stores true Boolean values (or NULL)."
http://www.fortunecity.com/skyscraper/oracle/699/orahtml/oreview/transb1.html

That's gotta be screwy that pl/sql has booleans, but Oracle does not. And apparently because of the OCI, JDBC drivers can't pass boolean parameters to pl/sql:
http://www.csee.umbc.edu/help/oracle8/java.815/a64685/tips3.htm#1005343

Oracle haters club:
http://weblogs.asp.net/alex_papadimoulis/archive/2004/11/29/271650.aspx

Yah, that's about all I found on the issue.. a lot of questions and no answer.

Thankfully, it has been a few years since I had to use Oracle. I don't know the answer to your question, but I had a few similar "issues":

- Why is Oracle's handling of dates so impossible to use?

- Does Oracle provide any way to distinguish between an empty string and a NULL? (I don't think it did.)

- Why the $#@! did my boss choose Oracle?

BTW, do you *really* use the kid's play that is mysql? If so, *WHY*? It's been a long time since I even bothered with mysql and back then it didn't have any boolean type. I'd check mysql's docs, but their site appears to be down or unreachable or who knows what. Oracle appears to be for sado-masochists. While Postgres supports TRUE|'t'|'true'|'y'|'yes'|'1' and FALSE|'f'|'false'|'n'|'no'|'0' for booleans:
http://www.postgresql.org/docs/8.0/interactive/datatype-boolean.html

I prefer the 1 or 0 with tinyint with MySQL so much better ;) Also certain people love that ENUM('y','n') for true/false values.

MySQL tends to be easier to use esp. when one has been using it for years! ;)

I use Postgresql for work and MySQL for play but in the past the bulk of the projects I've worked on all used MySQL.

I use MySQL 4 as a development DB on my Windows machine. I love it because the administrative tools are far better than what they used to be years ago, it takes little RAM, and it´s almost free :-)

PHPs support for MySQL is good as well (Except for the lack of PHP4´s support for the longer encrypted passwords in MySQL 4 +). I don´t really like PHP much though.

There's no point in running a company with ten billion dollars a year in revenue and a sixty-billion-dollar market cap if you can't say "Pah! I scoff at your pathetic little standard."

It's not like Oracle is going to lose millions of dollars of sales to Sybase because they don't have a boolean data type.

While it doesn't tell the whys precisely, Tom Kyte, master of Oracle and author of the "AskTom" site and column in Oracle Magazine, explains his POV on it, and also gives his techniques for working with pseudo-booleans at

http://asktom.oracle.com/pls/ask/f?p=4950:8:2109566621053828525::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:6263249199595

Also, from another expert, Joe Celko (if you don't know his name, look him up and read SQL for Smarties): http://www.codecomments.com/archive352-2005-1-377223.html
"There are no BOOLEAN types in SQL-92 for a good reason, having to do with NULLs and 3VL."

That is, programs expect a Boolean to have either True or False; by definition, no other value beyond these 2 is allowed. If you allow a Null, things break and now you have 3 values (3VL), unless you redefine Boolean to allow it. That's problematic for many folks, either practically or theoretically.

So, one can say that Oracle should expand the SQL standard since everyone else has... but there is a good case to be made by pretty smart folks that there are better ways to do what you want without modifying the basic tenets of the language.

As for me, I have a group of functions I wrote (sigh) to simulate "istrue" and "isfalse" boolean functions, and I document that Null=False in my programs. Silly but true.

> That is, programs expect a Boolean to have
> either True or False; by definition, no other
> value beyond these 2 is allowed. If you allow a
> Null, things break...

Weak, just don't allow NULL in the field if that's a problem. TA DA! Seems to work with every other product and if it didn't work, things would be breaking and no one would ask for it.

Don't have an answer, but I would go with the tinyint or char option. A integer datatype is going to take up 4 times the space, which just seems silly for a 0/1.

I'm still doing a lot of SQL server work and that has a boolean data type (they call it 'bit'), but if I had to make one, I would use the tinyint to keep the 0/1 format.

Just my $.02

Oops, Oracle doesn't have tinyint does it. I guess a Char(1) with 0/1 in it then.

Yuck!

Booleans are in SQL-99:
http://www.daffodildb.com/sql99features.html

As for the mysql harpies - little kids think hammers are great all-purpose tools until they wise up.

adults know that hammers and air nailers both have a place in carpentry but know when to chose one or the other.

it has very little to do with brand bigotry, and a lot to do with cost and suitability.

for my part, i'll stop using mysql when it stops being adequate and cheap. alas, i'll pobably never learn to steer clear of a troll.

First, I'm not the same mike that's been trolling.

While I can't provide an answer, I can say that DB2, both mainframe and unix, doesn't have a boolean data type either.

This has been so pervasive that at the bank where I work, they don't even use the phrase. Everything's an 'indicator' which is a CHAR(1) 'Y' or 'N'.

LOL. Tell it like it is and get called a troll. I don't care - I'm fluent with many databases and know how lousy mysql is in so many ways. Ignorance is bliss for one-trick ponies who only know one thing (like mysql). It's amazing how so many people choose to keep their heads in the sand. But that's why so many sheep use python, mysql, and wear black in the Valley.

Why such a heated discussion over different SQL databases? Everyone knows that SQLite is the only one that matters. The other ones are just too bloated to be embedded in an application. :-D

PBW - I think you're missing the subtlety that a Boolean type needs to be implemented at a more global level. When you create a field you can toggle whether NULL values are allowed on and off, so the DB package as a whole needs provisions for NULL values to accommodate those users who allow them.

I blame Al Gore. He should have solved this problem when he invented the Internet. :-)

No I understand, but yet somehow a number of database packages get along fine with allowing NULL in a Boolean. If you don't want NULL Booleans, fine the DBA can design that in their database. Software packages exist for database design which makes that kind of global rule easy to manage.

Also, I have seen software packages (ServiceCenter for example, who is a market leader in problem and change management) use NULL Boolean fields in their application.

The key is flexibility. The world doesn't have to rule based to the nth degree. Sometimes real life has a good reason to bend the rules a bit. :)

There is also the metadata issue. Us Java people have a fetish for libraries that generate code based on metadata. Having chars instead of boolean really messes up your karma. Imagine the horror of:

if (product.getProcessed() == 'Y')

instead of

if (product.isProcessed())

Almost too ghastly to contemplate.