Ransack: != NULL breaking postgres

[benlieb]

The following query using the is_present predicate does not work in postgres :

SELECT "app_logs".* FROM "app_logs" LEFT OUTER JOIN "users" ON "users"."id" = "app_logs"."user_id" WHERE (("users"."username" ILIKE '%lieb%' OR "users"."email" ILIKE '%blah%') AND ("app_logs"."user_id" IS NOT NULL AND "app_logs"."user_id" != NULL))

Specifically

"app_logs"."user_id" != NULL

A chat on freenode in postgressql yielded this quaint exchange:

[11:27am] benlieb: I'm using a ruby gem (ransack that makes queries dynamically). Moving from mysql to postgres has caused some issues. The following generated query no longer works in postgres: SELECT "app_logs".* FROM "app_logs" LEFT OUTER JOIN "users" ON "users"."id" = "app_logs"."user_id" WHERE (("users"."username" ILIKE '%lieb%' OR "users"."email" ILIKE '%blah%') AND ("app_logs"."user_id" IS NOT NULL AND "app_logs"."user_id" != NULL))
[11:27am] speeddragon left the chat room. (Ping timeout: 240 seconds)
[11:27am] benlieb: I've traced it to "app_logs"."user_id" IS NOT NULL AND "app_logs"."user_id" != NULL
[11:27am] mst: != NULL
[11:27am] mst: seriously
[11:27am] mst: that's completely broken
[11:27am] benlieb: the second half breaks it
[11:27am] mst: yes. because SQL doesn't work that way.
[11:28am] wvaske joined the chat room.
[11:28am] Suguki joined the chat room.
[11:28am] mst: that second clause makes no sense at all
[11:28am] mst: it's the first clause, repeated, except broken
[11:28am] _djbkd joined the chat room.
[11:28am] linuxhiker: mst: be nice not everyone understands how null works
[11:28am] benlieb: so I thought so
[11:28am] mst: figure out why it generated that and make it not do that
[11:28am] benlieb: and unfortunately it looks like a bug, so I'm SOL
[11:29am] mst: it's open source
[11:29am] mst: find the bit that broke and hit it with a mallet
[11:29am] mst: then go hit the author with a mallet for not understanding SQL or NULL

[seanlinsley]

Please post the code that's generating that query.

[benlieb]

irb(main):014:0> AppLog.ransack(user_id_present: 1).result.to_sql
=> "SELECT \"app_logs\".* FROM \"app_logs\" WHERE (\"app_logs\".\"user_id\" IS NOT NULL AND \"app_logs\".\"user_id\" != NULL)"

I think it's because it's a INT field?

Since the string field produces:

irb(main):015:0> AppLog.ransack(msg_present: 1).result.to_sql
=> "SELECT \"app_logs\".* FROM \"app_logs\" WHERE (\"app_logs\".\"msg\" IS NOT NULL AND \"app_logs\".\"msg\" != '')"

[seanlinsley]

I haven't used the present predicate before. I normally use null instead:

>> User.ransack(id_null: true).result.count
   (2.0ms)  SELECT COUNT(*) FROM "users" WHERE "users"."id" IS NULL
 => 0 
>> User.ransack(id_null: false).result.count
   (2.7ms)  SELECT COUNT(*) FROM "users" WHERE ("users"."id" IS NOT NULL)
 => 1234 

[avit]

Duplicates #641

[jonatack]

Thanks everyone. Since @avit has linked it to the previous issue (well done), let's close this one to avoid duplicates.