Your SQL Condition is wrong — it will short-circuit… No, it won’t!

Ever received a code-review from a fellow programmer that made you doubt your own code? I did, but I had no problems pushing back ;)

DALL-E, instructed and carefully guided by Geek on Software and Money (fun fact — there are three legs for one body somewhere there, did you spot them?)

I was enhancing recently an SQL query in one of our apps, where the SELECT statement needed an additional conditions, as requested by the end-user.

To give you a context, imagine a table of Countries from Americas only (North and South). It’s a very simple one, defined by a country_name , population_count , and year which that population count corresponds to.

| country_name   | year | population_count |
|----------------|------|------------------|
| Canada         | 2020 | 38 million       |
| Canada         | 2019 | 37 million       |
| Canada         | 2018 | 36 million       |
| Canada         | 2017 | 35 million       |
| Canada         | 2016 | 34 million       |
| United States  | 2017 | 325 million      |
| United States  | 2016 | 323 million      |
| Mexico         | 2020 | 128 million      |
| Mexico         | 2019 | 127 million      |
| Mexico         | 2016 | 124 million      |
| Brazil         | 2020 | 212 million      |
| Brazil         | 2016 | 204 million      |
| Argentina      | 2020 | 45 million       |
| Argentina      | 2019 | 44 million       |

Now imagine my end-user requested that for a certain part of their view in the application, they do not want to see any entries if they come from Canada, US or Mexico (I mean, Canada and US ok, but why don’t you want to see Mexico, man? Common!, but we don’t judge our end-users, we execute! And execute it was that statement for years, something like this:

SELECT country_name, year
FROM americas
WHERE country_name NOT IN ('Canada', 'United States', 'Mexico');

Now, my enhancement consisting in adding Canada back to the list (for whatever reason) but only when the population count is bigger than 35 million.

Fair enough, here is the enhancement:

SELECT country_name, year
FROM americas
WHERE country_name NOT IN ('Canada', 'United States', 'Mexico')
   OR (country_name IN ('Canada') AND population_count > 35)
ORDER BY population_count;

Was it simple enough? Yeah, it was, but usually we spend more time in defending our code than in writing it.

Reviewer: “Hey buddy, your condition will not work, you know?”

Me: “Which one, this? country_name NOT IN (‘Canada’………) OR (………IN (‘Canada’ AND ………) Why?”

Reviewer: “You know, the first condition being true, the second part will be skept due to that thing, shortcut or something, how do we call it?”

Me: “Oooh, you mean short-circuit? Right, you know, I’ll show you something.”

So, who do you think is right, me or the reviewer? Remember, I have tested my code pretty well before asking for code-review, you know!

What a short-circuit is and how it works

DALL-E, instructed and carefully guided by Geek on Software and Money

It turns out that my reviewer was absolutely right conceptually. The short-circuit evaluation is a valid technique used in progamming languages to speed up an evaluation of a boolean expression. This optimization allows the evaluation to stop as soon as the outcome is determined, without needing to evaluate all parts of the expression. This concept applies in various programming languages, including Python, Java, and SQL databases like PostgreSQL, each with its nuances.

Python

In Python, boolean operators and and or perform short-circuit evaluation:

• and operator: If the first operand is false, Python does not evaluate the second operand because the overall expression cannot be true.
• or operator: If the first operand is true, Python does not evaluate the second operand because the overall expression is already true.

This behavior is useful for writing more efficient code and for conditionally evaluating expressions, such as checking if an object is not None before accessing its attributes.

a = 0
b = 10
result = a and (b / a) 
# This will not raise a ZeroDivisionError 
# because (b / a) is not evaluated.

Java

Java also uses short-circuit evaluation with its boolean operators && (logical AND) and || (logical OR):

• && operator: If the expression on the left side is false, the right side is not evaluated because the result cannot be true.
• || operator: If the expression on the left side is true, the right side is not evaluated because the result is already true.

This is particularly useful for preventing null pointer exceptions or for optimizing performance by avoiding unnecessary computations.

int a = 0;
boolean result = (a != 0) && (10 / a > 1);  
// The second part is not evaluated, preventing an ArithmeticException.

PostgreSQL

In PostgreSQL, and most SQL databases, short-circuit evaluation is applied in the evaluation of AND and OR conditions within WHERE clauses:

• AND condition: PostgreSQL stops evaluating the rest of the conditions if it encounters a false condition, as the entire expression cannot be true.
• OR condition: PostgreSQL stops evaluating the rest of the conditions if it encounters a true condition, as the entire expression is determined to be true.

However, it’s important to note that the SQL standard does not strictly guarantee the order of evaluation in these expressions, so relying on side effects of short-circuit evaluation in SQL queries can lead to unpredictable results (this specific example here is pretty dumb, but will do for the sake of example).

SELECT * 
FROM tricky_table 
WHERE false AND (SELECT 1/0) = 1;  
-- The SELECT 1/0 is not evaluated.

Real Life Language

Don’t you think that this smart short-cut evaluation happens only in computers, through some very smart circuits in the CPU. In fact the biological neuronal circuits in your brain do pretty much the same thing. Here is an example:

You: “Hey buddy, this Sunday are you willing to come to my place OR I’ll come see you?”

Your Buddy: “I’ll come”

Your neurons: “I see, discard everything after the OR condition”

How the short-circuit does NOT work!

Now than, we are very smart by now and know pretty well what a short-circuit is. We even know how it should most likely work in a few programming languages, but! The interesting question always is “How does this <fill-in-your-preferred-feature> not work?” or even better one “How ca we break it?”

You should always ask these questions… That’s how an experienced programmer keeps avoiding or introducing new and creative bugs in their systems, for the fun of their future selves ;)

Preventing Short-Circuit Evaluation

To prevent short-circuit evaluation in Python, you can use the bitwise operators & and | for boolean contexts, ensuring both sides of the expression are evaluated.

# Preventing short-circuit evaluation
x = 0

# This will raise ZeroDivisionError because it evaluates both sides.
print(x == 0 | 1/x)  

Preventing Short-Circuit Evaluation

In Java, you can prevent short-circuit evaluation by using the bitwise operators & and | instead of logical operators for boolean expressions. This forces both sides of the expression to be evaluated.

// Preventing short-circuit evaluation
int x = 0;
if (x == 0 | 1/x > 1) {  
    // This will throw ArithmeticException because both sides are evaluated.
    System.out.println("Not short-circuited");
}

Preventing Short-Circuit Evaluation

To ensure all expressions are evaluated, you might need to structure your query differently, possibly using functions or case statements to explicitly control the evaluation.

-- Using a CASE statement to ensure evaluation
SELECT CASE WHEN 1=0 THEN (SELECT 1/0) ELSE 'Not Evaluated' END;

Ok, so we kind of grasped the concept of the short-circuit now, and not only in SQL-based languages but in Java and Python programming languages, as well. But I haven’t answered my initial question on who was right, me or my reviewer?

DALL-E, instructed and carefully guided by Geek on Software and Money

Here is the truth. My reviewer was absolutely right when they raised the question on whether my SQL query will work as expected or not, due to the concept of short-circuit in logical satements evaluations.

I was right when answering that this is not something to worry about, since in my query case the short-circuit evaluation will not apply. But why will it not?

-- Reminder - the query that we discuss
SELECT country_name, year
FROM americas
WHERE country_name NOT IN ('Canada', 'United States', 'Mexico')
   OR (country_name IN ('Canada') AND population_count > 35)
ORDER BY population_count;

Very simple! It’s because the OR condition that I have defined is not a simple boolean expression that evaluates to TRUE or FALSE.

When SQL processes an IN or NOT IN list, it is not evaluating the list in a short-circuit manner like it would with AND or OR logical operators, where the evaluation can stop as soon as the result is determined. Instead, IN and NOT IN are set-based operations. The SQL engine treats these operations differently, often evaluating the entire set to determine if the left-hand side value exists (or does not exist) within the right-hand side set.

Set-Based Evaluation: IN and NOT IN are inherently set-based, meaning they evaluate the presence or absence of a value within a set. This requires considering all elements of the set to accurately return a result.

Postgres and Beyond

In the future posts of our journey, we will delve into some other interesting intricacies of the SQL concepts and more specifically I’ll try to show you how you may loose money only because you didn’t use the right data type for your money columns, in PostregSQL, as well as how to solve/prevent this from happening. Stay tuned by following and/or subscribing.

Call for Action

Don’t be a scrooge! Give a round of applause (aka Clapping) by hitting that follow button, and share your thoughts in the comments (if you got some)! Your engagement adds sparkle to this digital rendez-vous. Let’s keep the conversation rolling and the good vibes flowing! 👏🌟💬