DEV Community

Dmitry Daw
Dmitry Daw

Posted on

Is ActiveRecord right in omitting parentheses in queries? (and how ChatGPT lies again)

I need to get a selection of users with an SQL query like this:



sql =<<~SQL
    token IS NULL OR
    (
      token = 'some_token'
      AND (
        state = 'cancelled' AND created_at > ?
        OR state = 'submitted'
      )
    )
  SQL
User.where(sql, Time.now)


Enter fullscreen mode Exit fullscreen mode

I don't like to have SQL in my queries — all hidden code in scopes is now pops up, and it is hard to compose.

But could I rewrite this query with ActiveRecord? Let's try:



User
  .where(token: nil)
  .or(
    User.where(token: 'some_token').and(
      User.where(state: 'cancelled')
        .or(User.where('created_at > ?', Time.now))
    )
  )


Enter fullscreen mode Exit fullscreen mode

Looks better, but does it generate the same SQL?



SELECT "users".*
FROM "users"
WHERE ("users"."token" IS NULL
       OR "users"."token" = 'some_token'
       AND ("users"."state" = 'cancelled'
            OR created_at > '2024-04-16 10:46:43.129109'))


Enter fullscreen mode Exit fullscreen mode

Hm, where do the parentheses after IS NULL OR go? Doesn't it look like a different condition?

Tests are passing, but if ActiveRecord omits parentheses - wouldn't it be problematic in some cases?

For example, if we have an expression like this a || (b && (c || d)) - would it be the same if there were no parentheses, like a || b && c || d?

We are programmers in the modern era, so let's ask ChatGPT.

question
ChatGPT answer:
answer

So it seems all is okay. But let's (just for the sake of the experiment) check manually too:



booleans = [true, false]
combinations = booleans.product(booleans, booleans, booleans)
combinations.each do |a, b, c, d|
  expression1 = a && (b || (c && d))
  expression2 = a && b || c && d
  if expression1 != expression2
    puts "a: #{a}, b: #{b}, c: #{c}, Expression1: #{expression1}, Expression2: #{expression2}"
  end
end

=> a: false, b: true, c: true, Expression1: false, Expression2: true
=> a: false, b: false, c: true, Expression1: false, Expression2: true


Enter fullscreen mode Exit fullscreen mode

So, it's actually not. This robotic liar!

Okay, a && (b || (c && d)) and a && b || c && d are not equivalent.
Let's check another one:



combinations = booleans.product(booleans, booleans, booleans, booleans,
                                booleans, booleans, booleans, booleans, booleans)
combinations.each do |a, b, c, d, e, f, g, i, k|
  expression1 = a && b || (c && d || (e && f || (g && i || k)))
  expression2 = a && b || c && d || e && f || g && i || k
  if expression1 != expression2
    puts "a: #{a}, b: #{b}, c: #{c}, Expression1: #{expression1}, Expression2: #{expression2}"
  end
end


Enter fullscreen mode Exit fullscreen mode

These are the same! But how? So many groups we have!

How's that? It's because of logical operators' precedence: logical AND evaluates before logical OR.

So in a || b && c, it will first evaluate b && c, and then ||. We could see it like this: a || (b && c).

And it's the same in most languages - in Ruby and PostgreSQL for sure.

To make it easier to understand, we could rewrite expressions as multiplication and addition: because logical AND with binary values works exactly like normal arithmetic multiplication, and logical OR works in many senses as arithmetic addition.

E.g. a + (b * c) is the same as a + b * c.

And in our expressions:



  # these are not the same
  expression1 = a * (b + (c * d))
  expression2 = a * b + c * d

  # these are the same
  expression3 = a * b + (c * d + (e * f + (g * i + k)))
  expression4 = a * b + c * d + e * f + g * i + k


Enter fullscreen mode Exit fullscreen mode

Now it's starting to make sense - ActiveRecord is in its right to omit parentheses in our SQL example, as it is similar to expression3. And it's actually an optimization to send less data over the network.

But let's check, does it work correctly with expressions like a && (b || (c && d))?



User
  .where(token: nil)
  .and(
    User.where(token: 'some_token')
      .or(
        User.where(state: 'cancelled')
            .where('created_at > ?', Time.now)
      )
    )


Enter fullscreen mode Exit fullscreen mode


SELECT "users".*
FROM "users"
WHERE "users"."token" IS NULL
  AND ("users"."token" = 'some_token'
       OR "users"."state" = 'cancelled'
       AND (created_at > '2024-04-16 11:15:36.584382'))


Enter fullscreen mode Exit fullscreen mode

Good - parentheses after AND are in their place!

And if we rewrite OR to AND?



User
  .where(token: nil)
  .and(
    User.where(token: 'some_token')
      .and(
        User.where(state: 'cancelled')
            .where('created_at > ?', Time.now)
      )
    )


Enter fullscreen mode Exit fullscreen mode


SELECT "users".*
FROM "users"
WHERE "users"."token" IS NULL
  AND "users"."token" = 'some_token'
  AND "users"."state" = 'cancelled'
  AND (created_at > '2024-04-16 11:20:36.296399')


Enter fullscreen mode Exit fullscreen mode

It omits all parentheses—and rightfully so.

Okay, but we could use not only the .or method but also OR directly, like .where("... OR ..."). Does ActiveRecord handle this?
This is easy — .where(sql) always wraps the expression inside it in parentheses:



User.where(token: 'some_token')
    .where("state = 'cancelled' OR created_at > ?", Time.now)


Enter fullscreen mode Exit fullscreen mode


SELECT "users".*
FROM "users"
WHERE "users"."token" = 'some_token'
  AND (state = 'cancelled'
       OR created_at > '2024-04-16 11:23:00.877269')


Enter fullscreen mode Exit fullscreen mode

Even if it's only one condition



User.where(token: 'some_token')
    .where("state = 'cancelled'").to_sql


Enter fullscreen mode Exit fullscreen mode


SELECT "users".*
FROM "users"
WHERE "users"."token" = 'some_token'
  AND (state = 'cancelled')


Enter fullscreen mode Exit fullscreen mode

So, it seems all is good, and we could use our nice and clean ActiveRecord! Nice!

References:

Billboard image

Imagine monitoring that's actually built for developers

Join Vercel, CrowdStrike, and thousands of other teams that trust Checkly to streamline monitor creation and configuration with Monitoring as Code.

Start Monitoring

Top comments (0)

Billboard image

The Next Generation Developer Platform

Coherence is the first Platform-as-a-Service you can control. Unlike "black-box" platforms that are opinionated about the infra you can deploy, Coherence is powered by CNC, the open-source IaC framework, which offers limitless customization.

Learn more

👋 Kindness is contagious

Explore a sea of insights with this enlightening post, highly esteemed within the nurturing DEV Community. Coders of all stripes are invited to participate and contribute to our shared knowledge.

Expressing gratitude with a simple "thank you" can make a big impact. Leave your thanks in the comments!

On DEV, exchanging ideas smooths our way and strengthens our community bonds. Found this useful? A quick note of thanks to the author can mean a lot.

Okay