Rails Ruby on Rails

Comments and changes to this ticket

• You flagged this item as spam.
  

  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 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.

• You flagged this item as spam.
  

  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;

  1|1|1|False

  2|1|1|True

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

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

  2|1|1|True

  sqlite>

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

• 

  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.

• You flagged this item as spam.
  

  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. :)

• You flagged this item as spam.
  

  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 http://www.sqlite.org/datatype3.html ). Otherwise boolean logic in SQL breaks. For example:

  sqlite> SELECT * FROM "foos" WHERE active;
  sqlite> SELECT * FROM "foos" WHERE NOT active;
  1|bar|t
  2|baz|f

  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? ;)