has_one association with disable_joins: true is not applying limit 1 to the resultant queries

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

has_one association with disable_joins: true is not applying the clause limit 1 to the constructed queries.

This can load an unlimited number of IDs into memory, which are then sent in the following query. As a result there can be performance degradation in the Rails application and the database.

It is worth noting that the same association on using preload does not load unbounded records. (Examples covered at the last)

Example with disable_joins

The below association chain from models/environment.rb constructs query without the LIMIT clause.

  has_one :last_deployment, -> { success.distinct_on_environment }, class_name: 'Deployment', inverse_of: :environment
  has_one :last_deployable, through: :last_deployment, source: 'deployable', source_type: 'CommitStatus', disable_joins: true
  has_one :last_pipeline, through: :last_deployable, source: 'pipeline', disable_joins: true
> Environment.last.last_deployable
(0.8ms)  SELECT "deployments"."deployable_id" FROM "deployments" WHERE "deployments"."environment_id" = 35 AND "deployments"."status" = 2 AND "deployments"."deployable_type" = 'CommitStatus' ORDER BY environment_id, deployments.id DESC
CommitStatus Load (15.1ms)  SELECT "ci_builds".* FROM "ci_builds" WHERE "ci_builds"."id" IN (807, 804, 801)
> Environment.last.last_pipeline
(0.9ms)  SELECT "deployments"."deployable_id" FROM "deployments" WHERE "deployments"."environment_id" = 35 AND "deployments"."status" = 2 AND "deployments"."deployable_type" = 'CommitStatus' ORDER BY environment_id, deployments.id DESC
(0.5ms)  SELECT "ci_builds"."commit_id" FROM "ci_builds" WHERE "ci_builds"."id" IN (807, 804, 801)
Ci::Pipeline Load (12.2ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" WHERE "ci_pipelines"."id" IN (93, 94, 95) ORDER BY "ci_pipelines"."id" ASC LIMIT 1

In both the above cases the following query is not having the LIMIT 1 clause.

SELECT "deployments"."deployable_id"
FROM   "deployments"
WHERE  "deployments"."environment_id" = 35
       AND "deployments"."status" = 2
       AND "deployments"."deployable_type" = 'CommitStatus'
ORDER  BY environment_id,
          deployments.id DESC

Example without disable_joins 

  has_one :last_deployment, -> { success.distinct_on_environment }, class_name: 'Deployment', inverse_of: :environment
  has_one :last_deployable, through: :last_deployment, source: 'deployable', source_type: 'CommitStatus'
  has_one :last_pipeline, through: :last_deployable, source: 'pipeline'
> Environment.last.last_deployable
  CommitStatus Load (9.1ms)  SELECT "ci_builds".* FROM "ci_builds" INNER JOIN "deployments" ON "ci_builds"."id" = "deployments"."deployable_id" WHERE "deployments"."environment_id" = 35 AND "deployments"."status" = 2 AND "deployments"."deployable_type" = 'CommitStatus' ORDER BY environment_id, deployments.id DESC LIMIT 1
> Environment.last.last_pipeline
  Ci::Pipeline Load (5.8ms)  SELECT "ci_pipelines".* FROM "ci_pipelines" INNER JOIN "ci_builds" ON "ci_pipelines"."id" = "ci_builds"."commit_id" INNER JOIN "deployments" ON "ci_builds"."id" = "deployments"."deployable_id" WHERE "deployments"."environment_id" = 35 AND "deployments"."status" = 2 AND "deployments"."deployable_type" = 'CommitStatus' ORDER BY environment_id, deployments.id DESC LIMIT 1

For the above example we can observe cross join of deployments table with the ci_* tables with the clause LIMIT 1.

Association preload example for a relationship without disable_joins

Example is mentioned in !68870 (comment 669978744)

We do not observe costly queries or joins when we preload record(s)

Edited by 🤖 GitLab Bot 🤖