Início / user-214884

Dan Co's questions

Martin Hope
Dan Co
Asked: 2022-07-28 19:08:38 +0800 CST
  • 3

Estou tentando entender por que estamos obtendo um resultado incorreto ao fazer a concatenação de strings com order by. Há uma série de combinações que funcionam e nos dão um dado correto. Uma coisa estranha é que quando um índice não clusterizado exclusivo é adicionado à tabela, ele produz um resultado incorreto.

O script abaixo reproduzirá o resultado incorreto. Eu também adicionei 5 casos de teste que eu acho muito estranho.

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)
               );

A pergunta:

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
*/
  • resultado do caso de teste 1:1000000,5000000
  • resultado do caso de teste 2:5000000
  • resultado do caso de teste 3:1000000,5000000
  • resultado do caso de teste 4:1000000,5000000
  • resultado do caso de teste 5:1000000,5000000

Estou curioso sobre "por que" isso está acontecendo? Isso poderia ser um design de limitação ou um bug por design no SQL Server?

Executei o script no SQL Server 2016 Standard Edition e no SQL Server 2019 Developer Edition e ambos produzem o mesmo resultado.

sql-server sql-server-2016