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?
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?
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
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
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:
Comparison of different SQL implementations
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';
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.
