Open
Description
SCRUB ... WITH OPTIONS INDEX ... uses a FULL OUTER JOIN query to check that a secondary index matches the primary index. Because it uses FULL OUTER JOIN, the query requires spilling a hash table of the full dataset to disk, which won't work for a large table. This query should be rewritten to use streaming joins instead of hash join.
Here's a repro using cockroach demo on v24.2.1:
CREATE TABLE ab (a INT PRIMARY KEY, b STRING, INDEX (b));
INSERT INTO ab SELECT i, repeat('b', 10000 + i) FROM generate_series(0, 9999) AS s(i);
EXPERIMENTAL SCRUB TABLE ab AS OF SYSTEM TIME '-1ms' WITH OPTIONS INDEX ALL;Because cockroach demo only has 100 MiB of disk-temp-storage by default, the scrub fails with the following error message:
demo@127.0.0.1:26257/defaultdb> EXPERIMENTAL SCRUB TABLE ab AS OF SYSTEM TIME '-1ms' WITH OPTIONS INDEX ALL;
ERROR: scrub-index: this query requires additional disk space: hashjoiner-disk: disk budget exceeded: 1048576 bytes requested, 104857600 currently allocated, 0 bytes in budget
SQLSTATE: 53100
The query that fails is:
SELECT pri.a, pri.b, sec.a, sec.b
FROM (SELECT a, b FROM [104 AS table_pri]@[1]) AS pri
FULL JOIN (SELECT a, b FROM [104 AS table_sec]@[2]) AS sec
ON (pri.a = sec.a) AND (pri.b IS NOT DISTINCT FROM sec.b)
AS OF SYSTEM TIME '-1ms'
WHERE (pri.a IS NULL) OR (sec.a IS NULL);If instead this query were rewritten as four streaming joins unioned together, it would finish:
-- we need CTEs to prevent the optimizer from optimizing these joins to norows
WITH pri1 AS (
SELECT a, b FROM [104 AS table_pri]@[1]
),
pri2 AS (
SELECT a, b FROM [104 AS table_pri]@[1] WHERE b IS NULL ORDER BY a
),
sec3 AS (
SELECT a, b FROM [104 AS table_sec]@[2]
),
sec4 AS (
SELECT a, b FROM [104 AS table_sec]@[2] WHERE b IS NULL ORDER BY a
)
SELECT * FROM (
-- 1. left join from pri to sec, b is not null
SELECT pri1.a, pri1.b, sec1.a, sec1.b
FROM pri1
LEFT LOOKUP JOIN [104 AS table_sec]@[2] AS sec1
ON (pri1.a = sec1.a) AND (pri1.b = sec1.b)
WHERE (sec1.a IS NULL)
UNION ALL
-- 2. left join from pri to sec, b is null
SELECT pri2.a, pri2.b, sec2.a, sec2.b
FROM pri2
LEFT MERGE JOIN
(SELECT a, b FROM [104 AS table_sec]@[2] WHERE b IS NULL ORDER BY a) AS sec2
ON (pri2.a = sec2.a)
WHERE (sec2.a IS NULL)
UNION ALL
-- 3. left join from sec to pri, b is not null
SELECT pri3.a, pri3.b, sec3.a, sec3.b
FROM sec3
LEFT LOOKUP JOIN [104 AS table_pri]@[1] AS pri3
ON (pri3.a = sec3.a) AND (pri3.b = sec3.b)
WHERE (pri3.a IS NULL)
UNION ALL
-- 4. left join from sec to pri, b is null
SELECT pri4.a, pri4.b, sec4.a, sec4.b
FROM sec4
LEFT MERGE JOIN
(SELECT a, b FROM [104 AS table_pri]@[1] WHERE b IS NULL ORDER BY a) AS pri4
ON (pri4.a = sec4.a)
WHERE (pri4.a IS NULL)
) AS OF SYSTEM TIME '-1ms';Jira issue: CRDB-42670
Epic CRDB-30356
Metadata
Metadata
Assignees
Labels
Code not up to spec/doc, specs & docs deemed correct. Solution expected to change code/behavior.Would prevent or help troubleshoot a customer escalation - bugs, missing observability/tooling, docsIssues/test failures with no fix SLASQL Foundations Team (formerly SQL Schema + SQL Sessions)Failures and bugs on the master branch.