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.

Sign up for GitHub

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

Jump to bottom

Query: when rewriting null semantics for comparisons with functions use function specific metadata to get better SQL #18555

Closed
maumar opened this issue Oct 23, 2019 · 0 comments · Fixed by #19607
Closed

Query: when rewriting null semantics for comparisons with functions use function specific metadata to get better SQL #18555

maumar opened this issue Oct 23, 2019 · 0 comments · Fixed by #19607
Assignees
@maumar
Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. providers-beware type-enhancement
Milestone
5.0.0

Comments

@maumar
Copy link
Contributor

maumar commented Oct 23, 2019 •
edited by smitpatel
Loading

Some functions can only be nullable if their arguments are nullable (e.g. ToUpper, SubString). This results in much simpler and faster SQL - by removing unnecessary function calls to determine if the values are null we can levarage indexes better.

Some functions (e.g. DATEDIFF) use some of the arguments to determine its nullability but not all of them.

Some functions (e.g. JSON_VALUE) can be null even when neither of its arguments are null.

We should add metadata for the built-in functions so we know how to deal with their nullability, as well as provide means for user defined functions to specify how they should behave
@maumar maumar mentioned this issue Oct 24, 2019
Merged
@ajcvickers ajcvickers added this to the Backlog milestone Oct 25, 2019
This was referenced Nov 1, 2019
Merged
Open
Closed
@maumar maumar self-assigned this Nov 8, 2019
@maumar maumar modified the milestones: Backlog, 5.0.0 Nov 14, 2019
@maumar maumar mentioned this issue Nov 26, 2019
Merged
@maumar maumar mentioned this issue Dec 6, 2019
Closed
@maumar maumar mentioned this issue Dec 31, 2019
Closed
maumar added a commit that referenced this issue Jan 15, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
3c88271 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
canBeNull - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
maumar added a commit that referenced this issue Jan 16, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
3b276cb 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
nullResultAllowed - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
@maumar maumar mentioned this issue Jan 16, 2020
Closed
maumar added a commit that referenced this issue Jan 16, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
ecdeed4 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
nullResultAllowed - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
@maumar maumar added closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. providers-beware labels Jan 16, 2020
maumar added a commit that referenced this issue Jan 22, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
9cfbb33 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
nullResultAllowed - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
maumar added a commit that referenced this issue Jan 22, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
df884df 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
nullResultAllowed - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
maumar added a commit that referenced this issue Jan 22, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
9ca9ce7 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
nullResultAllowed - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
maumar added a commit that referenced this issue Jan 22, 2020
@maumar
Fix to #18555 - Query: when rewriting null semantics for comparisons …
2a8678b 
…with functions use function specific metadata to get better SQL

When we need to compute whether a function is null, we often can just evaluate nullability of it's constituents (instance & arguments), e.g.
SUBSTRING(stringProperty, 0, 5) == null -> stringProperty == null

Adding metadata to SqlFunctionExpression:
nullResultAllowed - indicates whether function can ever be null,
instancePropagatesNullability - indicates whether function instance can be used to calculate nullability of the entire function
argumentsPropagateNullability - array indicating which (if any) function arguments can be used to calculate nullability of the entire function

If "canBeNull" is set to false we can instantly compute IsNull/IsNotNull of that function.
Otherwise, we look at values of instancePropagatesNullability and argumentsPropagateNullability - if any of them are set to true, we use corresponding argument(s) to compute function nullability.
If all of them are set to false we must fallback to the old method and evaluate nullability of the entire function.
@maumar maumar mentioned this issue Jan 22, 2020
Merged
@maumar maumar closed this as completed in d75a0e5 Jan 22, 2020
@ajcvickers ajcvickers mentioned this issue Jan 30, 2020
Closed
@maumar maumar mentioned this issue Mar 4, 2020
Merged
@ajcvickers ajcvickers modified the milestones: 5.0.0, 5.0.0-preview1 Mar 13, 2020
@ajcvickers ajcvickers modified the milestones: 5.0.0-preview1, 5.0.0 Nov 7, 2020
@maumar maumar mentioned this issue May 28, 2024
Merged
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@maumar maumar

Labels
area-query closed-fixed The issue has been fixed and is/will be included in the release indicated by the issue milestone. providers-beware type-enhancement
Projects
None yet
Milestone
5.0.0
3 participants
@ajcvickers @smitpatel @maumar