BUG in usp_AdaptiveIndexDefrag: When dbScope is set, a new scan is done every time.

Open Beginner friendly

#320 opened on May 17, 2026 0 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty: 2/5
Estimated time: 1-3 hours
Newbie friendliness: 74/100

Issue type: Bug
Clarity: Clearly specified
Activity status: Quiet
Tech stack: sql
Domain: databases

Research direction

Start by locating usp_AdaptiveIndexDefrag and the scope-check query referencing dbo.tbl_AdaptiveIndexDefrag_Working and dbo.tbl_AdaptiveIndexDefrag_Stats_Working. Compare the quoted @dbScope value with the database-name values used in the NOT IN subquery. Done means a scoped database does not trigger a rescan on every execution, while forced rescans still behave as intended.

Written by the indexing model from the issue text.

Description

In this piece of code, the query checks if the QUOTED dbScope is NOT in the list of UNQUOTED dbNames that are in the dbo.tbl_AdaptiveIndexDefrag_Working or dbo.tbl_AdaptiveIndexDefrag_Stats_Working tables, and of cause this will never be true. The effect is the
script does a rescan whenever you set the @dbScope parameter.

            /* Check if database scope has changed, if rescan is not being forced */
	IF @forceRescan = 0 AND @dbScope IS NOT NULL -- Specific scope was set
	BEGIN
		IF (SELECT COUNT(DISTINCT [dbID]) FROM dbo.tbl_AdaptiveIndexDefrag_Working) > 1
			OR **QUOTENAME(LOWER(@dbScope)) NOT IN (SELECT DISTINCT LOWER([dbName]) FROM dbo.tbl_AdaptiveIndexDefrag_Working UNION SELECT DISTINCT LOWER(dbName) FROM dbo.tbl_AdaptiveIndexDefrag_Stats_Working**)
		BEGIN
			SET @forceRescan = 1
			RAISERROR('Scope has changed. Forcing rescan of single database in scope...', 0, 42) WITH NOWAIT;
		END;
	END;

Suggested fix: add Quotename to the selects in the NOT IN () term:

QUOTENAME(LOWER(@dbScope)) NOT IN (SELECT DISTINCT QUOTENAME(LOWER([dbName])) FROM dbo.tbl_AdaptiveIndexDefrag_Working UNION SELECT DISTINCT QUOTENAME(LOWER(dbName)) FROM dbo.tbl_AdaptiveIndexDefrag_Stats_Working)
BEGIN

Dominant language: Jupyter Notebook
Stars: 1.6k
Forks: 749
PR merge metrics: No merged PRs in 30d

Contributor guide

No contributing guide indexed for this repository

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.

Similar issues

Batched `completeJobs`/`failJobs` are never retried: the retry loop in `batch()` exits after the first failure Open

Difficulty 2/5 1-3 hours Newbie friendliness 88/100

graphile/worker#636 · Opened Sep 21, 2026
Test Failure: com.ibm.ws.jdbc.fat.sqlserver.SQLServerTest J2CA0079E Open

Difficulty 2/5 1-3 hours Newbie friendliness 72/100

OpenLiberty/open-liberty#35839 · Opened Sep 21, 2026
hash join v2: 'unsupported column type for pre-alloc 225' when joining on a VECTOR column Open

affects-9.0 found-by-ai may-affects-25.10 may-affects-26.3 may-affects-26.9 may-affects-7.5 may-affects-8.1 may-affects-8.5 severity/major sig/execution type/bug

Difficulty 2/5 1-3 hours Newbie friendliness 85/100

pingcap/tidb#71473 · Opened Sep 21, 2026
JSON_VALUE fails to parse when a RETURNING clause is present but the optional ON EMPTY/ON ERROR clause is omitted Open

Difficulty 1/5 Under an hour Newbie friendliness 92/100

antlr/grammars-v4#5014 · 1 comment · Opened Sep 21, 2026
readBinary(std::vector) throws a bare Poco::Exception (POCO_EXCEPTION code 1000) for too-large sizes instead of TOO_LARGE_ARRAY_SIZE Open

comp-query-execution fuzz

Difficulty 2/5 1-3 hours Newbie friendliness 88/100

ClickHouse/ClickHouse#121303 · 3 comments · Opened Sep 21, 2026

More Databases issues