Странное поведение при SELECT в VARCHAR с завершающим пробелом SQL Server

Question

Я надеюсь, что это интересная головоломка для эксперта по SQL.

Когда я запускаю следующий запрос, я ожидаю, что он не вернет результатов.

-- Create a table variable Note: This same behaviour occurs in standard tables.

DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)

-- Add some test data Note: Without space, space prefix and space suffix

INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')

-- SELECT statement that is filtered by a value without a space and also a value with a space suffix

SELECT 
     t.Foo
     , t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar '
AND t.Foo = 'Bar'

Результаты возвращают одну строку:

[Foo]  [About]
Bar    Space Suffix

Проблема в том, что люди копируют и вставляют значения из электронных писем и т. Д. И каким-то образом попадают в таблицу. Я рассматриваю это как отдельную проблему, поскольку я LTRIM(RTRIM(Foo)) как триггер INSERT и UPDATE, но некоторые каким-то образом проходят через сеть.

Мне нужно больше узнать об этом поведении и о том, как его обойти.

Также стоит отметить, что LEN(Foo) тоже нечетное, а именно:

DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)
INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')

SELECT 
     t.Foo
     , LEN(Foo) [Length]
     , t.About
FROM @TestResults t

Дает следующие результаты:

[Foo]   [Length]  [About]
Bar     3         No spaces
Bar     3         Space Suffix
 Bar    4         Space prefix

Без каких-либо дополнительных размышлений, что мне нужно изменить в предложении WHERE, чтобы вернуть 0 результатов, как ожидалось?

Answer 1

Ответ состоит в том, чтобы добавить следующий пункт:

AND DATALENGTH(t.Foo) = DATALENGTH('Bar')

Выполняем следующий запрос...

DECLARE @Chars TABLE (CharNumber INT NOT NULL)

DECLARE @CharNumber INT = 0

WHILE(@CharNumber <= 255)
    BEGIN
        INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)

        SET @CharNumber = @CharNumber + 1

    END

SELECT 
    CharNumber
    , IIF('Test' = 'Test' + CHAR(CharNumber),1,0) ['Test' = 'Test' + CHAR(CharNumber)]
    , IIF('Test' LIKE 'Test' + CHAR(CharNumber),1,0) ['Test' LIKE 'Test' + CHAR(CharNumber)]
    , IIF(LEN('Test') = LEN('Test' + CHAR(CharNumber)),1,0) [LEN('Test') = LEN('Test' + CHAR(CharNumber))]
    , IIF(DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber)),1,0) [DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))]
FROM @Chars
WHERE ('Test' = 'Test' + CHAR(CharNumber))
OR ('Test' LIKE 'Test' + CHAR(CharNumber))
OR (LEN('Test') = LEN('Test' + CHAR(CharNumber)))
ORDER BY CharNumber

... дает следующие результаты...

CharNumber  'Test' = 'Test' + CHAR(CharNumber)  'Test' LIKE 'Test' + CHAR(CharNumber)   LEN('Test') = LEN('Test' + CHAR(CharNumber))    DATALENGTH('Test') = DATALENGTH('Test' + CHAR(CharNumber))
0           1                                   1                                       0                                               0
32          1                                   0                                       1                                               0
37          0                                   1                                       0                                               0

DATALENGTH можно использовать для проверки равенства двух VARCHAR, поэтому исходный запрос можно исправить следующим образом:

-- Create a table variable Note: This same behaviour occurs in standard tables.

DECLARE @TestResults TABLE (Id int IDENTITY(1,1) NOT NULL, Foo VARCHAR(100) NOT NULL, About VARCHAR(1000) NOT NULL)

-- Add some test data Note: Without space, space prefix and space suffix

INSERT INTO @TestResults(Foo, About) VALUES('Bar', 'No spaces')
INSERT INTO @TestResults(Foo, About) VALUES('Bar ', 'Space Suffix')
INSERT INTO @TestResults(Foo, About) VALUES(' Bar', 'Space prefix')

-- SELECT statement that is filtered by a value without a space and also a value with a space suffix

SELECT 
     t.Foo
     , t.About
FROM @TestResults t
WHERE t.Foo like 'Bar '
AND t.Foo like 'Bar'
AND t.Foo = 'Bar ' 
AND t.Foo = 'Bar' 
AND DATALENGTH(t.Foo) = DATALENGTH('Bar') -- Additional clause

Я также сделал функцию, которая будет использоваться вместо =

ALTER FUNCTION dbo.fVEQ( @VarCharA VARCHAR(MAX), @VarCharB VARCHAR(MAX) ) 
RETURNS BIT 
WITH SCHEMABINDING
AS
BEGIN
    -- Added by WonderWorker on 18th March 2020

    DECLARE @Result BIT = IIF(
        (@VarCharA = @VarCharB AND DATALENGTH(@VarCharA) = DATALENGTH(@VarCharB))

    , 1, 0)

    RETURN @Result

END

.. Вот тест для всех 256 символов, используемых в качестве завершающих символов, чтобы доказать, что это работает.

-- Test fVEQ with all 256 characters

DECLARE @Chars TABLE (CharNumber INT NOT NULL)

DECLARE @CharNumber INT = 0

WHILE(@CharNumber <= 255)
    BEGIN
        INSERT INTO @Chars(CharNumber) VALUES(@CharNumber)

        SET @CharNumber = @CharNumber + 1

    END

SELECT 
    CharNumber
    , dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) [fVEQ Trailing Char Test]
    , dbo.fVEQ('Bar','Bar') [fVEQ Same test]
    , dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') [fVEQ Leading Char Test]
FROM @Chars
WHERE (dbo.fVEQ('Bar','Bar' + CHAR(CharNumber)) = 1)
AND (dbo.fVEQ('Bar','Bar') = 0)
AND (dbo.fVEQ('Bar',CHAR(CharNumber) + 'Bar') = 1)

Answer 2

Причина, по которой завершающие пробелы не учитываются при сравнении строк, заключается в понятии строковых полей фиксированной длины, в которых любое содержимое короче фиксированной длины автоматически дополняется справа пробелами. Такие поля фиксированной длины не могут отличить значимые конечные пробелы от заполнения.

Объяснение того, почему строковые поля фиксированной длины вообще существуют, заключается в том, что они значительно повышают производительность во многих случаях, и когда SQL был разработан, он был обычным для символьных терминалов (которые обычно обрабатывали конечные пробелы, эквивалентные заполнению), отчеты печатались с моноширинным шрифтом. шрифты (в которых для заполнения и выравнивания использовались пробелы в конце), а также форматы хранения и обмена данными (в которых использовались поля фиксированной длины вместо обширных и дорогостоящих разделителей и сложной логики синтаксического анализа), чтобы все они были ориентированы на поля фиксированной длины, поэтому была тесная интеграция с этой концепцией на всех этапах обработки.

При сравнении двух полей фиксированной длины одинаковой фиксированной длины буквальное сравнение, конечно, возможно и даст правильные результаты.

Но при сравнении поля фиксированной длины заданной фиксированной длины с полем фиксированной длины другой фиксированной длины желаемое поведение никогда не состояло бы в том, чтобы включать конечные пробелы в сравнение, поскольку два таких поля никогда не могли бы совпадать буквально просто в силу их различной фиксированной длины. Более короткое поле может быть преобразовано и дополнено до длины более длинного (по крайней мере, концептуально, если не физически), но завершающий пробел все равно будет рассматриваться как дополнение, а не как значимое.

При сравнении поля фиксированной длины с полем переменной длины желаемое поведение также, вероятно, состоит в том, чтобы никогда не включать конечные пробелы в сравнение. Более сложные подходы, которые пытаются приписать смысл конечным пробелам в части сравнения переменной длины, будут получены только за счет более медленной логики сравнения, дополнительной концептуальной сложности и возможности ошибки.

С точки зрения того, почему сравнения переменной длины с переменной длиной игнорируют завершающие пробелы, поскольку здесь пробелы в принципе могут иметь смысл, объяснение, вероятно, заключается в сохранении согласованности в поведении сравнения, например, когда задействованы поля фиксированной длины, и избегание наиболее распространенных своего рода ошибка, поскольку конечные пробелы в базах данных гораздо чаще являются ложными, чем значимыми.

В настоящее время система баз данных, разработанная во всех отношениях с нуля, вероятно, отказалась бы от полей фиксированной длины и, вероятно, выполняла бы все сравнения буквально, предоставив разработчику возможность явно иметь дело с фиктивными конечными пробелами, но, по моему опыту, это привело бы к дополнительным усилиям по разработке и значительному снижению производительности. более частая ошибка, чем текущая схема SQL, где ошибки в логике программы, связанные с молчаливым игнорированием завершающих пробелов, обычно возникают только при разработке сложной логики измельчения строк для использования с ненормализованными данными (которые являются типом данных, которые SQL специально не оптимизирован для обработки).

Чтобы было ясно, это не недокументированная функция, а заметная функция, которая существует по замыслу.

Answer 3

Если вы измените запрос на

SELECT 
     Foo
     , About
     , CASE WHEN Foo LIKE 'Bar ' THEN 'T' ELSE 'F' END As Like_Bar_Space
     , CASE WHEN Foo LIKE 'Bar'  THEN 'T' ELSE 'F' END As Like_Bar
     , CASE WHEN Foo =    'Bar ' THEN 'T' ELSE 'F' END As EQ_Bar_Space
     , CASE WHEN Foo =    'Bar'  THEN 'T' ELSE 'F' END As EQ_Bar
FROM @TestResults

это дает вам лучший обзор, поскольку вы видите результат различных условий по отдельности:

Foo     About         Like_Bar_Space   Like_Bar   EQ_Bar_Space   EQ_Bar
------  ------------  ---------------  ---------  -------------  ------
Bar     No spaces      F                T          T              T
Bar     Space Suffix   T                T          T              T
 Bar    Space prefix   F                F          F              F

Похоже на равных =игнорирует конечные пробелы как в искомой строке, так и в шаблоне. LIKE, однако, не игнорирует конечный пробел в шаблоне, но игнорирует дополнительный конечный пробел в искомой строке. Ведущие пробелы никогда не игнорируются.

Я не знаю, как туда попали неправильные записи, но вы можете исправить их с помощью

UPDATE @TestResults SET Foo = TRIM(Foo)

---

Вы можете сделать тест, чувствительный к конечному пробелу, с помощью:

WHERE t.Foo + ";" = pattern + ";" 

Вы можете сделать тест нечувствительности к конечному пробелу с помощью:

WHERE RTRIM(t.Foo) = RTRIM(pattern)