On Oct 4, 2010, at 2:53 PM, John Duu wrote:
>>>
>>> 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".
Not for me... osx, mysql 5.1.46..... sounds like your adapter is doing
something it shouldn't be...
mysql> desc foo;
+-------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| s | varchar(100) | YES | | NULL | |
+-------+--------------+------+-----+---------+-------+
1 row in set (0.01 sec)
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 |
+----------+
--
You received this message because you are subscribed to the Google Groups "Ruby
on Rails: Talk" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en.
