sql: simple CASE with a random() operand evaluates it once per WHEN branch with vectorize on, so rows can reach ELSE, which never happens with vectorize off

Open
#176,195 1 comment 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
48/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
go, sql
Domain
backend, databases

Research direction

Start by reading EvalCaseExpr in pkg/sql/sem/eval/expr.go and planProjectionOperators in pkg/sql/colexec/colbuilder/execplan.go, then inspect the related COALESCE issue #77799 for context. Compare how the row-by-row and vectorized paths evaluate a simple CASE base expression, and use the supplied query with vectorize off and on to check the behavior. Done means the vectorized query no longer returns -1 for this case.

Written by the indexing model from the issue text.

Description

C-bug O-community T-sql-queries X-blathers-triaged X-move-internal

Describe the problem

For CREATE TABLE t (x INT NOT NULL) filled with 32 rows of x = 0, the query SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 ... WHEN 10 THEN 10 ELSE -1 END FROM t behaves differently in the two engines. The row-by-row engine evaluates the operand (random() * 10)::INT + x once per row; its value is between 0 and 10, so it always matches one of the WHEN branches and no row is -1. The vectorized engine plans and evaluates the operand separately for every WHEN branch, so the same row is compared against a different random() value in each branch. A row can miss every branch and fall through to ELSE, which gives -1 values that the row-by-row engine can never produce. The specific random values differ between runs because of random(); the problem is that the vectorized engine can return the ELSE result at all. The operand of a simple CASE should be evaluated once.

To Reproduce

Deployment: a single-node CockroachDB cluster started with cockroach start-single-node --insecure (tested: v26.2.7, 1 node, default settings except sql.stats.automatic_collection.enabled = false). Run the script in an empty database. The script runs the query twice in one session: first with vectorize = off (row-by-row engine), then with vectorize = on (vectorized engine).

SET TIME ZONE 'UTC';
CREATE TABLE t (x INT NOT NULL);
INSERT INTO t SELECT 0 FROM generate_series(1,32);

SELECT setseed(0.5);
-- row-by-row engine
SET vectorize = off;
EXPLAIN (VERBOSE) SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
-- vectorized engine
SET vectorize = on;
EXPLAIN (VERBOSE) SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;

Expected behavior

No row is -1, so every row is one of the values 0 to 10, as in the row-by-row engine. The CockroachDB v26.2 documentation (Simple CASE expressions, scalar-expressions) says: "Evaluates <cond>, then picks the WHEN branch where <condval> is equal to <cond>, then evaluates and returns the corresponding THEN expression." So the operand is evaluated first, once, and then compared with the WHEN values. The function documentation for random() says: "Returns a random floating-point number between 0 (inclusive) and 1 (exclusive)." and lists it as volatile, so evaluating it again changes the result. PostgreSQL 18.6 also evaluates the operand once and returns no -1 for this query.

PostgreSQL 18.6 (Debian 18.6-1.pgdg12+2):

SELECT setseed(0.5);
 setseed 
---------
 
(1 row)

SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
 case 
------
   10
    8
    1
    2
    6
    9
    1
    2
    5
    8
    5
   10
    5
    5
    7
    4
    7
    6
    9
    9
    0
    4
    2
    9
    8
    1
    6
   10
    2
    6
    7
    5
(32 rows)

Additional data / screenshots

With vectorize = off all 32 rows are between 0 and 10 and none is -1. With vectorize = on, 11 of the 32 rows are -1 in the run below. The remaining random values differ between the two runs, which is expected.

SELECT setseed(0.5);
 setseed 
---------
 
(1 row)

-- row-by-row engine
EXPLAIN (VERBOSE) SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
                                                                                                          info                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 distribution: local
 vectorized: false
 
 • render
 │ columns: ("case")
 │ render case: CASE x + (random() * 10.0)::INT8 WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END
 │
 └── • scan
       columns: (x)
       estimated row count: 1,000 (missing stats)
       table: t@t_pkey
       spans: FULL SCAN
(12 rows)

SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
 case 
------
    7
    6
    9
    8
    9
    9
    1
    8
    2
   10
    6
    2
    7
    4
    6
    9
    6
    6
    2
    5
    5
    6
    0
    7
    2
   10
    5
    7
    0
    5
    5
    2
(32 rows)

-- vectorized engine
EXPLAIN (VERBOSE) SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
                                                                                                          info                                                                                                           
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 distribution: local
 vectorized: true
 
 • render
 │ columns: ("case")
 │ render case: CASE x + (random() * 10.0)::INT8 WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END
 │
 └── • scan
       columns: (x)
       estimated row count: 1,000 (missing stats)
       table: t@t_pkey
       spans: FULL SCAN
(12 rows)

SELECT CASE ((random() * 10)::INT + x) WHEN 0 THEN 0 WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 WHEN 5 THEN 5 WHEN 6 THEN 6 WHEN 7 THEN 7 WHEN 8 THEN 8 WHEN 9 THEN 9 WHEN 10 THEN 10 ELSE -1 END FROM t;
 case 
------
   -1
   -1
   -1
   -1
   10
   -1
    7
    4
    2
    8
    6
   -1
    6
    2
   -1
   -1
    1
    4
   -1
    1
    1
    9
    9
   -1
    2
    6
   -1
    0
    3
    7
    2
    7
(32 rows)

Environment:

  • CockroachDB version:
CockroachDB CCL v26.2.7-dev-8652ac22d9f90501e48ae4f24efd7faab33cf7c5 (x86_64-pc-linux-gnu, built 2026/10/23 21:35:41, go1.25.13)
  • Server OS: Linux 6.8.0-138-generic x86_64, AMD EPYC 7J13 64-Core Processor
  • Client app: psql

Additional context

In the row-by-row engine, EvalCaseExpr evaluates the base expression of CASE <val> once, with expr.Expr.(tree.TypedExpr).Eval(ctx, e), and then compares that single value with each WHEN value in a loop. In the vectorized engine, the *tree.CaseExpr branch of planProjectionOperators loops over the WHEN branches and, for each one, takes left := t.Expr.(tree.TypedExpr) and builds NewTypedComparisonExpr(EQ, left, right), which plans the same base expression tree again. The base expression is therefore planned as n separate operators: EXPLAIN shows a copy of the castFloatIntOp / defaultBuiltinFuncOperator chain inside every WHEN arm of caseOp. For a volatile base expression such as random() every copy produces its own value. A fix could plan the base expression once and reuse its result through a buffered column, or refuse to vectorize a simple CASE whose base expression contains a volatile function.

The related issue discusses the same kind of problem for COALESCE: the vectorized engine rewrites it into a CASE, so a volatile expression is evaluated twice (a comment shows SELECT COALESCE(nextval('s'),0) followed by currval giving 1 with vectorize = off and 2 with vectorize = on), and the comment thread agrees that the vectorized engine should match the row-by-row engine. It does not mention the simple CASE <expr> WHEN ... form where the base expression is evaluated once per WHEN branch, which is what this issue covers. (The NULLIF example in that issue is a rewrite that the optimizer applies for both engines, so it is not the same problem.)

Related: https://github.com/cockroachdb/cockroach/issues/77799

Found with AI assistance.

Jira issue: CRDB-70790

Dominant language
Go
Stars
32.6k
Forks
4.2k
PR merge metrics
No merged PRs in 30d

Getting set up

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from cockroachdb/cockroach

All issues in cockroachdb/cockroach

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.