This project is archived and is in readonly mode.

#910 ✓duplicate
Eric

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

Reported by Eric | August 27th, 2008 @ 12:43 AM | in 2.x

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.

Comments and changes to this ticket

Create your profile

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

<h2 style="font-size: 14px">Tickets have moved to Github</h2>

The new ticket tracker is available at <a href="https://github.com/rails/rails/issues">https://github.com/rails/rails/issues</a>

Pages