How to write an ActiveRecord conditions containing null?

jdunneuk · October 4, 2010, 8:48am

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?

jdunneuk · October 4, 2010, 3:17pm

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.

jdunneuk · October 4, 2010, 7:24pm

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

jdunneuk · October 4, 2010, 8:07pm

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.

jdunneuk · October 4, 2010, 8:09pm

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.

jdunneuk · October 4, 2010, 8:41pm

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

jdunneuk · October 4, 2010, 9:27pm

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.

jdunneuk · October 4, 2010, 11:36pm

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

jdunneuk · October 4, 2010, 11:44pm

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
[email protected]

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

jdunneuk · October 4, 2010, 11:39pm

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
http://www.marnen.org
[email protected]

jdunneuk · October 4, 2010, 11:53pm

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
[email protected]

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

jdunneuk · October 4, 2010, 11:48pm

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
[email protected]

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
[email protected]

jdunneuk · October 5, 2010, 12:10am

On Oct 4, 2010, at 2:53 PM, John Duu wrote:

“NULL”), and you should certainly not rely on this silliness.
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…

mysql> insert into foo values (‘NULL’);
mysql> insert into foo values (NULL);
mysql> insert into foo values (‘abc’);

mysql> select * from foo;
±-----+
| s |
±-----+
| NULL |
| NULL |
| abc |
±-----+

mysql> select count() from foo where s IS NULL;
±---------+
| count() |
±---------+
| 1 |
±---------+

mysql> select count() from foo where s IN (‘NULL’);
±---------+
| count() |
±---------+
| 1 |
±---------+

mysql> select count() from foo where s IN (NULL);
±---------+
| count() |
±---------+
| 0 |
±---------+

mysql> select count() from foo where s IN (‘“NULL”’);
±---------+
| count() |
±---------+
| 0 |
±---------+

jdunneuk · October 4, 2010, 11:55pm

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,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]