select helper with boolean and null/nil values

I have a boolean column that I would like to modify with a select
helper element, and have :include_blank map to (database) null.

This *almost* works. On the create/edit/update page, if you select
true/yes it sets the column to true, if you select No/false it sets it
to false, and if you set it to blank, it sets it to (database) null.
fantastic.

Now you edit the record. If it is "True" in the database the select
box picks the correct value. If it was set to 'no' or (database)null
however the selection is set to the blank value.

This is especially problematic if you edit any other field on the
form, as it resets *everything* back to 'null', regardless of if it
was "false" or "null" to beginwith.

I have constructed a trivial test application to demonstrate this:

http://www.cs.rpi.edu/~crossd/selecttest.tar.bz2

Fire it up in 'rails server' and go to http://127.0.0.1:3000/testings

(I've included a sqlite3 db there with 2 rows in it). edit one, set
it to 'Yes', check the 'show' page, edit it again, set it to 'No',
check the 'show' page, edit it again, set it to blank (you won't have
to, its already there), and save it and check the show page. notice
all 3 edits work correctly, but the initial value is NOT set correctly
for for the 'False' case.

Is this a bug? am I doing something wrong?

I believe your problems are coming from the fact that you allow
something different than true/false in your DB column. If the column
is supposed to contain a boolean value, why are you allowing it to
contain the null value? A boolean value is either true or false, not
null. If I were you I would force the value to be one or the other and
make the column not null capable. You can enforce the rule with one of
the validation helpers. This is extracted from the
'validates_presence_of' documentation:

"If you want to validate the presence of a boolean field (where the
real values are true and false), you will want to use
validates_inclusion_of :field_name, :in => [true, false]. "

If you don't want to display errors and just control the issue
silently you can always use before_save to change the value to false
if needed. Something like:

<code>
def before_save
  self.my_boolean_column = false unless self.my_boolean_column == true
end
</code>

You should check against the value "true" (meaning that you shouldn't
leave the equality comparison out) because otherwise you could run
into problems if the value is ever something other than true/false/
null. A simple empty string would return "true" on the condition
otherwise as this example shows:

# This works in all cases

a = true

=> true

puts 'true' if a == true

true
=> nil

puts 'true' if a

true
=> nil

# This works only when comparing against 'true'

a = ''

=> ""

puts 'true' if a == true

=> nil

puts 'true' if a

true # Notice how this would make the code above work incorrectly on
the before_save
=> nil

pepe wrote in post #962574:

I believe your problems are coming from the fact that you allow
something different than true/false in your DB column. If the column
is supposed to contain a boolean value, why are you allowing it to
contain the null value? A boolean value is either true or false, not
null.

Poppycock. It's perfectly OK to have Boolean columns (or any columns)
be null-capable if the semantics of the data dictate that.

Best,

[...]It's perfectly OK to have Boolean columns (or any columns)
be null-capable if the semantics of the data dictate that.

Sure. However, in my experience most people make boolean columns null
capable "just because" or because it's the default behavior of the DB
when defining columns and after a while problems arise because the
returned value is expected to be either true or false, not null: "It
is a boolean column, right? Why doesn't it return true or false? Why
do I have to worry about the value being null?".

IMHO boolean columns are just a little different because of their very
nature. Sure, they can be set to be null but I really don't see the
advantage in it. In my experience, if I set the column to default to
either true or false (probably false all the time) and change it as
needed things work much better and things get easier.

pepe wrote in post #962590:

[...]It's perfectly OK to have Boolean columns (or any columns)
be null-capable if the semantics of the data dictate that.

Sure. However, in my experience most people make boolean columns null
capable "just because" or because it's the default behavior of the DB

[...]

That may be. For myself, I try to consider carefully whether I need
null values in a particular field, and what they mean. For booleans,
three states are often useful.

IMHO boolean columns are just a little different because of their very
nature.

I don't see the difference. Where do you see it?

Sure, they can be set to be null but I really don't see the
advantage in it.

I've run across cases where something was best modeled by SQL-style
3-valued logic (for example, yes/no/maybe sorts of things). Why
*wouldn't* I use a nullable boolean field for that?

In my experience, if I set the column to default to
either true or false (probably false all the time) and change it as
needed things work much better and things get easier.

For a true 2-valued boolean, that's also what I do.

Best,

I assure you that if my usage could have been modeled with 2 states instead of 3, I would have disallowed “null” :slight_smile:

Three value is very useful. If I have to move this to an integer type, I will (yuck)… but really SQL allows this, the select-box allows this, it works for OTHER data-types. (I can have NULL dates that show up as blank in date_select helpers. I don’t think anyone would suggest that I move to a text type to support a null date, since NO date isn’t a date!).

So, at this point, have we confirmed this is a bug? Even within ruby, we could model this as true/false/nil, right?

> IMHO boolean columns are just a little different because of their very
> nature.

I don't see the difference. Where do you see it?

> Sure, they can be set to be null but I really don't see the
> advantage in it.

I've run across cases where something was best modeled by SQL-style
3-valued logic (for example, yes/no/maybe sorts of things). Why
*wouldn't* I use a nullable boolean field for that?

Certainly not very 'academic' but from Wikipedia's page
http://en.wikipedia.org/wiki/Boolean_data_type:

"In computer science, the Boolean or logical data type is the most
primitive data type, having one of two values (true or false),
intended to represent the truth values of logic and Boolean algebra."

Why do I think boolean columns are a little different? You could
interpret that null is 'similar' to false because it is not true, but
that could also be the case the other way around. If you look a the
issue from the perspective of "well, a boolean column is just a
column", yes, I agree, there is no difference. However if you look at
boolean columns from the perspective of their most likely reason to
exist they are different because they imply that they will always
contain a value, either true or false and null is neither. That is
different IMO than, for example, a date column, which could very
possibly be conceived to contain either a date or no date at all.
Could a boolean column be conceived to be null? Certainly, but again,
not the most likely case in my experience.

Although an interesting proposition I don't think I would ever use a
boolean column to store "true/false/maybe sort of things". Of course,
the column could be created to allow it but that, IMO, would defeat
the purpose of the boolean column. I would rather use a different type
because, as it happened to me in the past, a 'true/false/maybe' sort
of scenario can quickly become a 'true/false/maybe/likely/not that
likely/very likely' sort of scenario and then I would need to change
my DB and code.

pepe wrote in post #962618:

*wouldn't* I use a nullable boolean field for that?

Certainly not very 'academic' but from Wikipedia's page
http://en.wikipedia.org/wiki/Boolean_data_type:

"In computer science, the Boolean or logical data type is the most
primitive data type, having one of two values (true or false),
intended to represent the truth values of logic and Boolean algebra."

Why do I think boolean columns are a little different? You could
interpret that null is 'similar' to false because it is not true,

But I'm not. I think SQL has it right: null is neither false nor true.

but

that could also be the case the other way around. If you look a the
issue from the perspective of "well, a boolean column is just a
column", yes, I agree, there is no difference. However if you look at
boolean columns from the perspective of their most likely reason to
exist they are different because they imply that they will always
contain a value, either true or false and null is neither.

But that's not even true if we ignore the true 3-valued yes/no/maybe
case. The intent of SQL null is to represent *unknown* data. Imagine
storing data about your friends, including whether they own a car. You
might have a 'has_car' column, which would of course be boolean.

Now, you may not know (because you haven't asked) whether some of your
friends own a car or not. So what to put in has_car? You can't put
true. You can't put false. Guess what, you put null!

That is
different IMO than, for example, a date column, which could very
possibly be conceived to contain either a date or no date at all.
Could a boolean column be conceived to be null?

Yes, absolutely. See above.

Certainly, but again,
not the most likely case in my experience.

But common enough. Don't pretend it doesn't happen.

Although an interesting proposition I don't think I would ever use a
boolean column to store "true/false/maybe sort of things".

Why not?

Of course,
the column could be created to allow it but that, IMO, would defeat
the purpose of the boolean column.

No. SQL booleans are three-valued. It makes sense to use that
property.

I would rather use a different type
because, as it happened to me in the past, a 'true/false/maybe' sort
of scenario can quickly become a 'true/false/maybe/likely/not that
likely/very likely' sort of scenario and then I would need to change
my DB and code.

Then change it *at that time*. You can't know what will happen in the
future, and therefore you can't really design for it. Remember YAGNI.
Don't overdesign. Don't anticipate if anticipation does not give you a
present benefit.

Best,

Ok… lets bring it back around. Forget it says “boolean”, for get all of the baggage that has. There is a column type called “boo” (for the sake of argument) in SQL, that Rails claims to support, but apparently the model is incorrect? (again, have we confirmed this is a bug, and not something I am doing wrong?) and doesn’t fully support the “boo” column type? This seems like a rails bug in the “boo” type.

Interestingly though, it DOES let you set it correctly, and even print it, just when used in a select() box does it choke?

> Why do I think boolean columns are a little different? You could
> interpret that null is 'similar' to false because it is not true,

But I'm not. I think SQL has it right: null is neither false nor true.

Out of context. I never said it was and 'you' meant 'anybody'. Sorry
if it was misleading. Don't take things so personally.

But that's not even true if we ignore the true 3-valued yes/no/maybe
case. The intent of SQL null is to represent *unknown* data. Imagine
storing data about your friends, including whether they own a car. You
might have a 'has_car' column, which would of course be boolean.

Now, you may not know (because you haven't asked) whether some of your
friends own a car or not.

That is the first thing I ask my friends. Don't you? :wink:

So what to put in has_car? You can't put
true. You can't put false. Guess what, you put null!

I'd put "I didn't ask" but that wouldn't fit in the boolean
column... :wink:

Very true, though.

> That is
> different IMO than, for example, a date column, which could very
> possibly be conceived to contain either a date or no date at all.
> Could a boolean column be conceived to be null?

Yes, absolutely. See above.

You have certain ability to repeat yourself.

> Certainly, but again,
> not the most likely case in my experience.

But common enough. Don't pretend it doesn't happen.

Not pretending.

> Although an interesting proposition I don't think I would ever use a
> boolean column to store "true/false/maybe sort of things".

Why not?

Fragmented. Because of the explanation I gave. Did you read the whole
paragraph? (see below).

> Of course,
> the column could be created to allow it but that, IMO, would defeat
> the purpose of the boolean column.

No. SQL booleans are three-valued. It makes sense to use that
property.

I agree with you in the case of *unkown* data.

(From above: you see? this next paragraph explains why. I knew I had
written it somewhere!)

> I would rather use a different type
> because, as it happened to me in the past, a 'true/false/maybe' sort
> of scenario can quickly become a 'true/false/maybe/likely/not that
> likely/very likely' sort of scenario and then I would need to change
> my DB and code.

Then change it *at that time*.

Sure, and spend who knows how much time changing DB and code if that
happens.

You can't know what will happen in the
future, and therefore you can't really design for it. Remember YAGNI.
Don't overdesign. Don't anticipate if anticipation does not give you a
present benefit.

YAGNI is a very good principle to follow and I certainly try to adhere
to it as much as possible, however it's not the only thing to take
into consideration. There is something else I try to use as much as
possible, it's called personal experience. There have been times when
because of adhering to YAGNI I had to go back and rework A LOT of
code. Now I prefer 'overdesigning' a little bit if I see that there is
a possibility that not doing so might come back to bite me, even if
the only "present benefit" I get is to have peace of mind and know
that I won't get bitten by it. I'd rather spend 30 or 60 extra minutes
giving a universal solution to a problem or generating related
additional functionality that I might see a use for (but not needed
right now) than having to go back and spend days reworking something
because of those 'saved' 30 or 60 minutes.

Hi David,

I haven't looked at your code, I was just trying to offer a possible
reason/solution to keep you going. I'll try to take a look asap but
I'm busy right now. However, I am using true/false in an HTML select
and it works fine for me.

pepe wrote in post #962646:

> Why do I think boolean columns are a little different? You could
> interpret that null is 'similar' to false because it is not true,

But I'm not. I think SQL has it right: null is neither false nor true.

Out of context. I never said it was and 'you' meant 'anybody'. Sorry
if it was misleading. Don't take things so personally.

I wasn't taking it personally. My point was that if I use null in a
boolean column, I do not interpret it as equivalent to false. Others
might, but that would be a mistake, as you know.

[...]

So what to put in has_car? You can't put
true. You can't put false. Guess what, you put null!

I'd put "I didn't ask" but that wouldn't fit in the boolean
column... :wink:

Very true, though.

Right. "NULL" is SQL for "I didn't ask".

[...]

> I would rather use a different type
> because, as it happened to me in the past, a 'true/false/maybe' sort
> of scenario can quickly become a 'true/false/maybe/likely/not that
> likely/very likely' sort of scenario and then I would need to change
> my DB and code.

Then change it *at that time*.

Sure, and spend who knows how much time changing DB and code if that
happens.

You can't know what will happen in the
future, and therefore you can't really design for it. Remember YAGNI.
Don't overdesign. Don't anticipate if anticipation does not give you a
present benefit.

YAGNI is a very good principle to follow and I certainly try to adhere
to it as much as possible, however it's not the only thing to take
into consideration. There is something else I try to use as much as
possible, it's called personal experience.

Personal experience is valuable, but you can't always know that this
design will be just like the last one.

There have been times when
because of adhering to YAGNI I had to go back and rework A LOT of
code. Now I prefer 'overdesigning' a little bit if I see that there is
a possibility that not doing so might come back to bite me, even if
the only "present benefit" I get is to have peace of mind and know
that I won't get bitten by it.

You know nothing of the sort in most cases. Your actual problems are
usually not the ones you anticipated -- after all, if you had
anticipated them, you would have anticipated a solution too.

I'd rather spend 30 or 60 extra minutes
giving a universal solution to a problem or generating related
additional functionality that I might see a use for (but not needed
right now) than having to go back and spend days reworking something
because of those 'saved' 30 or 60 minutes.

That's a poor trade-off. I know it looks good, but it really isn't.
What you're doing is wasting 30-60 minutes for something that you don't
need now and may never need.

Barring a few special things like I18N, which really is easier to put in
at the start, what takes 30-60 minutes to put in now should take 30-60
minutes to put in two years from now when you actually need it. If
that's not the case in your codebase, then you're probably not
refactoring enough.

Best,

Personal experience is valuable, but you can't always know that this
design will be just like the last one.

I don't think about it as a matter of design but just common sense in
most cases. I'll explain later.

> I'd rather spend 30 or 60 extra minutes
> giving a universal solution to a problem or generating related
> additional functionality that I might see a use for (but not needed
> right now) than having to go back and spend days reworking something
> because of those 'saved' 30 or 60 minutes.

That's a poor trade-off. I know it looks good, but it really isn't.
What you're doing is wasting 30-60 minutes for something that you don't
need now and may never need.

Just a silly example:

At a place I used to work I was using a much less flexible language
than Ruby. This language didn't have by far the capabilities to
calculate dates or handle strings that Ruby has or the flexibility to
run SQL statements that Rails provides. So during my work there I was
tired of hard coding SQL statements all over the place, escaping
countless quotes, etc. The code statements were ugly looking and
difficult to read. I didn't really 'need' a tool or a system to make
the whole thing more flexible, nobody asked for it!, but I got to it
and created a system to handle the SQL statements in a much more
flexible manner and while I was at it I built additional string and
date handling routines that I didn't need at the time. They were
initially conceived only to solve the SQL string problems. After 1/2 a
day or so of work I had a good additional set of functions that could
handle not only the SQL string problems I had but also much more
generic string functionality that was natively missing in the
language. Think something like 'squeeze'.

Did I need all that? No. Was it nice to work with it after I was done?
You betcha! And the funny thing is that after I created all that the
other developers started using the string and date functions I had
created. So what did the 1/2 day bought me? Personally, not much,
maybe satisfaction would be most of it. To the company it meant
countless of productivity hours gained, only because I "over-
engineered" something I didn't even need to do.

pepe wrote in post #962678:

Personal experience is valuable, but you can't always know that this
design will be just like the last one.

I don't think about it as a matter of design but just common sense in
most cases. I'll explain later.

> I'd rather spend 30 or 60 extra minutes
> giving a universal solution to a problem or generating related
> additional functionality that I might see a use for (but not needed
> right now) than having to go back and spend days reworking something
> because of those 'saved' 30 or 60 minutes.

That's a poor trade-off. I know it looks good, but it really isn't.
What you're doing is wasting 30-60 minutes for something that you don't
need now and may never need.

Just a silly example:

At a place I used to work I was using a much less flexible language
than Ruby. This language didn't have by far the capabilities to
calculate dates or handle strings that Ruby has or the flexibility to
run SQL statements that Rails provides. So during my work there I was
tired of hard coding SQL statements all over the place, escaping
countless quotes, etc. The code statements were ugly looking and
difficult to read. I didn't really 'need' a tool or a system to make
the whole thing more flexible, nobody asked for it!,

At that point, you *did* need it. Ugly or repetitive code is an example
of a need -- in this case, for refactoring or generalization.

[...]

Did I need all that? No.

Yes you did, by the definition of "need" I"m using.

Best,

At that point, you *did* need it. Ugly or repetitive code is an example
of a need -- in this case, for refactoring or generalization.

There might have been a 'personal' need but I don't think that makes
it a *real* need. The ugly code was just the way it is done. Changing
it was like saying "I don't like the way Ruby lets me assign strings
to a variable so I am going to change it." and then you come up with
something that replaces my_var = 'my string'.

Hi David,

Can you post your application in a different format? I need a C
compiler to install bzip2 and I'd rather not go into all that.

Thanks.

Sorry, I took a break for the weekend, here it is in .tgz format:

http://www.cs.rpi.edu/~crossd/selecttest.tgz

Ok… I have been doing some experimentation… and honestly, now I am beginning to get a bit ticked off with how inconsistently this is being handled…

For example, if you replace the f.select() in my example code with:

select_tag(“working”, options_for_select([["", “”], [“Yes”, true], [“No”,false]], @testing.working))

it WORKS! (well minus the posting values back to the form and having it update the database since its not in the model…

HOWEVER:
f.select (:working, [["", “”], [“Yes”, true], [“No”, false]], {:selected => @testing.working})

does NOT work.

REALLY?!

In fact I changed the selections choices to have [“first”, “first”] ahead of the “” choice to test a theory that it wasn’t actually matching anything… and it turns out that’s what its doing, for the case of “false” it doesn’t actually match ANYTHING. you can replace “:selected => false” and it won’t work, it will select “Yes”… go ahead, try it.)… HOWEVER, it works just fine with select_tag?! AUUUGH.

I’ve been through the rails code a couple of times, but I cannot see where this obtuse bug is laying.

And.. yes.. I am replying to myself a lot, but I found a workaround
that indicates that this IS a bug, it has *NEVER* worked. People just
got lucky because of the default order of choices.

To make this work it is as "easy" as:
[f.]select([object], :method, [["Yes", true],["No", false]]
{:include_blank=>true, :selected=>@object.method.to_s})

@object.method.to_s is the "magic" there. for whatever idiotic,
stupid, time-wasting reason, "false" and "nil" are NEVER matching
anything. In fact if you remove the ":include_blank" there, and keep
that ordering, *false* will show up as "Yes"/true... because
'selected' doesn't get set, since it doesn't match anything in the
list.

This is *horribly* broken behavior, and a patch should be made.
Someone flips an option somewhere and they could wind up trashing
countless data before they realize "false"/nil doesn't actually match
anything.

David,

I experienced this exact same problem. I echo your sentiments and you
are very correct that it is a bug and a big pain. Thanks for posting
the problem and a working solution:

Here is my *now* working snippet of code:

<%= f.select :male, [["Male", true],["Female", false]],
{:include_blank=>true, :selected=>@user.male.to_s} %>

Thanks again,
Sean