Is ActiveRecord right in omitting parentheses in queries? (and how ChatGPT lies again)
Tags: ruby, rails, postgres, chatgpt
Published: 2024-04-16 11:45:19

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

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

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:

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

Looks better, but does it generate the same SQL?
```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'))
```

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](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/n1qoxej4d6zabwqbzj7g.png)
ChatGPT answer:
![answer](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/9s3f6zlbm3nk0o8gtw6n.png)

So it seems all is okay. But let's (just for the sake of the experiment) check manually too:
```ruby
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
```

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:

```ruby
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
```
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:
```ruby
  # 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
```

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))`?

```ruby
User
  .where(token: nil)
  .and(
    User.where(token: 'some_token')
      .or(
        User.where(state: 'cancelled')
            .where('created_at > ?', Time.now)
      )
    )
```
```sql
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'))
```
Good - parentheses after `AND` are in their place! 

And if we rewrite `OR` to `AND`?

```ruby
User
  .where(token: nil)
  .and(
    User.where(token: 'some_token')
      .and(
        User.where(state: 'cancelled')
            .where('created_at > ?', Time.now)
      )
    )
```
```sql
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')
```
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:

```ruby
User.where(token: 'some_token')
    .where("state = 'cancelled' OR created_at > ?", Time.now)
```
```sql
SELECT "users".*
FROM "users"
WHERE "users"."token" = 'some_token'
  AND (state = 'cancelled'
       OR created_at > '2024-04-16 11:23:00.877269')
```

Even if it's only one condition
```ruby
User.where(token: 'some_token')
    .where("state = 'cancelled'").to_sql
```
```sql
SELECT "users".*
FROM "users"
WHERE "users"."token" = 'some_token'
  AND (state = 'cancelled')
```

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


References:
* [Operator precedence in PostgreSQL](https://www.postgresql.org/docs/7.2/sql-precedence.html)
* [Operator precedence in Ruby](https://ruby-doc.org/core-2.6.2/doc/syntax/precedence_rdoc.html)