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
Nobody has claimed this yet.
Assessment
- Difficulty
- 4/5
- Estimated time
- 3-5 days
- Newbie friendliness
- 48/100
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
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.)
- CockroachDB: pkg/sql/sem/eval/expr.go#L132 —
evaluator.EvalCaseExpr - CockroachDB: pkg/sql/colexec/colbuilder/execplan.go#L2498 —
planProjectionOperators
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
- No Dockerfile or Docker Compose file
- No pull request template
- Read the contributing guide
First steps
- Read the whole issue, then the project's contributing guide.
- Comment on the issue to say you are picking it up — it saves two people doing the same work.
- Fork the repository and make your change on a branch.
- Open a pull request that references the issue number.
More from cockroachdb/cockroach
-
C-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
cockroachdb/cockroach#176245 · 1 comment ·
-
sql: pg_database.dattablespace is 0 but pg_tablespace.oid is 1663, so they no longer joinPossibly taken @1AbdullahButt claimed this 14 days ago. OpenC-bug O-community X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
cockroachdb/cockroach#176228 · 1 comment ·
-
PG compat: overflow in integer power calculation triggers incompatible SQLSTATEMay be free again @darshpatel1052 claimed this 18 days ago, and no pull request is open. OpenC-bug O-community X-blathers-untriaged
Difficulty 2/5 1-3 hours Newbie friendliness 62/100
cockroachdb/cockroach#176223 · 2 comments ·
-
util/log: bufferedSinkCloser.Close leaks a goroutine after timeoutPossibly taken @dajiaohuang claimed this 14 days ago. OpenC-bug O-community X-blathers-untriaged
Difficulty 1/5 Under an hour Newbie friendliness 84/100
cockroachdb/cockroach#176210 · 1 comment ·
-
C-bug O-community T-sql-queries X-blathers-triaged X-move-internal
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
cockroachdb/cockroach#176150 · 1 comment ·
All issues in cockroachdb/cockroach
Similar issues
-
☢️ Bug v3
Difficulty 2/5 1-3 hours Newbie friendliness 82/100
Maintainers usually reply within 1 day
-
area/import-export area/projects concern/ux
Difficulty 2/5 1-3 hours Newbie friendliness 68/100
go-vikunja/vikunja#4455 · 1 comment ·
Maintainers usually reply within 2 days
-
Dependabot PRs no longer merge automaticallyPossibly taken @dwisiswant0 claimed this today. Open
Difficulty 2/5 1-3 hours Newbie friendliness 78/100
projectdiscovery/urlfinder#160 ·
Maintainers usually reply within 1 day
-
database
Difficulty 2/5 1-3 hours Newbie friendliness 72/100
chnm/legal-modernism#450 ·
Maintainers usually reply within 1 day
-
tech-debt
Difficulty 2/5 1-3 hours Newbie friendliness 76/100
Maintainers usually reply within 1 day