searchlogic uses NULL value on search by date (created_at_gte)

[spovich]

Doing a simple search on the created_at column will produce SQL that has a NULL value for the date.

In the form, I input in ISO date format (YYYY-MM-DD), and the values shows in the params in the logs:

Parameters: {"commit"=>"Search", "action"=>"index", "controller"=>"foos", "search"=>{"city_like"=>"", "created_at_gte"=>"2009-04-13"}}

But the resulting SQL gives a NULL for the date:

SELECT * FROM "foos" WHERE (foos.created_at >= NULL)

[spovich]

Ok, the issue is that searchlogic silently makes a nil when you provide a date for a datetime field. However, ruby converts date to time without a problem:

Time.parse "Jan 1, 2009" => Thu Jan 01 00:00:00 -0800 2009

So, this seems to be a bug to me. I'll write some failing specs if you agree that it should be able to convert a date to a datetime when searching.

[spovich]

Taking another look and I see search_spec.rb line 267 is a search of a datetime that is given an date, so we agree that a Date should be upcast to a dateTime.

The specs all pass for me using the SQLite db, but trying the same thing with a stock rails app 2.3.4 and ruby 1.8.7 in IRB (console) with Postgres 8.3 and latest pg gem (0.8.0) will fail (running on OS X):

>> s = Message.search

=> #<Searchlogic::Search:0x35e6ad4 @klass=Message(id: integer, type: string, uuid: string, title: string, description: text, created_at: datetime, updated_at: datetime), @current_scope=nil>

>> s.created_at_after = 'Jan 1, 2009'
=> "Jan 1, 2009"
>> s.created_at_after
=> nil

I don't know how to make this fail in SQLIte, but it is clearly failing in postgres. Any ideas?

Thanks, John

[spovich]

Ok, so I modified the searchlogic test spec's to use postgres, and everything passes except the tests where LIKE becomes ILIKE. Must be something in our app, so will need to dig in further.

[jwachira]

hopefully you are not using validates_date_time plugin which cast a datetime field to nil, I nuked the plugin from my project, all is working fine! yunks!

[spovich]

Yea, I eventually found that was the problem also. We switched to validates_timeliness which solved it. Looking at the code for validates_date_time reinforces the point that you should always review the code in plugins (some ugly code in there). If I had looked at it first, I would never have used it!

Thanks and sorry that I didn't follow up here with my findings before (maybe saved you some trouble).

[spovich]

didn't realize this wasn't closed.. is now!