Rails - Mar 2011 - Testing for boolean values in a scope isn't portable?

The following scope works on sqlite3 in development, but fails in
production on PostgreSQL:

  scope :past_due,
        where(''requested_start_date < ? AND complete IS NOT
?'',
Date.today, true).
        order(''requested_start_date ASC'')

2011-03-11T15:51:33-08:00 app[web.1]: ActiveRecord::StatementInvalid
(PGError: ERROR:  syntax error at or near "''t''"
2011-03-11T15:51:33-08:00 app[web.1]: LINE 1: ...requested_start_date
< ''2011-03-11'' AND complete IS NOT ''t'')

Contrariwise, sqlite3 blows up if you use a construct like:
  scope :past_due,
        where(''requested_start_date < ? AND complete IS NOT
TRUE'',
Date.today).
        order(''requested_start_date ASC'')

I''m currently testing now for "t":

  scope :past_due,
        where(''requested_start_date < ? AND complete IS NOT
"t"'',
Date.today).
        order(''requested_start_date ASC'')

but this seems wrong somehow. What is the correct way to test for a
Boolean value in a named scope that will be portable across all
databases?

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

So, after more trial and error, this is the portable version.

  scope :past_due,
        where(''(requested_start_date < ?) AND (complete =
?)'',
Date.today, true).
        order(''requested_start_date ASC'')

I''d still like to understand *why* this is more portable, though, if
anybody knows the answer.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Mar 11, 4:31 pm, "Todd A. Jacobs"
<codegnome.consult...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> So, after more trial and error, this is the portable version.
>
>   scope :past_due,
>         where(''(requested_start_date < ?) AND (complete =
?)'',
> Date.today, true).
>         order(''requested_start_date ASC'')
>
> I''d still like to understand *why* this is more portable, though,
if
> anybody knows the answer.
Turns out this still isn''t portable. On PostgreSQL I have to run:

   Job.where(''(requested_start_date < ?) AND (complete IS NOT
TRUE)'',
Date.today)

but this is still blowing up SQLite3. Sadness. Back to the drawing
board; any suggestions?

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

>> So, after more trial and error, this is the portable version.
>> 
>>  scope :past_due,
>>        where(''(requested_start_date < ?) AND (complete =
?)'',
>> Date.today, true).
>>        order(''requested_start_date ASC'')
>> 
>> I''d still like to understand *why* this is more portable,
though, if
>> anybody knows the answer.
Databases have different ideas about what the value of true/false and whether it
understands the keywords ''true'' and ''false''.
MySQL (if I remember right) doesn''t even have a boolean field.  Instead
a the adapter will convert TINYINT''s to true/false for you.

So you get into problems if you pass literal true/false values... by doing what
you''ve done above you''re letting the rails database adapters
convert Ruby''s true into something the database understands...
> Turns out this still isn''t portable. On PostgreSQL I have to run:
> 
>   Job.where(''(requested_start_date < ?) AND (complete IS NOT
TRUE)'',
> Date.today)
> 
> but this is still blowing up SQLite3. Sadness. Back to the drawing
> board; any suggestions?
I''d have to look it up, but "IS" and "IS NOT" are
not always the same thing as "=" and "<>" when it
comes to NULL values.  In postgresql you can''t say "WHERE
something = NULL"  You have to use IS or IS NOT.  I''m sure
it''s similar for other databases.

You might try "complete IS NOT ?" and pass in ruby''s true.  I
don''t use sqlite much so don''t know what it
does/doesn''t support.

If you find out I''m wrong or off on any of the above, post back to the
list... you''re not the only that this catches.  My personal annoyance
is the MySQL doing case insensitive searches on LIKE and Postgresql doing case
*sensitive* searches on LIKE.  Gets me every time :/

-philip

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Mar 11, 7:43 pm, "Todd A. Jacobs"
<codegnome.consult...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
wrote:
> On Mar 11, 4:31 pm, "Todd A. Jacobs"
<codegnome.consult...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org>
> wrote:
>
> > So, after more trial and error, this is the portable version.
>
> >   scope :past_due,
> >         where(''(requested_start_date < ?) AND (complete =
?)'',
> > Date.today, true).
> >         order(''requested_start_date ASC'')
>
> > I''d still like to understand *why* this is more portable,
though, if
> > anybody knows the answer.
>
> Turns out this still isn''t portable. On PostgreSQL I have to run:
>
>    Job.where(''(requested_start_date < ?) AND (complete IS NOT
TRUE)'',
> Date.today)
>
> but this is still blowing up SQLite3. Sadness. Back to the drawing
> board; any suggestions?
What type is ''complete'' declared as? From this list, it looks
like PG
supports a real boolean type:

http://troels.arvin.dk/db/rdbms/#data_types-boolean

Looking at the ActiveRecord source, it looks like doing this:

SomeModel.find(:conditions => [''boolean_field = ?'', true])

will get the SQL:

SELECT * FROM some_models WHERE boolean_field = ''t''

which is pretty wrong. The behavior is inherited from other DBs that
don''t have a real boolean type (MySQL, for instance, aliases
TINYINT(1) to BOOLEAN, SQLite tends to store ''t'' and
''f'', and SQL
Server devotees store 0xFF and 0x00).

Perhaps the Postgres adapter should override quoted_true and
quoted_false (in quoting.rb) to return a more appropriate value for
boolean columns? I''m guessing that this SQL would work:

SELECT * FROM some_models WHERE boolean_field = TRUE

--Matt Jones

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

>>> So, after more trial and error, this is the portable version.
>> 
>>>   scope :past_due,
>>>         where(''(requested_start_date < ?) AND (complete
= ?)'',
>>> Date.today, true).
>>>         order(''requested_start_date ASC'')
>> 
>>> I''d still like to understand *why* this is more portable,
though, if
>>> anybody knows the answer.
>> 
>> Turns out this still isn''t portable. On PostgreSQL I have to
run:
>> 
>>    Job.where(''(requested_start_date < ?) AND (complete IS
NOT TRUE)'',
>> Date.today)
>> 
>> but this is still blowing up SQLite3. Sadness. Back to the drawing
>> board; any suggestions?
> 
> What type is ''complete'' declared as? From this list, it
looks like PG
> supports a real boolean type:
> 
> http://troels.arvin.dk/db/rdbms/#data_types-boolean
> 
> Looking at the ActiveRecord source, it looks like doing this:
> 
> SomeModel.find(:conditions => [''boolean_field = ?'',
true])
> 
> will get the SQL:
> 
> SELECT * FROM some_models WHERE boolean_field = ''t''
> 
> which is pretty wrong. The behavior is inherited from other DBs that
> don''t have a real boolean type (MySQL, for instance, aliases
> TINYINT(1) to BOOLEAN, SQLite tends to store ''t'' and
''f'', and SQL
> Server devotees store 0xFF and 0x00).
> 
> Perhaps the Postgres adapter should override quoted_true and
> quoted_false (in quoting.rb) to return a more appropriate value for
> boolean columns? I''m guessing that this SQL would work:
> 
> SELECT * FROM some_models WHERE boolean_field = TRUE
Postgres doesn''t mind "= ''t''".  On a table
of mine where is_enabled is a boolean field, all of the below return the same
number.

select count(*) from admin_users where is_enabled = true;
select count(*) from admin_users where is_enabled is true;
select count(*) from admin_users where is_enabled = ''t'';

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.

On Mar 12, 10:07 am, Matt Jones
<al2o...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
> SELECT * FROM some_models WHERE boolean_field = TRUE
This doesn''t actually work across both SQLite3 and PostgreSQL. The
only portable solution I found was:

  SELECT * FROM model WHERE (boolean_field)

In other words, trying to test IS, IS NOT, or = all failed horribly on
one platform or the other, but simply testing for the field itself
returned the proper equality test. I''m not sure why they failed, mind
you, just that empirically there''s something fundamentally wrong with
the way the equality is being tested across the supported platforms.

In the end, I ended up with the following named scope in my model:

  scope :past_due,
        where(''(requested_start_date < ?) AND NOT
complete'',
Date.today).
        order(''requested_start_date ASC'')

Whether this is a bug, or simply an ugly edge case, I will leave up to
those wiser than myself.

-- 
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk+unsubscribe@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.