Incorrect string matching behavior on SQL Server with whitespace patterns

[nesterenko-kv]

I'm getting wrong results from database when trying to filter string (Contains, StartsWith, EndsWith, etc) with spaces.

Steps to reproduce

I'm build query like:

var test = "  ";
var venues = await context.Venues.Where(x => x.Name.Contains(test)).ToListAsync(cancellationToken);

Which gets translated to:

SELECT [v].[Id], [v].[Name]
FROM [Venues] AS [v]
WHERE (@__test_0 = N'') OR CHARINDEX(@__test_0, [v].[Name]) > 0

This query returns all rows from database when @__test_0 contains spaces.

Further technical details

EF Core version: 3.1.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET Core 3.1

[ajcvickers]

@nesterenko-kv Please post a small, runnable project or code listing that reproduces the behavior you are seeing so that we can investigate.

[nesterenko-kv]

@ajcvickers repro. Let me know, if you need more details.

[ajcvickers]

Note for team: I was able to reproduce this with 3.1. More minimal repro and output:

public class Venue
{
    public Guid Id { get; set; }
    public string Name { get; set; }
}

public class BloggingContext : DbContext
{
    private readonly ILoggerFactory Logger 
        = LoggerFactory.Create(c => c.AddConsole());//.SetMinimumLevel(LogLevel.Debug));

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder
            .UseLoggerFactory(Logger)
            .EnableSensitiveDataLogging()
            .UseSqlServer(@"Server=(localdb)\mssqllocaldb;Database=Test;ConnectRetryCount=0");
    }
    
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Venue>();    
    }
}

public class Program
{
    public static async Task Main()
    {
        using (var context = new BloggingContext())
        {
            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            context.Add(new Venue { Name = "Test" });
            
            await context.SaveChangesAsync();
        }

        using (var context = new BloggingContext())
        {
            var test = "  ";
            var venues = await context.Set<Venue>().Where(x => x.Name.Contains(test)).ToListAsync();
            Console.WriteLine($"Matched {venues.Count}");
        }
    }
}

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[@__test_0='  ' (Size = 4000)], CommandType='Text', CommandTimeout='30']
      SELECT [v].[Id], [v].[Name]
      FROM [Venue] AS [v]
      WHERE (@__test_0 = N'') OR (CHARINDEX(@__test_0, [v].[Name]) > 0)
Matched 1

[ajcvickers]

Notes from team discussion:

• This happens because of the way trailing spaces are handled by SQL Server. This makes ' ' = '' return true!
• Ideal fix would be to implement General QueryContext-based mechanism for caching of generated RelationalCommand #17598 and sniff for the empty string parameter so that we can generate different SQL. However, General QueryContext-based mechanism for caching of generated RelationalCommand #17598 is on the backlog.
• Another way to fix would be to check the length of the parameter rather than comparing to the empty string. However, this might cause index misses.

[ajcvickers]

Notes from further team discussion:

• General QueryContext-based mechanism for caching of generated RelationalCommand #17598 is not currently scheduled for EF Core 5. On the other hand, we don't want to wait until after 5.0 to fix this issue. Therefore @roji and @maumar will look into a tactical fix even if we later do General QueryContext-based mechanism for caching of generated RelationalCommand #17598.
• We should also consider the case where the empty string is coming from a column, although this is likely to be rarer.
• Clarification on my last point above: we should check that we don't break hitting indexes with whatever solution we come up with. Probably checking the length will be okay.

[roji]

Summary

On SQL Server, LEN(col) = 0 suffers from the same issue as col = '': it identifies spaces-only strings a having length 0 (incidentally it also does not utilize indexes).

However, col LIKE '' may be a good solution: it only identifies truly empty strings and uses an index. At least on PostgreSQL (where this whole trailing spaces madness doesn't happen), LIKE also uses an index.

We should ideally verify also on Sqlite and MySQL, though I'd have to spend more time to understand how to analyze queries there. We can postpone dealing with this to see whether we end up doing #17598 for 5.0 (which I'd really like to).

---

SQL Server

Behavior around trailing spaces

CREATE TABLE text_tests (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(10));
INSERT INTO text_tests (name) VALUES ('');
INSERT INTO text_tests (name) VALUES ('  ');
SELECT COUNT(*) FROM text_tests WHERE name=''; -- Returns both
SELECT LEN(name) FROM text_tests; -- Returns 0 for both
SELECT COUNT(*) FROM text_tests WHERE name LIKE '';  -- Returns only the 1st
SELECT '|' + name + '|' FROM text_tests;  -- Returns || for the first, |  | for the second

Index usage

-- Create schema and insert data
CREATE TABLE data (id INT IDENTITY(1,1) PRIMARY KEY, name NVARCHAR(10));
CREATE INDEX IX_name ON data(name);

BEGIN TRANSACTION;

DECLARE @i INT = 0;
WHILE @i < 50000
BEGIN
    INSERT INTO data (name) VALUES (CAST(@i AS NVARCHAR(MAX)));
    SET @i = @i + 1;
END;

INSERT INTO data (name) VALUES ('');
INSERT INTO data (name) VALUES ('  ');

DECLARE @j INT = @i;
WHILE @j < 100000
BEGIN
    INSERT INTO data (name) VALUES (CAST(@j AS NVARCHAR(MAX)));
    SET @j = @j + 1;
END;

COMMIT;

-- Queries
SET SHOWPLAN_ALL ON;
SELECT id FROM data WHERE name=''; -- Does an index seek with IX_name, TotalSubtreeCost=0.0032842
SELECT id FROM data WHERE LEN(name) = 0; -- Scans the entire table, TotalSubtreeCost=0.48661754
SELECT id FROM data WHERE name LIKE ''; -- Does an index scan with IX_name, TotalSubtreeCost=0.0032842
SET SHOWPLAN_ALL OFF;

PostgreSQL

Behavior around trailing spaces

DROP TABLE IF EXISTS text_types;
CREATE TABLE text_types (
    id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    text TEXT,
    varchar VARCHAR(10),
    char VARCHAR(10));
INSERT INTO text_types (text, varchar, char) VALUES ('', '', '');
INSERT INTO text_types (text, varchar, char) VALUES ('  ', '  ', '  ');

SELECT * FROM text_types WHERE text = ''; -- Returns 1st row only
SELECT * FROM text_types WHERE varchar = ''; -- Returns 1st row only
SELECT * FROM text_types WHERE char = ''; -- Returns 1st row only

SELECT LENGTH(text), LENGTH(varchar), LENGTH(char) FROM text_types; -- 1st row all zeros, 2nd row all 2s 
SELECT '|' || text || '|' || varchar || '|' || char || '|' FROM text_types; -- 1st row no spaces, 2nd row 2 spaces everywhere

Index usage

-- Create schema and insert data
DROP TABLE IF EXISTS data;
CREATE TABLE data (id INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY , name TEXT);
CREATE INDEX IX_name ON data(name);

DO $$BEGIN
  FOR i IN 1..50000 LOOP
    INSERT INTO data (name) VALUES (i::TEXT);     
  END LOOP;
  INSERT INTO data (name) VALUES ('');
  INSERT INTO data (name) VALUES ('  ');
  FOR i IN 50001..100000 LOOP
    INSERT INTO data (name) VALUES (i::TEXT);
  END LOOP;
END$$;

-- Queries - same index usage results as SQL Server
EXPLAIN SELECT * FROM data WHERE name='';
EXPLAIN SELECT * FROM data WHERE LENGTH(name) = 0;
EXPLAIN SELECT * FROM data WHERE name LIKE '';

Sqlite

Behavior around trailing spaces

CREATE TABLE text_tests (id INT, name TEXT);
INSERT INTO text_tests (id, name) VALUES (1, '');
INSERT INTO text_tests (id, name) VALUES (2, '  ');
SELECT id FROM text_tests WHERE name=''; -- Returns only id=1
SELECT LENGTH(name) FROM text_tests;  -- Returns 0 and 2
SELECT id FROM text_tests WHERE name LIKE ''; -- Returns only id=1
SELECT '|' || name || '|' FROM text_tests;  -- Returns || for the first, |  | for the second

Index usage

CREATE TABLE data (id INTEGER PRIMARY KEY, name TEXT);
CREATE INDEX IX_name ON data(name);
-- Insert 1..50000
INSERT INTO data
SELECT x, x FROM (
  WITH RECURSIVE
      cnt(x) AS (
          SELECT 1
          UNION ALL
          SELECT x+1 FROM cnt
          LIMIT 50000
      )
  SELECT x FROM cnt
);
INSERT INTO data (id, name) VALUES (200000, '');
INSERT INTO data (id, name) VALUES (200001, '  ');
-- Insert 50001..100000
INSERT INTO data
SELECT x, x FROM (
                     WITH RECURSIVE
                         cnt(x) AS (
                             SELECT 50001
                             UNION ALL
                             SELECT x+1 FROM cnt
                             LIMIT 50000
                         )
                     SELECT x FROM cnt
                 );

SELECT COUNT(*) FROM data;    
    
EXPLAIN QUERY PLAN SELECT * FROM data WHERE name=''; -- SEARCH TABLE data USING COVERING INDEX IX_name (name=?)
EXPLAIN QUERY PLAN SELECT * FROM data WHERE LENGTH(name) = 0; -- SCAN TABLE data
EXPLAIN QUERY PLAN SELECT * FROM data WHERE name LIKE ''; -- SCAN TABLE data