Incorrect Result When Doing String Concatenation With ORDER BY

I'm try to understand why we are getting an incorrect result when doing string concatenation with order by. There are a series of combination that works and gives us a correct data. One weird thing is that when a unique nonclustered index is added to the table, it produces an incorrect result.

Below script will reproduce the incorrect result. I also added a 5 test cases which I think is very weird.

CREATE DATABASE [Stringer];
DROP TABLE IF EXISTS dbo.Options
CREATE TABLE dbo.Options (OptionId int NOT NULL,Keyword nvarchar(8) NOT NULL, OptionPartOrder int NULL, OptionRank int NOT NULL, OptionCategory nvarchar(50) NOT NULL);
INSERT INTO dbo.Options VALUES (1000000,N'Socks' , NULL, 1, N'Size'), (5000000, N'Socks', NULL, 2, N'Colour');
ALTER TABLE [dbo].[Options] DROP CONSTRAINT [uq_OptionId];
ALTER TABLE [dbo].[Options] ADD CONSTRAINT [uq_OptionId] PRIMARY KEY NONCLUSTERED ([OptionId] ASC);
CREATE CLUSTERED INDEX [cx_keyword] ON [dbo].[Options]([keyword] ASC);
DROP INDEX [cx_keyword] ON [dbo].[Options];
CREATE OR ALTER FUNCTION [dbo].[Split_dan] (@list nvarchar(MAX), @spliton nvarchar(5)) RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN (   SELECT   ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS Id
                      , CA.v AS [value]
               FROM STRING_SPLIT(@list, @spliton) s
               CROSS APPLY( VALUES(RTRIM(LTRIM(s.value))) ) AS CA(v)
               );

the query:

USE [Stringer];
GO
DECLARE
    @keyword NVARCHAR(8)= N'Socks',
    @optionids NVARCHAR(250)= N'1000000,5000000',
    @delimit_in NVARCHAR(5)=',',
    @delimit_out NVARCHAR(5)=',',
    @optionidout NVARCHAR(max);
SELECT @optionidout = ISNULL(@optionidout, '') + s.[value] + @delimit_out --, OptionId, OptionPartOrder, OptionRank, OptionCategory
FROM dbo.Options o
INNER JOIN dbo.Split_dan(@optionids, @delimit_in) s ON o.[OptionId] = CAST(s.[value] AS int) AND ISNUMERIC(s.[value]) = 1
WHERE o.[Keyword] = @keyword
ORDER BY ISNULL(o.OptionPartOrder,0), ISNULL(o.OptionRank, -1) , o.OptionCategory;
IF CHARINDEX(@delimit_out, @optionidout) > 0
        SET @optionidout = LEFT(@optionidout, LEN(@optionidout) - LEN(@delimit_out))
SELECT @optionidout AS 'options';
/* -- TEST CASE --
 - heap table or clustered (without unique nonclustered) produces correct result.
 - adding unique nonclustered index (with/without clustering key) produces incorrect result
 - with unique nonclustered index, removing ISNULL function on o.OptionPartOrder in ORDER BY produces correct result.
 - with unique nonclustered index, changing the s.value on the SELECT list to CAST(o.[OptionId] AS nvarchar) produces correct result.
 - with unique nonclustered index, removing the ISNULL(o.OptionPartOrder,0) expression in ORDER BY clause produces correct result
*/

• test case 1 result: 1000000,5000000
• test case 2 result: 5000000
• test case 3 result: 1000000,5000000
• test case 4 result: 1000000,5000000
• test case 5 result: 1000000,5000000

I'm curious on "why" this is happening? Could this be a limitation design or bug by design in SQL Server?

I've run the script on SQL Server 2016 Standard Edition and SQL Server 2019 Developer Edition and both produce the same result.

Asked by user214884 (170 )
Jul 28, 2022 at 03:08