Rails 3 SQLite3 Boolean false

SQLite uses 1 for true and 0 for false:

SQLite does not have a separate Boolean storage class. Instead, Boolean values are stored as integers 0 (false) and 1 (true).

But SQLite also has a loose type system and automatically casts things so your 'f' is probably being interpreted as having a truthiness of "true" simply because it isn't zero.

A bit of digging indicates that you have found a bug in the Rails 3.0.7 SQLiteAdapter. In active_record/connection_adapters/abstract/quoting.rb, we find these:

def quoted_true  "'t'"enddef quoted_false  "'f'"end

So, by default, ActiveRecord assumes that the database understands 't' and 'f' for boolean columns. The MySQL adaptor overrides these to work with its tinyint implementation of boolean columns:

QUOTED_TRUE, QUOTED_FALSE = '1'.freeze, '0'.freeze#...def quoted_true  QUOTED_TRUEenddef quoted_false  QUOTED_FALSEend

But the SQLite adapter does not provide its own implementations of quoted_true or quoted_false so it gets the defaults which don't work with SQLite's booleans.

The 't' and 'f' booleans work in PostgreSQL so maybe everyone is using PostgreSQL with Rails 3 or they're just not noticing that their queries aren't working properly.

I'm a little surprised by this and hopefully someone can point out where I've gone wrong, you can't be the first person to use a boolean column in SQLite with Rails 3.

Try monkey patching def quoted_true;'1';end and def quoted_false;'0';end into ActiveRecord::ConnectionAdapters::SQLiteAdapter (or temporarily hand-edit them into active_record/connection_adapters/sqlite_adapter.rb) and see if you get sensible SQL.

I ran across this as well, here's how to monkey patch:

require 'active_record/connection_adapters/sqlite_adapter'module ActiveRecord  module ConnectionAdapters    class SQLite3Adapter < SQLiteAdapter      def quoted_true; '1' end      def quoted_false; '0' end    end  endend

I don't get how I'm still running across this bug??

You may find useful the following code snippet for adding compatibility with SQLite boolean columns actually working on Rails 4 (also posted at https://gist.github.com/ajoman/9391708):

# config/initializers/sqlite3_adapter_patch.rbmodule ActiveRecord  module ConnectionAdapters    class SQLite3Adapter < AbstractAdapter      QUOTED_TRUE, QUOTED_FALSE = "'t'", "'f'"      def quoted_true        QUOTED_TRUE      end      def quoted_false        QUOTED_FALSE      end    end  endend