COALESCE correlated scalar subquery duplicates result rows

Open
#176,217 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
22/100
Issue type
Bug
Clarity
Clearly specified
Activity status
Active
Tech stack
sql
Domain
database

Research direction

Start by reproducing both queries with the schema and data in the issue, then compare EXPLAIN output for the control and the correlated-subquery version to find where the extra rows appear. The problem is in how the optimizer handles a correlated scalar subquery under GROUP BY, which needs real familiarity with the SQL planner. Done means both queries return blue and red once each, with a regression test covering this shape.

Written by the indexing model from the issue text.

Description

C-bug O-community X-blathers-triaged

Describe the problem

A correlated scalar subquery containing COALESCE duplicates rows when evaluated in a GROUP BY query.

The direct expression returns blue and red once each. Moving the same expression into a scalar subquery correlated by a primary key causes each value to be returned twice.

To Reproduce

The issue reproduces with the following sequence:

  1. Start a CockroachDB v26.3.0 cluster and connect with cockroach sql.
  2. Create the two tables and insert the rows shown in Additional data and screenshots.
  3. Run the control query and observe that it returns two rows.
  4. Run the correlated-subquery query and observe four rows: blue and red are each returned twice.

Expected behavior

Both queries should return blue and red once each.

The inner query is correlated using the primary-key column coalesce_data.id and therefore produces exactly one scalar value for every outer row. Moving the expression into the correlated scalar subquery should not change the result cardinality.

Additional data and screenshots

The complete schema, test data, control query, affected query, and observed output are included below. No screenshot is required because the behavior is visible in the cockroach sql output.

Schema and test data
DROP TABLE IF EXISTS coalesce_guard;
DROP TABLE IF EXISTS coalesce_data;

CREATE TABLE coalesce_guard (
    marker STRING
);

CREATE TABLE coalesce_data (
    id INT PRIMARY KEY,
    payload STRING
);

INSERT INTO coalesce_guard VALUES ('blocked');
INSERT INTO coalesce_data VALUES (1, 'red'), (2, 'blue');
Control query
SELECT NULLIF(
           COALESCE(d.payload, 'missing'),
           g.marker
       ) AS result
FROM coalesce_data AS d
CROSS JOIN coalesce_guard AS g
GROUP BY d.id, d.payload, g.marker
ORDER BY result;
Control output
result
------
blue
red
(2 rows)
Affected query
SELECT NULLIF(
           (
               SELECT COALESCE(x.payload, 'missing')
               FROM coalesce_data AS x
               WHERE x.id = d.id
           ),
           g.marker
       ) AS result
FROM coalesce_data AS d
CROSS JOIN coalesce_guard AS g
GROUP BY d.id, d.payload, g.marker
ORDER BY result;
Actual output
result
------
blue
blue
red
red
(4 rows)

Environment:

  • CockroachDB version: CockroachDB CCL v26.3.0, built 2026-07-28
  • Server OS: macOS on Apple Silicon
  • CockroachDB build target: aarch64-apple-darwin21.2
  • Client application: cockroach sql
  • Connection: local single-node test cluster at 127.0.0.1:26257

Additional context

This is a silent wrong-result issue. The additional rows change the query cardinality without producing an error, making the problem difficult for an application to detect.

Applications using the result for grouping, deduplication, counting, or downstream processing may silently process each value more than once.

Jira issue: CRDB-71124

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.