Bug on IndexOptimize when executing with @PartitionLevel = 'N'

[ze1441]

Description of the issue When executing the procedure IndexOptimize with the parameter @PartitionLevel = 'N', I'm getting the error "The multi-part identifier "partitions.partition_number" could not be bound."

SQL Server version and edition Microsoft SQL Server 2017 (RTM-CU27) (KB5006944) - 14.0.3421.10 (X64)

Version of the script Version: 2022-12-03 17:23:44

What command are you executing? EXECUTE dbo.IndexOptimize @Databases = 'databaseName', @Execute = 'N', @LogToTable = 'Y', @PartitionLevel = 'N', @Indexes = 'databaseName.dbo.TableName'

What output are you getting? Msg 4104, Level 16, State 1, Line 9 The multi-part identifier "partitions.partition_number" could not be bound.

Aditional notes I have found that the problem is on the line 1586: Changed:

+ ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND (index_resumable_operations.partition_number = partitions.partition_number OR index_resumable_operations.partition_number IS NULL)) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'

To:

+ ', ' + CASE WHEN @Version >= 14 THEN 'CASE WHEN EXISTS(SELECT * FROM sys.index_resumable_operations index_resumable_operations WHERE state_desc = ''PAUSED'' AND index_resumable_operations.object_id = indexes.object_id AND index_resumable_operations.index_id = indexes.index_id AND ('+ CASE WHEN @PartitionLevel = 'Y' THEN 'index_resumable_operations.partition_number = partitions.partition_number' ELSE 'index_resumable_operations.partition_number IS NULL' END + ')) THEN 1 ELSE 0 END' ELSE '0' END + ' AS ResumableIndexOperation'

And this solved the problem.