If CPK contains non-integer key, find_each builds invalid query #148

Closed
crosspath opened this Issue Feb 27, 2013 · 6 comments

2 participants

@crosspath

Problem

Model has composite primary key [string, integer, integer]. Query for find_each is invalid. Error is shown.
Using PostgreSQL and Rails 3.

Steps

Create model

class Title < ActiveRecord::Base
  belongs_to :lang
  attr_accessible :entity, :entity_id, :title
  self.primary_keys = :entity, :entity_id, :lang_id
end

schema.rb

  create_table "titles", :id => false, :force => true do |t|
    t.string   "entity"
    t.integer  "entity_id"
    t.integer  "lang_id"
    t.text     "title"
    t.datetime "created_at",                :null => false
    t.datetime "updated_at",                :null => false
  end

Execute:

Title.where( entity: 'agelimits' ) find_each { |i| p i }

And get message:

PG::Error: Error:  operator is not exist: character varying >= integer

And get query:

    SELECT  "titles".* FROM "titles"  WHERE "titles"."entity" = 'agelimits' AND ("titles"."entity" >= 0) AND ("titles"."entity_id" >= 0) AND ("titles"."lang_id" >= 0) ORDER BY "titles".entity ASC,"titles".entity_id ASC,"titles".lang_id ASC LIMIT 1000

What expected:

    SELECT  "titles".* FROM "titles"  WHERE "titles"."entity" = 'agelimits' AND ("titles"."entity" != '') AND ("titles"."entity_id" >= 0) AND ("titles"."lang_id" >= 0) ORDER BY "titles".entity ASC,"titles".entity_id ASC,"titles".lang_id ASC LIMIT 1000

Solution

See lib/composite_primary_keys/relation/batches.rb:28:in `find_in_batches'
Change this block to:

        self.primary_key.each do |key|
          condition = case relation.columns_hash[key.to_s].type
            when :string
              table[key].not_eq ''
            when :integer
              table[key].gteq start
          end
          relation = relation.where(condition)
        end
@cfis

Thanks for the report. Not sure I follow it though. What is the intent of:

when :string
              table[key].not_eq ''

Do yo have time to submit a patch with a test case?

@crosspath
table[key] == "entity"

So table[key].not_eq '' == "entity" != ''
Patch is here: http://pastebin.com/upxL1VAR

@crosspath

Similar problem. If PK has a column of type string, the query for the next batch was not correct.
This code generate query with conditions "greater than":

if primary_key_offset
  self.primary_key.each do |key|
    relation = relation.where(table[key].gt(primary_key_offset))
  end
  records = relation.to_a
else
  raise "Primary key not included in the custom select clause"
end

For example, CPK consists of two columns [string Text, integer Id]. Query looks like:

SELECT "table".* FROM "table" WHERE ("table".Text != '') AND ("table".Id > 0) -- it's correct, see first message
AND ("table".Text > 'some value') AND ("table".Id > 123) -- it's not correct
ORDER BY "table".Text ASC, "table".Id ASC -- order is important

Table rows:
| Id | Text |
| ... | ... |
| 122 | some value |
| 123 | some value |
| 124 | some value |
| ... | ... |

So this query will skip all rows with Text == 'some value', but it should not do this.

Patch: https://gist.github.com/crosspath/5150632
And previous patch: https://gist.github.com/crosspath/5150625

@cfis

Sorry for getting sidetracked on this, and thanks for the patches. Will try to review this weekend and get posted up.

@cfis

See my comment here:

#150 (comment)

@cfis

Closing since code was merged.

@cfis cfis closed this Aug 5, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment