Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query: optimize database null-semantics propagation #34126

Open
ranma42 opened this issue Jun 30, 2024 · 7 comments
Open

Query: optimize database null-semantics propagation #34126

ranma42 opened this issue Jun 30, 2024 · 7 comments

Comments

@ranma42
Copy link
Contributor

ranma42 commented Jun 30, 2024

We can optimize some case block for nullability by letting expressions take care of it instead of doing explicit checks.

db.Set<NullSemanticsEntity1>().Select(
    x => x.NullableStringA != null && x.NullableStringB != null
        ? x.NullableStringA + x.NullableStringB
        : default)

translates to

SELECT CASE
    WHEN "e"."NullableStringA" IS NOT NULL AND "e"."NullableStringB" IS NOT NULL
        THEN "e"."NullableStringA" || "e"."NullableStringB"
    ELSE NULL
END
FROM "Entities1" AS "e"

but ideally it should translate to

SELECT "e"."NullableStringA" || "e"."NullableStringB"
FROM "Entities1" AS "e"

This is in a sense the dual of pushing nullability information from clause test to clause result, but it also relies on clause/else matching; see #25977 for more cross-clause optimizations.

@ranma42
Copy link
Contributor Author

ranma42 commented Jun 30, 2024

This could be a way to achieve database null semantics for some operations (see #33616 for an issue related to this).
When compared to a flag, it has the obvious disadvantage that null checks are required in the C# expressions; OTOH it can be used locally (even in subqueries) and it is a valid optimization regardless of that (specifically, it would be quite effective whenever a developer is manually trying to replicate the SQL null semantics for an operation).

@ranma42
Copy link
Contributor Author

ranma42 commented Jul 3, 2024

Related to #16050

@roji
Copy link
Member

roji commented Jul 8, 2024

Just noting that the original query is "badly written", i.e. the user could just write:

db.Set<NullSemanticsEntity1>().Select(x => x.NullableStringA + x.NullableStringB)

We generally don't go too far into "correcting" badly-written queries. We do go into this when there's good reason to believe that such a query could be a result of previous transformations that we do in the EF pipeline, of course.

@roji roji added this to the Backlog milestone Jul 8, 2024
@ranma42
Copy link
Contributor Author

ranma42 commented Jul 8, 2024

Just noting that the original query is "badly written", i.e. the user could just write:

db.Set<NullSemanticsEntity1>().Select(x => x.NullableStringA + x.NullableStringB)

#33616 is about this... if the translation of string concatenation is not settled, I can use a different operation for the tests.

We generally don't go too far into "correcting" badly-written queries. We do go into this when there's good reason to believe that such a query could be a result of previous transformations that we do in the EF pipeline, of course.

I believe some of these checks are added by the pipeline, but those in the tests are definitely introduced manually.
Note that several operations cannot be easily expressed in C# without handling the null case in some way.
What is in general the idiomatic way to request null propagation for those cases?

@roji
Copy link
Member

roji commented Jul 8, 2024

Note that several operations cannot be easily expressed in C# without handling the null case in some way.
What is in general the idiomatic way to request null propagation for those cases?

Can you provide more context on what you're asking? I'm not sure I understand exactly.

@ranma42
Copy link
Contributor Author

ranma42 commented Jul 8, 2024

Note that several operations cannot be easily expressed in C# without handling the null case in some way.
What is in general the idiomatic way to request null propagation for those cases?

Can you provide more context on what you're asking? I'm not sure I understand exactly.

A simple example is getting the length of a (nullable) string:

  1. Select(b => b.NullableString?.Length) cannot be used in expressions 😭
  2. Select(b => b.NullableString == null ? default(int?) : b.NullableString.Length)
  3. Select(b => b.NullableString.Length) causes a warning when #nullable enable; fails at runtime (basically because the type is int instead of int?)
  4. Select(b => b.NullableString!.Length) could be problematic as in other contexts ! is often considered undesirable (and possibly even linted against); fails at runtime (again int vs int?)
  5. Select(b => MyLengthFunction(b.NullableString)) would work, but requires defining MyLengthFunction as UDF (and typing it so that it returns an int?, which iiuc is not idiomatic either).

Option 1 cannot be used at all (does not even compile).
Option 3/4 work if no actual null is being propagated, but then it's not really relevant for null propagation.
Option 2 works, but iiuc might be considered a bad way to express this, which hopefully means that there is a better way to compute this.
Option 5 works, but it quickly diminishes the value of the ORM.

@ranma42
Copy link
Contributor Author

ranma42 commented Jul 8, 2024

I am not sure if this is noteworthy, but on EFCore 8.0.6

db.Blogs.Select(x => x.NullableString == null ? default(int?) : x.NullableString.Length ).ToList();

translates to

      SELECT length("b"."NullableString")
      FROM "Blogs" AS "b"

while

db.Blogs.Select(x => x.NullableString == null ? default(int?) : 1 + x.NullableString.Length ).ToList();

translates to

      SELECT CASE
          WHEN "b"."NullableString" IS NULL THEN NULL
          ELSE 1 + length("b"."NullableString")
      END
      FROM "Blogs" AS "b"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

5 participants