Query: Unnecessary null semantics checks

[divega]

Creating an issue to track cases in which because of null semantics we generate superfluous (but harmless) null checks in the SQL.

[divega]

One case is described in #15554 (comment):

Given the LINQ query:

var query2 = context.People
    .Select(p => p.Things.Select(t => t.Id).Contains(someThing.Id))
    .ToList();

The SQL generated is

SELECT CASE
    WHEN @__someThing_Id_0 IN (
        SELECT [t].[Id]
        FROM [Things] AS [t]
        WHERE ([p].[Id] = [t].[PersonId]) AND [t].[PersonId] IS NOT NULL
    )
     THEN CAST(1 AS bit)
    ELSE CAST(0 AS bit)
END
FROM [People] AS [p]

Note the unnecessary [t].[PersonId] IS NOT NULL check. At first glance, my take is that this shouldn't be there for two reasons:

1. The source is the expansion of a navigation between Person and Thing, so it shouldn't apply null semantics at all
2. Even if null semantics were necessary, this term wouldn't be necessary because it is used in a WHERE clause so any rows for which [p].[Id] = [t].[PersonId] evaluates to null (because [t].[PersonId] happens to null) won't be returned by the subquery.

[smitpatel]

@maumar - PTAL.

[944332213]

Equality judgments against functions still generate redundant null checks:

var query1 = context.People.Where(item => item.Name1.Length == item.Name2.Length).ToArray();

The resulting SQL is approximately:

SELECT `q`.`Id`, `q`.`Name1`, `q`.`Name2`
FROM `people` AS `q`
WHERE (CHAR_LENGTH(`q`.`Name1`) = CHAR_LENGTH(`q`.`Name2`)) OR (CHAR_LENGTH(`q`.`Name1`) IS NULL AND CHAR_LENGTH(`q`.`Name2`) IS NULL)

The above behavior also occurs on the aggregate function of the subquery.
Additional null checks will result in performance degradation.
Even when aliases are available, the resulting SQL is ignored. This behavior can sometimes lead to errors, such as fields that cannot be found on GROUP BY.
The same situation will appear with Database scalar function Mapping. It seems to be related to the null propagation of the function.

If only for equality, consider a solution:

var query2 = context.People.Where(item => item.Name1.Length - item.Name2.Length == 0).ToArray();

So it's as expected:

SELECT `q`.`Id`, `q`.`Name1`, `q`.`Name2`
FROM `people` AS `q`
WHERE (CHAR_LENGTH(`q`.`Name1`) - CHAR_LENGTH(`q`.`Name2`)) = 0

[roji]

Duplicate of #31063