This project is archived and is in readonly mode.

#66 ✓invalid

True/False conditions broken for sqlite

Reported by Tobin | April 30th, 2008 @ 03:15 AM

The current SQLiteAdapter does not format true/false conditions correctly for queries. It is using the default quoted_true and quoted_false methods.

The ruby code

User.find(:all, :conditions => ['active = ?', true])

produces the incorrect SQL: SELECT * FROM invites WHERE (is_accepted = 't')

This SQL will not select the active rows.

The correct SQL is: SELECT * FROM invites WHERE (is_accepted = 'True')

This is true for both sqlite2 and sqlite3. I have attached a patch which should rectify the problem. I looked at creating a unit test for this, but couldn't figure out where or how to add a test like this.

Comments and changes to this ticket

  • Tobin

    Tobin April 30th, 2008 @ 03:18 AM

    Crap, totally screwed up my SQL. Here is the correct SQL for the Ruby code I posted.

    User.find(:all, :conditions => ['active = ?', true])

    Current, incorrect SQL - SELECT * FROM users WHERE (active = 't')

    New, correct SQL - SELECT * FROM users WHERE (active = 'True')

  • DHH

    DHH April 30th, 2008 @ 10:02 PM

    • State changed from “new” to “invalid”

    Using 't' and 'f' seems to work fine on sqlite3:

    sqlite> SELECT * FROM "posts" WHERE (live = 't');

    2|ab|2008-04-30 20:58:22|2008-04-30 20:58:22|t

    sqlite> SELECT * FROM "posts" WHERE (live = 'f');

    1|a|2008-04-30 20:58:14|2008-04-30 20:58:14|f

    If you can find a failing test case, you can put it in finder_test.rb in AR.

  • Tobin

    Tobin April 30th, 2008 @ 11:31 PM

    Hmm, that's weird, it definitely does not work for me. I'm on OS X 10.5, with SQLite 3.5.2 from MacPorts.

    $ sqlite3 development.sqlite3

    SQLite version 3.5.2

    Enter ".help" for instructions

    sqlite> select * from connections;



    sqlite> select * from connections where active = 't';

    sqlite> select * from connections where active = 'True';



    I guess I'll do a little more digging, see if I can figure something out.

  • DHH

    DHH April 30th, 2008 @ 11:38 PM

    How where those connections created? I think SQLite actually just compares the string, doesn't it? So if you created those rows with something else than Rails, which might have used True instead of t, the comparison will fail. But Rails uses t on both insert/query.

  • Tobin

    Tobin May 1st, 2008 @ 02:05 AM

    You're correct, I created those records outside Rails. After some further testing, it is working as it should.

    Thanks for the debugging help. :)

  • Jon Jensen

    Jon Jensen March 16th, 2011 @ 07:34 PM

    • Tag set to activerecord, bug, patch, sqlite

    Any chance we reopen this ticket? The proposed patch was wrong but the idea is right. Rails is putting the strings "t" and "f" in these columns, when the more appropriate values would be 1 and 0 (see ). Otherwise boolean logic in SQL breaks. For example:

    sqlite> SELECT * FROM "foos" WHERE active;
    sqlite> SELECT * FROM "foos" WHERE NOT active;

    In SQLite boolean logic, the strings "t" and "f" both evaluate to false (0). Even if you only access the database through Rails, this is a nice-to-fix, as it makes for simpler finders and scopes... e.g. ":conditions => 'active'" is prettier than ":conditions => ['active = ?', true]". You can already do the former in MySQL/Postgres, just not SQLite.

    We wouldn't say "if foo == true" in Ruby, so why should we say "WHERE foo = 't'" in SQL? ;)

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href=""></a>

People watching this ticket