List:General Discussion« Previous MessageNext Message »
From:Kevin Williams Date:March 3 2004 7:45pm
Subject:Re: PgSQL vs MySQL
View as plain text  
Michael,

I couldn't agree more!  I didn't even realize there was a boolean issue
until now.  I don't really understand the issue of using 0 as false, and
1 as true.  The way I view it is that "true and false" are merely
abstract names for 0 and 1.  When the database is accessed using Java, I
don't even see the 0 and 1.  For example, using the JDBC API,
pStmt.setBoolean(1, true) command sets the field to 1, and
rs.getBoolean('booleanField') returns true if 1, and false if 0.

So again, I reiterate, what's the issue?

Kevin

On Wed, 2004-03-03 at 11:31, Michael Stassen wrote:
> Curtis Maurand wrote:
> > :-)
> > 
> > someflag enum('TRUE','FALSE');
> > 
> > Not quite boolean, but it works.
> > 
> > Curtis
> > 
> > On Wed, 3 Mar 2004, Mark Warner wrote:
> > 
> > 
> >>The thing which bothers me most about MySQL is the lack of a proper 
> >>boolean. I don't like having to abstract a tinyint(1) into true or 
> >>false. As much of my work involves building applications with Yes or No 
> >>questions, I think I am switching to PostgreSQL.
> <SNIP>
> 
> To my mind, a boolean is something that can be used in a boolean 
> context.  That is, if flag is a boolean, the following should work as 
> expected:
> 
>    SELECT * FROM mytable WHERE flag;     # rows with flag = TRUE
>    SELECT * FROM mytable WHERE NOT flag; # rows with flag = FALSE
> 
> If you have to compare the column's value to something, it isn't really 
> a boolean value.
> 
> MySQL, like most programming languages, treats 0 as FALSE and any other 
> number as TRUE.  That means that boolean expressions are evaluated 
> numerically and compared to 0.
> 
> In a numeric context, ENUM columns return the value's position in the 
> list of allowed values, starting with 1.  This means that with the 
> definition
> 
>    someflag enum('TRUE','FALSE')
> 
> someflag evaluates as 1 when it is 'TRUE' and 2 when it is 'FALSE', both 
> of which are TRUE in boolean context.  Hence, you cannot use someflag in 
> boolean context and get the expected results.  In other words, someflag 
> looks like a boolean if you view the data, but doesn't behave as a 
> boolean in queries.
> 
> I don't quite know what Mark Warner means by "abstract a tinyint(1) into 
> true or false," but I personally find using tinyint for boolean to be a 
> simple solution.  I define "someflag TINYINT", then set it to 0 for 
> FALSE and 1 (or any other number) for true.  Of course, if you're not a 
> programmer it won't look like a boolean when viewing the data (is that 
> what you don't like, Mark?), but it will behave as one.
> 
> With tinyint you get some added flexibility, which may be an advantage, 
> depending on your application.  For example, if I store the number of 
> children a person has in the tinyint kids, I can use kids in boolean 
> context:
> 
>    SELECT * FROM persontable WHERE kids;      #people who have children
>    SELECT * FROM persontable WHERE NOT kids;  #people who don't
> 
> One more advantage of tinyints is that, with common programming 
> languages, they continue to function as booleans in your application.
> 
> Another option is to take advantage of the special error value in every 
> ENUM.  	If you insert a wrong value, mysql replaces it with '', which is 
> always 0 in numeric context.  So, you could define
> 
>    someflag ENUM ('TRUE', 'T');
> 
> If you insert 'FALSE' or 'F' (or 'false' or 'f') into someflag, it will 
> get the error value, 0 or ''.  When you view the data, you will see 
> 'TRUE' or 'T' for TRUE values and blanks for FALSE, and someflag will 
> behave as boolean in queries.
> 
> If your values are 'Y' and 'N', then, strictly speaking, you don't have 
> a boolean unless you've defined which is TRUE and which is FALSE. 
> Assuming you want to treat 'Y' as TRUE, you could define
> 
>    yesflag ENUM ('YES', 'Y');
> 
> Inserting 'YES' or 'Y' (or 'yes' or 'y') will get you the corresponding 
> entries, which are TRUE in boolean context.  Inserting 'NO' or 'N' (or 
> 'no' or 'n') will get you blanks, which are FALSE in boolean context.
> 
> Michael
> 
Thread
PgSQL vs MySQLMark Warner3 Mar
Re: PgSQL vs MySQLdavid.best3 Mar
  • Re: PgSQL vs MySQLMark Warner3 Mar
    • Re: PgSQL vs MySQLCurtis Maurand3 Mar
      • Re: PgSQL vs MySQLRay3 Mar
      • Re: PgSQL vs MySQLMichael Stassen3 Mar
        • Re: PgSQL vs MySQLKevin Williams3 Mar
        • Re: PgSQL vs MySQLCurtis Maurand3 Mar
  • Re: PgSQL vs MySQLDavid Griffiths3 Mar
    • RE: PgSQL vs MySQLJames Kelty3 Mar
    • RE: PgSQL vs MySQLJeremy Smith3 Mar
      • Re: PgSQL vs MySQLKaarel13 Mar
  • Re: PgSQL vs MySQLKaren Abgarian3 Mar
  • Re: PgSQL vs MySQLDavid Griffiths3 Mar
    • Re: PgSQL vs MySQLMark Warner3 Mar
Re: PgSQL vs MySQLEric @ Zomething3 Mar
  • Re: PgSQL vs MySQLMartijn Tonies3 Mar
Re: PgSQL vs MySQLDavid Griffiths3 Mar
Re: PgSQL vs MySQLJeremy Zawodny3 Mar