This project is archived and is in readonly mode.
True/False conditions broken for sqlite
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.
-
sqlite.patch
646 Bytes
-
TobinCrap, 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- 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
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
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
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
- 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|fIn 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? ;)