Skip to content

HTTPS clone URL

Subversion checkout URL

You can clone with HTTPS or Subversion.

Download ZIP

Loading…

Using conditions with nil generates incorrect query in Sqlite (possibly other drivers as well) #209

Closed
armanx opened this Issue · 1 comment

2 participants

@armanx

Using conditions with Datamapper 1.2.0 don't translate correctly when using nil in queries. Below script demonstrates the errors:

require 'rubygems'
require 'data_mapper'

DataMapper::Logger.new($stdout, :debug)
dm = DataMapper.setup(:default, 'sqlite::memory:')

class Post
  include DataMapper::Resource

  property :id,         Serial    
  property :title,      String
  property :body,       Text
  property :published,  Boolean
  property :created_at, DateTime
end

DataMapper.auto_migrate!

p1 = Post.create(
  :title => "Post 1",
  :body => "hello world",
  :published => true,
  :created_at => Time.now
)
p p1

p2 = Post.create(
  :title => "Post 2",
  :body => "hello again, world",
  :published => false,
  :created_at => Time.now
)
p p2

p3 = Post.create(
  :title => "Post 3",
  :body => "goodbye world",
  #:published => false,
  :created_at => Time.now
)
p p3

puts "-----"

false_posts = Post.all( :conditions => ["published = ? ", false] )
puts "Posts that are published => false:"
p false_posts
# WORKS, AS EXPECTED

puts "-----"

empty_posts = Post.all(:published => nil)
puts "Posts that are published => nil:"
p empty_posts
# ALSO WORKS

puts "-----"

nil_posts = Post.all( :conditions => ["published = ? ", nil] )
puts "Posts that are published => nil:"
p nil_posts
# DOESN'T WORK

puts "-----"

empty_posts = Post.all( :conditions => ["published = ? OR published = ? ", nil, false] )
puts "Posts that are published => false or nil:"
p empty_posts
# DOESN'T WORK, ONLY GET published => false

puts "-----"

empty_posts = Post.all(:published => nil) | Post.all(:published => false)
puts "Posts that are published => false or nil:"
p empty_posts
# THIS WORKS

The error occurs when DM translates the condition into

SELECT "id", "title", "published", "created_at" FROM "posts" WHERE (published = NULL OR published = 'f' )

instead of

SELECT "id", "title", "published", "created_at" FROM "posts" WHERE ("published" IS NULL OR "published" = 'f') ORDER BY "id"
@dkubb
Owner

When using raw conditions like that DataMapper just interpolates the variables into the placeholders. It does not rewrite the SQL query. In this specific case, you can't merge nil into that query, substitute it for the ? and have it be valid SQL. You'd need to write the conditions as [ 'published IS NULL OR publised = ?', false ].

@dkubb dkubb closed this
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.