Duckdb only optimises `CASE WHEN` expressions when all branches contain the same expression; fails with NULL level

[RobinL]

Duckdb contains an optimisation (see here) that effectively rewrites:

CASE
    WHEN cosine_sim(l, r) > 0.9 THEN 1
    WHEN cosine_sim(l, r) > 0.8 THEN 2
    WHEN cosine_sim(l, r) > 0.7 THEN 3
    ...
END

to

WITH precomputed_cosine AS (
    SELECT cosine_sim(l, r) AS precompute_cosine_value, ...
)
SELECT
    CASE
        WHEN precompute_cosine_value > 0.9 THEN 1
        WHEN precompute_cosine_value > 0.8 THEN 2
        WHEN precompute_cosine_value > 0.7 THEN 3
        ...
    END

However, testing has show this optimisation only works when the case statement ONLY includes precompute_cosine_value.

As soon as the original statement is:

CASE
    WHEN l or r is null then 0
    WHEN cosine_sim(l, r) > 0.9 THEN 1
    WHEN cosine_sim(l, r) > 0.8 THEN 2
    WHEN cosine_sim(l, r) > 0.7 THEN 3
    ...
END

the optimisation fails

Ref:
#2738

[TODO: verify this by looking at the relevant duckdb code)

[RobinL]

Reprex

```python import time

import duckdb

num_rows = 10_000_000
con = duckdb.connect()

Start timing

start_time = time.time()

Create empty table

ids = con.execute(
f"create table ids as SELECT unnest(generate_series(1, {num_rows}) ) AS id"
)

query = """
CREATE TABLE df AS
SELECT
cast(random() as varchar)
.md5()
.substr(1, cast((3 + random() * 12) as int))
.translate('1234567890', 'abcdefghij') AS first_name_l,

cast(random() as varchar)
    .md5()
    .substr(1, cast((3 + random() * 12) as int))
    .translate('1234567890', 'abcdefghij') AS first_name_r,

FROM ids;
"""

con.execute(query)

con.table("df").show()

start_time = time.time()
query1 = """

SELECT
sum(CASE
WHEN first_name_l IS null OR first_name_r IS null THEN 0
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.95 THEN 5
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.9 THEN 4
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.8 THEN 3
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.7 THEN 2
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.6 THEN 1
ELSE 0
END) AS gamma_first_name
FROM df
"""
t = con.sql(query1)
t.show()
query1_time = time.time() - start_time
print(f"Query 1 execution time: {query1_time:.2f} seconds")

start_time = time.time()
query2 = """

SELECT
sum(CASE
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.95 THEN 5
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.9 THEN 4
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.8 THEN 3
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.7 THEN 2
WHEN jaro_winkler_similarity(first_name_l, first_name_r) >= 0.6 THEN 1
ELSE 0
END) AS gamma_first_name
FROM df
"""
t = con.sql(query2)
t.show()
query2_time = time.time() - start_time
print(f"Query 2 execution time: {query2_time:.2f} seconds")

start_time = time.time()
query3 = """
WITH reusable AS (
SELECT jaro_winkler_similarity(first_name_l, first_name_r) AS jws
FROM df
)
SELECT
sum(CASE
WHEN jws >= 0.95 THEN 5
WHEN jws >= 0.9 THEN 4
WHEN jws >= 0.8 THEN 3
WHEN jws >= 0.7 THEN 2
WHEN jws >= 0.6 THEN 1
ELSE 0
END) AS gamma_first_name
FROM reusable
"""
t = con.sql(query3)
t.show()
query3_time = time.time() - start_time

Print results

print(f"Query 2 execution time: {query3_time:.2f} seconds")

</p>
</details> 

┌──────────────────┐
│ gamma_first_name │
│ int128 │
├──────────────────┤
│ 2723042 │
└──────────────────┘

Query 1 execution time: 0.40 seconds
┌──────────────────┐
│ gamma_first_name │
│ int128 │
├──────────────────┤
│ 2723042 │
└──────────────────┘

Query 2 execution time: 0.11 seconds
┌──────────────────┐
│ gamma_first_name │
│ int128 │
├──────────────────┤
│ 2723042 │
└──────────────────┘

Query 2 execution time: 0.10 seconds

[RobinL]

The relevant DuckDB source code is here, and the behaviour observed is deliberate. Here's a chatgpt summary of why DuckDB has done it this way:

Details

Yes — that issue’s reading of cse_optimizer.cpp is basically right, with one nuance: it’s not “all branches must contain the expression”, it’s “the expression must be introduced on the leftmost (always-evaluated) side of a short-circuiting expression (CASE/AND/OR), otherwise it’s intentionally not eligible.”

What cse_optimizer does (succinctly)

• It runs on projections and aggregate/group-by operators.

• Pass 1 (CountExpressions): walks all expressions and counts repeated, non-trivial, non-volatile, non-aggregate expressions (so it can spot common subexpressions).

• Pass 2 (PerformCSEReplacement): for any expression with count > 1, it:

  • creates a new LogicalProjection below the operator,
  • computes that expression once in the new projection,
  • replaces each repeated occurrence with a BoundColumnRefExpression pointing at the projected result.

Why adding the NULL branch stops the rewrite

CountExpressions has special handling for short-circuiting expression classes:

case ExpressionClass::BOUND_CONJUNCTION:
case ExpressionClass::BOUND_CASE:
  // only extract from leftmost side

The mechanism is this line in the “first time we see an expression” path:

if (!state.short_circuited) {
    state.expression_count[expr] = CSENode();
}

And for CASE (and conjunctions) it sets state.short_circuited = true after visiting the first child, so only expressions first encountered in the first child get inserted into expression_count. Anything first seen later is ignored entirely (never counted, therefore never extracted).

• Without the NULL guard, the first WHEN condition contains cosine_sim(l,r) / jaro_winkler_similarity(...), so that expression is first encountered while short_circuited == false, gets recorded, later occurrences increment its count, and it’s extracted.
• With WHEN l IS NULL OR r IS NULL THEN 0 as the first branch, the expensive similarity expression is first encountered only in later WHENs — but by then short_circuited == true, so it never even enters expression_count, so it can’t reach count > 1, so no CSE projection is created.

This is deliberate: pulling an expression out of a CASE can force it to be evaluated even for rows where the CASE would have short-circuited before reaching it (classic example: CASE WHEN x=0 THEN 0 ELSE 1/x END — precomputing 1/x would introduce divide-by-zero errors).

What may or may not work as a workaround

Is there a workaround of the form: CASE WHEN l or r is null then 0 WHEN case when cosine_sim(l, r) > 0.9 THEN 1 WHEN cosine_sim(l, r) > 0.8 THEN 2 WHEN cosine_sim(l, r) > 0.7 THEN 3 end END ... END
Probably not (at least not for triggering DuckDB’s automatic CSE rewrite).

In the current cse_optimizer logic you pasted, once the outer CASE has visited its first child, it flips state.short_circuited = true for everything else inside that CASE. If you move the similarity thresholds into an inner CASE that sits in the ELSE/THEN of the outer CASE, the optimizer will still enter that inner CASE with short_circuited == true, so the cosine_sim(l,r) expression is never “first seen” in a non-short-circuited context, and therefore never becomes eligible for extraction.

So this:

CASE
  WHEN l IS NULL OR r IS NULL THEN 0
  ELSE CASE
    WHEN cosine_sim(l,r) > 0.9 THEN 1
    WHEN cosine_sim(l,r) > 0.8 THEN 2
    ...
  END
END

…won’t resurrect the automatic CSE optimisation.

Workarounds that do work

1) Manual precompute in a subquery/CTE (best: still avoids evaluating on NULL rows)

WITH base AS (
  SELECT
    *,
    CASE
      WHEN l IS NULL OR r IS NULL THEN NULL
      ELSE cosine_sim(l, r)
    END AS cs
  FROM t
)
SELECT
  CASE
    WHEN cs IS NULL THEN 0
    WHEN cs > 0.9 THEN 1
    WHEN cs > 0.8 THEN 2
    WHEN cs > 0.7 THEN 3
    ELSE 0
  END AS score
FROM base;

This guarantees at most one cosine_sim per row, and zero calls on rows that hit the NULL guard.

2) If cosine_sim is NULL-safe and you don’t care about computing it on NULL rows

You can eliminate the explicit NULL branch and fold it in:

CASE
  WHEN COALESCE(cosine_sim(l, r), 0) > 0.9 THEN 1
  WHEN COALESCE(cosine_sim(l, r), 0) > 0.8 THEN 2
  ...
  ELSE 0
END

This often allows CSE again (because the similarity expression appears in the first WHEN), but it will still evaluate cosine_sim(l,r) for NULL rows (it just becomes NULL then coalesces).

3) If cosine_sim might error and you want a safe “always evaluable” expression

Use try(...) (if available in your DuckDB version):

CASE
  WHEN COALESCE(try(cosine_sim(l, r)), 0) > 0.9 THEN 1
  ...
  ELSE 0
END

This can make it safe to put the expression “first” so CSE can extract it, but you pay whatever overhead try introduces.

If your goal is specifically “don’t compute similarity when NULL”, the CTE/subquery precompute (option 1) is the reliable one.

Examples that will and will not be optimised

> Can you give me a couple of examples of statements that will and will not be optimised? i\'m not sure i understood 'it’s “the expression must be introduced on the leftmost (always-evaluated) side of a short-circuiting expression (CASE/AND/OR), otherwise it’s intentionally not eligible.”', so a few examples would help me

Sure — here’s the key idea in plain English first, then concrete “will / won’t” examples.

What “introduced on the leftmost (always-evaluated) side” means

DuckDB’s CSE pass won’t even start counting a subexpression the first time it sees it if that first sighting is inside a short-circuitable region (CASE / AND / OR) that might not run.

So for an expression like f(l,r) to be extracted, it must appear at least once in the part of the expression tree that is guaranteed to be evaluated:

• A AND B: A is always evaluated; B might be skipped.
• A OR B: A is always evaluated; B might be skipped.
• CASE WHEN c1 THEN ... WHEN c2 THEN ... END: the first WHEN condition (c1) is always evaluated; later WHENs and THEN/ELSE parts might be skipped.

Once f(l,r) has been “introduced” in an always-evaluated spot, later occurrences (even in short-circuited parts) do get counted, and then CSE can kick in.

---

Examples that will be CSE-optimised (likely)

1) Your “pure thresholds” CASE (function appears in first WHEN)

SELECT
  CASE
    WHEN jaro_winkler_similarity(l, r) >= 0.95 THEN 5
    WHEN jaro_winkler_similarity(l, r) >= 0.90 THEN 4
    WHEN jaro_winkler_similarity(l, r) >= 0.80 THEN 3
    ELSE 0
  END AS score
FROM t;

Reason: jaro_winkler_similarity(l,r) is first seen in the first WHEN condition, which is always evaluated → it gets registered and then extracted.

2) AND where the repeated call appears in the left operand

SELECT
  (jaro_winkler_similarity(l, r) >= 0.7
   AND jaro_winkler_similarity(l, r) < 0.9) AS ok
FROM t;

Reason: left operand of AND is always evaluated, so the function is “introduced” there; the second occurrence increments the count → extractable.

---

Examples that will not be CSE-optimised

3) Your NULL-guard CASE (function first appears later)

SELECT
  CASE
    WHEN l IS NULL OR r IS NULL THEN 0
    WHEN jaro_winkler_similarity(l, r) >= 0.95 THEN 5
    WHEN jaro_winkler_similarity(l, r) >= 0.90 THEN 4
    ELSE 0
  END AS score
FROM t;

Reason: the function is first seen in the second WHEN (not always reached) → it never gets registered in expression_count → never extracted.

4) AND where the repeated call only appears in later operands

SELECT
  (l IS NOT NULL
   AND jaro_winkler_similarity(l, r) >= 0.7
   AND jaro_winkler_similarity(l, r) < 0.9) AS ok
FROM t;

Reason: first child of the conjunction is l IS NOT NULL. After that, DuckDB marks the rest as “short-circuited context”, so the function is first encountered there → not registered → no CSE.

5) CASE where the function is only in THEN/ELSE (not in first WHEN condition)

SELECT
  CASE
    WHEN flag THEN jaro_winkler_similarity(l, r)
    ELSE jaro_winkler_similarity(l, r)
  END AS v
FROM t;

Reason: the first always-evaluated bit is flag (first WHEN condition). The function first appears in THEN/ELSE (which are conditional) → not introduced → no CSE.

---

If you want to see whether it happened, EXPLAIN will usually show an extra PROJECTION below the operator when CSE extraction occurs. If you paste two EXPLAIN outputs (one “works”, one “fails”), I can point to the exact structural difference.