Your Web News in One Place

An Interest In:

Web News this Week

Search Archive

Some of Our Sources

View All Sources

Help Webnuz

Referal links:

Sign up for GreenGeeks web hosting
April 16, 2024 11:45 am GMT

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'      )    )  SQLUser.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:

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?

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
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}"  endend=> 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:

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}"  endend

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

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

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

User.where(token: 'some_token')    .where("state = 'cancelled'").to_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:


Original Link: https://dev.to/haukot/is-activerecord-right-in-omitting-parentheses-in-queries-and-how-chatgpt-lies-again-2dao

Share this article:    Share on Facebook
View Full Article

Dev To

An online community for sharing and discovering great ideas, having debates, and making friends

More About this Source Visit Dev To