Forum: Ruby on Rails query on NULL colum cannot pick it up

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Ee0293412faca21e4ebf3326ed9d5f8c?d=identicon&s=25 Kad Kerforn (kadoudal)
on 2012-06-08 16:05
(Received via mailing list)
I get a strange result upon querying DB records in which the column
(VARCHAR) is set to NULL by default

the generated query is :
SELECT * FROM `contents` WHERE `contents`.`type` IN ('Article') AND
(state <> 'draft');

when contents.state is NULL, then the record IS NOT selected
when contents.state is'' ( empty ) then the record is selected
( obviously when set to any other value too..)

is it the 'standard' behavior ? ( so I'll have to change the
default ..)
or should I modify /add anything in the query ?

thanks for feedback
15eb13d662803f57bc4aea59704988b4?d=identicon&s=25 Scott Ribe (Guest)
on 2012-06-08 16:22
(Received via mailing list)
On Jun 8, 2012, at 8:04 AM, Erwin wrote:

> when contents.state is NULL, then the record IS NOT selected
> when contents.state is'' ( empty ) then the record is selected
> ( obviously when set to any other value too..)

Yes, it's how SQL works--NULL is 'unknown', so comparison with any value
returns null. You need a SQL primer, and look especially for
three-valued logic.

--
Scott Ribe
scott_ribe@elevated-dev.com
http://www.elevated-dev.com/
(303) 722-0567 voice
Ee0293412faca21e4ebf3326ed9d5f8c?d=identicon&s=25 Kad Kerforn (kadoudal)
on 2012-06-08 22:44
(Received via mailing list)
thanks .. I am using now a '' (empty) default and it works
This topic is locked and can not be replied to.