#1085 ✓resolved
Jacques Crocker

DM Query generates SQL with empty set e.g. "WHERE id IN ( )"

Reported by Jacques Crocker | October 8th, 2009 @ 12:28 AM

Latest version of datamapper has an edge case (when combined with dm-aggregates in this case) where it will produce SQL with "IN ()". This works fine on sqlite3, however will explode on MySQL.

Here's a snippet to replicate the error. It will not throw an error until you use a mysql adapter, however you can view the SQL output and verify that its generating incorrect syntax on the SELECT

DataMapper::Logger.new(STDOUT, :debug) 
DataMapper.setup(:sqlite3, 'sqlite3::memory:')
DataMapper::Repository.adapters[:default] =  DataMapper::Repository.adapters[:sqlite3]

class Category
  include DataMapper::Resource
  property :id, Serial
  property :name, String

  has n, :posts
end

class Post
  include DataMapper::Resource
  property :id, Serial

  belongs_to :category
end

DataMapper.auto_migrate!

c = Category.new :name => "Food"
puts c.posts.count

here is the STDOUT:

 ~ (0.000322) SELECT sqlite_version(*)
 ~ (0.000187) DROP TABLE IF EXISTS "categories"
 ~ (0.000025) DROP TABLE IF EXISTS "posts"
 ~ (0.000022) PRAGMA table_info("categories")
 ~ (0.000297) CREATE TABLE "categories" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "name" VARCHAR(50))
 ~ (0.000009) PRAGMA table_info("posts")
 ~ (0.000111) CREATE TABLE "posts" ("id" INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT, "category_id" INTEGER NOT NULL)
 ~ (0.000115) CREATE INDEX "index_posts_category" ON "posts" ("category_id")
 ~ (0.000042) SELECT COUNT(*) FROM "posts" WHERE "category_id" IN ()

notice the last line is where the error is

Comments and changes to this ticket

Please Sign in or create a free account to add a new ticket.

With your very own profile, you can contribute to projects, track your activity, watch tickets, receive and update tickets through your email and much more.

New-ticket Create new ticket

Create your profile

Help contribute to this project by taking a few moments to create your personal profile. Create your profile »

People watching this ticket

Tags

Referenced by

Pages