Query: simplify IndexOf translation

[maumar]

Currently query like:

es.Where(e => e.NullableStringA.IndexOf("oo") == e.NullableIntA).Select(e => e.Id)

gets translated to:

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE (CASE
    WHEN N'oo' = N'' THEN 0
    ELSE CHARINDEX(N'oo', [e].[NullableStringA]) - 1
END = [e].[NullableIntA]) OR (CASE
    WHEN N'oo' = N'' THEN 0
    ELSE CHARINDEX(N'oo', [e].[NullableStringA]) - 1
END IS NULL AND [e].[NullableIntA] IS NULL)

This seems too complicated. While it's true that if argument is and empty string we can short circuit and not compute the result of CHARINDEX function it doesn't seem too common (usually queries are on indexof different than emtpy).
Another reason might have been that for cases when target is null, and argument is empty string we return 0, rather than null (i.e. any string starts with empty string - which is consistent with out mental model of null meaning unknown value). However this scenario is invalid on c# already (null ref) and seems like sacrificing 99% scenario for the sake of 1%.

Instead, we could simply translate to:

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE CHARINDEX(N'oo', [e].[NullableStringA]) - 1 = [e].[NullableIntA]
OR (CHARINDEX(N'oo', [e].[NullableStringA]) IS NULL AND [e].[NullableIntA] IS NULL)

which, once #18555 is done, can be further optimized to:

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE CHARINDEX(N'oo', [e].[NullableStringA]) - 1 = [e].[NullableIntA]
OR ([e].[NullableStringA] IS NULL AND [e].[NullableIntA] IS NULL)

[maumar]

Also this scenario yields different results between SqlServer and Sqlite, when the argument to IndexOf is empty string, i.e.

es.Where(e => e.NullableStringA.IndexOf("") == e.NullableIntA)

Sqlite produces:

SELECT "e"."Id"
FROM "Entities1" AS "e"
WHERE ((instr("e"."NullableStringA", '') - 1) = "e"."NullableIntA") OR (instr("e"."NullableStringA", '') IS NULL AND "e"."NullableIntA" IS NULL)

[ajcvickers]

Theoretically, could do fancy parameter snipping to special case empty string.

[ranma42]

Currently the translation for

ss.Set<NullSemanticsEntity1>().Where(e => e.NullableStringA.IndexOf("oo") == e.NullableIntA).Select(e => e.Id)

(see here) is

SELECT [e].[Id]
FROM [Entities1] AS [e]
WHERE CAST(CHARINDEX(N'oo', [e].[NullableStringA]) AS int) - 1 = [e].[NullableIntA]
OR ([e].[NullableStringA] IS NULL AND [e].[NullableIntA] IS NULL)

(see here).

Is there any further improvement that we should look for? (I see that there is a CAST in addition to the expected query)

Regarding the mismatch in the results from SQLite and SQL Server, it was apparently caused by the non-propagation of NULL when the pattern was the empty string; I tried to fix it in #33876.