Code snippet:
conditions = {:org => ["ABC", "XYZ"]} Defect.count :conditions => conditions
work as expected.
This doesn't:
conditions = {:org => ["ABC", "XYZ", nil]} Defect.count :conditions => conditions
Get error: ActiveRecord::ActiveRecordError: Syntax error: Encountered "NULL" at line 1, column 87.: SELECT count(*) AS count_all FROM defects WHERE (defects.org IN ('ABC','XYZ',NULL))
Any idea?
SQL does not use the equality comparison for null values, it uses 'IS
NULL' instead. Convenient, isn't it? 
Apparently Rails' magic does not go as far as to check if the NIL value is one of the values in the array to generate the correct SQL statement so I think you'll have to do it yourself and generate a string such as:
conditions = "org is null or org in ('ABC', 'XYZ')"
This should be so easy to implement that I am not sure why it has not already been done.
pepe wrote:
SQL does not use the equality comparison for null values, it uses 'IS NULL' instead. Convenient, isn't it?
Apparently Rails' magic does not go as far as to check if the NIL value is one of the values in the array to generate the correct SQL statement so I think you'll have to do it yourself and generate a string such as:
conditions = "org is null or org in ('ABC', 'XYZ')"
This should be so easy to implement that I am not sure why it has not already been done.
Found a bug/workaround/fix. Not sure which.
conditions = {:org => ["ABC", "XYZ", "NULL"]} Defect.count :conditions => conditions
Don't know what to do if the org name really is "NULL".
John Duu wrote:
conditions = {:org => ["ABC", "XYZ", "NULL"]} Defect.count :conditions => conditions
Don't know what to do if the org name really is "NULL".
The string "NULL" is not the same as a NULL value in a field. I doubt what you're showing here will generate the proper SQL as shown earlier by pepe:
pepe wrote:
conditions = "org is null or org in ('ABC', 'XYZ')"
You need to actually check the generated SQL in your development log to make sure it looks something like the above.
Found a bug/workaround/fix. Not sure which.
conditions = {:org => ["ABC", "XYZ", "NULL"]} Defect.count :conditions => conditions
Just ran a quick test on a table of mine with 7 records (6 with the same value in the column being searched and one record with a value of null in it) and your code above does not work for me (using MSSQL). It just doesn't produce an error but the record with a NULL value is not counted. The only way I found to count the NULL, *and only the null*, with the syntax you've been using is this:
condition = {:my_column => nil}
Don't know what to do if the org name really is "NULL".
As far as I can see you will need to either modify your condition value or run 2 separate counts, one for the nil ones and another one for the other values. This code should help if you want to go with the first option, I think:
YourModel.count :conditions => "your_column is null or your_column in ('#{your_values.join('\',\'')}')"
This assumes 'your_values' is an array. The code above will probably not work, however, if 'your_values' is empty as the content of your parenthesis will end up looking like this: ('')
Then the query would select (if they exist) records with an empty value in 'your_column', which is something you might want to consider as well since 'your_column' contains strings. So something like this should work:
YourModel.count :conditions => "your_column is null or your_column = '' or your_column in ('#{your_values.join('\',\'')}')"
If there is a better way I'd like to know.
pepe wrote:
SQL does not use the equality comparison for null values, it uses 'IS NULL' instead. Convenient, isn't it?
Right, because NULL really means "unknown", and SQL uses 3-valued logic, so NULL == NULL actually returns NULL, not TRUE or FALSE -- that is, we don't know whether the two NULL values are "really" equal. (Unlike in Ruby, where nil and false are both false in a logical context, SQL NULL is not FALSE.)
So, if I remember my SQL correctly: NULL == TRUE yields FALSE NULL == FALSE yields FALSE NULL == NULL yields NULL which is why it's necessary to have the special IS NULL syntax: NULL IS NULL yields TRUE
Apparently Rails' magic does not go as far as to check if the NIL value is one of the values in the array to generate the correct SQL statement so I think you'll have to do it yourself and generate a string such as:
conditions = "org is null or org in ('ABC', 'XYZ')"
I think you're right. I don't see another good way to do it. I wonder, though, if COALESCE() would be useful in certain cases.
This should be so easy to implement that I am not sure why it has not already been done.
Interesting question. I've never needed this except as part of complex queries where I'm already writing SQL anyway, but YMMV.
Best,
> This should be so easy to implement that I am not sure why it has not > already been done.
Interesting question. I've never needed this except as part of complex queries where I'm already writing SQL anyway, but YMMV.
It occurred to me while working on my very first RoR gig. I 'inherited' a DB with a column for the status of the record where NULL and the empty value 'meant' the same thing and an asterisk (*) meant that the record was 'soft-deleted'. I ended up writing a lot of functionality just to get the correct records. It didn't help that I didn't know that much RoR at the time. Not that I know much more now... However I could see that the problem I was facing should be pretty common and was hoping to find an ActiveRecord solution, but I never did find it.
Robert Walker wrote:
John Duu wrote:
conditions = {:org => ["ABC", "XYZ", "NULL"]} Defect.count :conditions => conditions
Don't know what to do if the org name really is "NULL".
The string "NULL" is not the same as a NULL value in a field. I doubt what you're showing here will generate the proper SQL as shown earlier by pepe:
pepe wrote:
conditions = "org is null or org in ('ABC', 'XYZ')"
You need to actually check the generated SQL in your development log to make sure it looks something like the above.
Seem to be picking up nil correctly.
code snippet: conditions = {:org => ["NULL"]} defects = Defect.first :conditions => conditions puts defects.inspect
output: Defect Load (15.0ms) SELECT * FROM defects WHERE (defects.org IN ('NULL')) FETCH FIRST 1 ROWS ONLY
#<Defect id: 1, org: nil, created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
I'm using JRuby 1.5.1 with JavaDB. Haven't tried with mysql or other db.
Not too familiar with raw sql, so don't know if WHERE (defects.org IN ('NULL')) is equivalent to org IS NULL or if this syntax is a JavaDB specific.
John Duu wrote: [...]
Not too familiar with raw sql,
Then get familiar with it! You can't use ActiveRecord effectively without knowing SQL. It's an abstraction layer, not a crutch. You need to know what's being abstracted
so don't know if WHERE (defects.org IN ('NULL')) is equivalent to org IS NULL or if this syntax is a JavaDB specific.
It is not equivalent at all. See the apostrophes around 'NULL'? That means it's the literal string NULL, not the SQL null value.
If you knew SQL, this would be trivially self-evident. Please learn SQL.
Best,
Marnen Laibow-Koser wrote:
John Duu wrote: [...]
so don't know if WHERE (defects.org IN ('NULL')) is equivalent to org IS NULL or if this syntax is a JavaDB specific.
It is not equivalent at all. See the apostrophes around 'NULL'? That means it's the literal string NULL, not the SQL null value.
If you knew SQL, this would be trivially self-evident. Please learn SQL.
Best, -- Marnen Laibow-Koser http://www.marnen.org marnen@marnen.org
If WHERE (defects.org IN ('NULL')) select the string literal 'NULL', then the data selected is wrong, no?
I would expect #<Defect id: 1, org: "NULL", created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
but instead, I actually get
#<Defect id: 1, org: nil, created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
(org is string data type).
John Duu wrote:
Marnen Laibow-Koser wrote:
John Duu wrote: [...]
so don't know if WHERE (defects.org IN ('NULL')) is equivalent to org IS NULL or if this syntax is a JavaDB specific.
It is not equivalent at all. See the apostrophes around 'NULL'? That means it's the literal string NULL, not the SQL null value.
If you knew SQL, this would be trivially self-evident. Please learn SQL.
Best, -- Marnen Laibow-Koser http://www.marnen.org marnen@marnen.org
If WHERE (defects.org IN ('NULL')) select the string literal 'NULL', then the data selected is wrong, no?
I would expect #<Defect id: 1, org: "NULL", created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
but instead, I actually get
#<Defect id: 1, org: nil, created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
(org is string data type).
Hmm. I wonder if JavaDB stores null values as the literal "NULL", or if the Ruby/JavaDB adaptor is at fault.
In either case, your database (or its adaptor) is doing something silly (in that there is apparently no way to distinguish between NULL and "NULL"), and you should certainly not rely on this silliness.
Best,
If WHERE (defects.org IN ('NULL')) select the string literal 'NULL', then the data selected is wrong, no?
I would expect #<Defect id: 1, org: "NULL", created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
but instead, I actually get
#<Defect id: 1, org: nil, created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
(org is string data type).
Hmm. I wonder if JavaDB stores null values as the literal "NULL", or if the Ruby/JavaDB adaptor is at fault.
In either case, your database (or its adaptor) is doing something silly (in that there is apparently no way to distinguish between NULL and "NULL"), and you should certainly not rely on this silliness.
Best, -- Marnen Laibow-Koser http://www.marnen.org marnen@marnen.org
I set up a test using sql3lite & mysql. Both responded the same way.
Look like 'NULL' in the IN clause is a reserve word. For a string literal NULL, you'll have to quote it.
So WHERE (defects.org IN ('NULL')) selects nil
and WHERE (defects.org in ('"NULL"')) select the literal "NULL".
John Duu wrote: [...]
I set up a test using sql3lite & mysql. Both responded the same way.
Can I see your test code?
Look like 'NULL' in the IN clause is a reserve word. For a string literal NULL, you'll have to quote it.
So WHERE (defects.org IN ('NULL')) selects nil
and WHERE (defects.org in ('"NULL"')) select the literal "NULL".
I'd be surprised. '"NULL"' should represent a 6-character string beginning and ending with a double-quote character, not the 4-character string just consisting of N, U, L, L.
Best,
If WHERE (defects.org IN ('NULL')) select the string literal 'NULL', then the data selected is wrong, no?
I would expect #<Defect id: 1, org: "NULL", created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
but instead, I actually get
#<Defect id: 1, org: nil, created_at: "2010-10-04 00:14:20", updated_at: "2010-10-04 00:14:20>
(org is string data type).
Hmm. I wonder if JavaDB stores null values as the literal "NULL", or if the Ruby/JavaDB adaptor is at fault.
In either case, your database (or its adaptor) is doing something silly (in that there is apparently no way to distinguish between NULL and "NULL"), and you should certainly not rely on this silliness.
Best, -- Marnen Laibow-Koser http://www.marnen.org marnen@marnen.org
I set up a test using sql3lite & mysql. Both responded the same way.
Look like 'NULL' in the IN clause is a reserve word. For a string literal NULL, you'll have to quote it.
So WHERE (defects.org IN ('NULL')) selects nil
and WHERE (defects.org in ('"NULL"')) select the literal "NULL".
Not for me... osx, mysql 5.1.46..... sounds like your adapter is doing something it shouldn't be...