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? -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
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. On Oct 4, 2:48 am, John Duu <li...-fsXkhYbjdPsEEoCn2XhGlw@public.gmane.org> wrote:> 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? > -- > Posted viahttp://www.ruby-forum.com/.-- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
John Duu
2010-Oct-04 17:24 UTC
Re: How to write an ActiveRecord conditions containing null?
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". -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Robert Walker
2010-Oct-04 18:07 UTC
Re: How to write an ActiveRecord conditions containing 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. -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> Found a bug/workaround/fix. Not sure which. > > conditions = {:org => ["ABC", "XYZ", "NULL"]} > Defect.count :conditions => conditionsJust 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. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser
2010-Oct-04 18:41 UTC
Re: How to write an ActiveRecord conditions containing null?
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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
> > 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. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe@googlegroups.com. For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
John Duu
2010-Oct-04 21:36 UTC
Re: How to write an ActiveRecord conditions containing null?
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. -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser
2010-Oct-04 21:39 UTC
Re: How to write an ActiveRecord conditions containing null?
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 marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
John Duu
2010-Oct-04 21:44 UTC
Re: How to write an ActiveRecord conditions containing null?
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.orgIf 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). -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser
2010-Oct-04 21:48 UTC
Re: How to write an ActiveRecord conditions containing null?
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-sbuyVjPbboAdnm+yROfE0A@public.gmane.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, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
John Duu
2010-Oct-04 21:53 UTC
Re: How to write an ActiveRecord conditions containing null?
>> >> 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-sbuyVjPbboAdnm+yROfE0A@public.gmane.orgI 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". -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Marnen Laibow-Koser
2010-Oct-04 21:55 UTC
Re: How to write an ActiveRecord conditions containing 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, -- Marnen Laibow-Koser http://www.marnen.org marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.org -- Posted via http://www.ruby-forum.com/. -- 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.
Philip Hallstrom
2010-Oct-04 22:08 UTC
Re: Re: How to write an ActiveRecord conditions containing null?
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 >> marnen-sbuyVjPbboAdnm+yROfE0A@public.gmane.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... 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 rubyonrails-talk-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk+unsubscribe-/JYPxA39Uh5TLH3MbocFF+G/Ez6ZCGd0@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en.