Lighthouse has a new layout. Prefer the old one? Return to the old layout, and switch back any time from the link at the top of each page.

This project is archived and is in readonly mode.

find(:include) will look for rows with null primary keys

#910

I am doing a very complicated :include and noticed many queries similar to the following:

SELECT * FROM "assets" WHERE ("assets".id IN (NULL))

Models are as follows:

Stuff
-----
id
one_asset_id
two_asset_id

Asset
-----
id

Table data:

Stuff

id | one_asset_id | two_asset_id
---|------------|-----------
1    1              NULL

Asset
id
--
1

AR usage:

=> Stuff.find(1, :include => [:one_asset, :two_asset])
Stuff Load (0.000884)  SELECT * FROM "stuff" WHERE ("stuff"."id" = 1)
Asset Load (0.001164)  SELECT * FROM "assets" WHERE ("assets".id IN (NULL))
Asset Load (0.000421)  SELECT * FROM "assets" WHERE ("assets".id IN (E'1'))

Now in my choice of database (Postgres), NULL values are not indexed so the NULL id query results in a sequential table scan, very painful for a million row table if this is done many times.

I looked in the code and found this in association_preload.rb:

conditions = "#{table_name}.#{connection.quote_column_name(primary_key)} IN (?)"
...
klass.find(:all, :conditions => [conditions, ids],

When the array ids is empty, it results in that NULL query. If we skipped the find in the cases that we have no ids to query for, we can avoid these NULL queries completely.

See attached diff for my patch.

Reported by Eric · August 27th, 2008 @ 12:43 AM

State: duplicate
Milestone: 2.x
Assigned to: nobody
Importance: none

Activity

  1. Pratik
    Pratik
    • State changed from new to incomplete

    Missing tests.

    Thanks.

    August 30th, 2008 @ 05:07 PM

  2. Eric
  3. Tarmo Tänav
    Tarmo Tänav
    • State changed from incomplete to open
    • Tag changed from activerecord, patch to activerecord, patch

    September 18th, 2008 @ 04:31 AM

  4. Frederick Cheung