Skip to content

Replace BatchCounter BETWEEN with >= AND <

What does this MR do?

In BatchCounter queries, replace BETWEEN with >= AND <. The queries are effectively the same, but this helps us support iterating over a timestamp field (!43772 (closed)). It is also recommended in https://wiki.postgresql.org/wiki/Don't_Do_This#Don.27t_use_BETWEEN_.28especially_with_timestamps.29.

Rails magic

Code change where the Rails magic happens:

[1] pry(main)> puts User.where(id: 1..3).to_sql
SELECT "users".* FROM "users" WHERE "users"."id" BETWEEN 1 AND 3
=> nil
[2] pry(main)> puts User.where(id: 1...4).to_sql
SELECT "users".* FROM "users" WHERE "users"."id" >= 1 AND "users"."id" < 4
=> nil
[3] pry(main)> puts User.where(User.arel_table['id'].between(1..3)).to_sql
SELECT "users".* FROM "users" WHERE "users"."id" BETWEEN 1 AND 3
=> nil
[4] pry(main)> puts User.where(User.arel_table['id'].between(1...4)).to_sql
SELECT "users".* FROM "users" WHERE "users"."id" >= 1 AND "users"."id" < 4
=> nil

Before

[92] pry(main)> Gitlab::UsageData.count(Issue, batch_size: 1_500)
   (0.4ms)  SELECT MIN("issues"."id") FROM "issues"
   (0.3ms)  SELECT MAX("issues"."id") FROM "issues"
   (0.7ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" BETWEEN $1 AND $2  [["id", 1], ["id", 1500]]
   (0.8ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" BETWEEN $1 AND $2  [["id", 1501], ["id", 3000]]
   (0.7ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" BETWEEN $1 AND $2  [["id", 3001], ["id", 4500]]
   (0.9ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" BETWEEN $1 AND $2  [["id", 4501], ["id", 6000]]
   (1.1ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" BETWEEN $1 AND $2  [["id", 6001], ["id", 7500]]
=> 7178

After

[90] pry(main)> Gitlab::UsageData.count(Issue, batch_size: 1_500)
   (0.6ms)  SELECT MIN("issues"."id") FROM "issues"
   (0.4ms)  SELECT MAX("issues"."id") FROM "issues"
   (0.6ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" >= $1 AND "issues"."id" < $2  [["id", 1], ["id", 1501]]
   (0.6ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" >= $1 AND "issues"."id" < $2  [["id", 1501], ["id", 3001]]
   (0.6ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" >= $1 AND "issues"."id" < $2  [["id", 3001], ["id", 4501]]
   (0.7ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" >= $1 AND "issues"."id" < $2  [["id", 4501], ["id", 6001]]
   (0.5ms)  SELECT COUNT("issues"."id") FROM "issues" WHERE "issues"."id" >= $1 AND "issues"."id" < $2  [["id", 6001], ["id", 7501]]
=> 7178

Does this MR meet the acceptance criteria?

Conformity

Availability and Testing

Security

If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:

  • [-] Label as security and @ mention @gitlab-com/gl-security/appsec
  • [-] The MR includes necessary changes to maintain consistency between UI, API, email, or other methods
  • [-] Security reports checked/validated by a reviewer from the AppSec team
Edited by Alishan Ladhani

Merge request reports