Skip to content

Commit 8d84df4

Browse files
jods4sdanylivMaceWindu
authored
Null comparison changes, rebased #3537 (#4547)
* Tests for #3535 * Places where withNull: false was confused for null * Don't sniff values under !CompareNullsAsValues * Places that say "withNull: null" but actually relied on == null * Fix build * Reduce the amount of tests run. New fixture added 2160 tests, but we can run it for a single provider instead (72 tests). * Make the new behavior configurable for back-compat * Fix Oracle handling of empty string "" * Fix compilation errors after rebase * Fixed Nullable comparison. * Address non-functional reviews * Fix build + more comments * Fix build * Fix build for real * Add tests for #4163 Enums mapped to empty string in Oracle * Fix build * update tests for #4163 * disable test for now as out-of-scope for this PR * remove unnecessary variable * disable broken test --------- Co-authored-by: Svyatoslav Danyliv <[email protected]> Co-authored-by: MaceWindu <[email protected]>
1 parent fb03f08 commit 8d84df4

35 files changed

+754
-301
lines changed

‎Source/LinqToDB/Common/CompareNulls.cs‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,44 @@
1+
namespace LinqToDB.Common
2+
{
3+
/// <summary>Defines how null values are compared in generated SQL queries.</summary>
4+
public enum CompareNulls
5+
{
6+
/// <summary>CLR semantics: nulls values are comparable/equal.</summary>
7+
/// <note>This option may add significant complexity in generated SQL, possibly preventing index usage.</note>
8+
/// <example>
9+
/// <code>
10+
/// public class MyEntity
11+
/// {
12+
/// public int? Value;
13+
/// }
14+
///
15+
/// db.MyEntity.Where(e => e.Value != 10)
16+
///
17+
/// from e1 in db.MyEntity
18+
/// join e2 in db.MyEntity on e1.Value equals e2.Value
19+
/// select e1
20+
///
21+
/// var filter = new [] {1, 2, 3};
22+
/// db.MyEntity.Where(e => ! filter.Contains(e.Value))
23+
/// </code>
24+
///
25+
/// Would be translated into:
26+
/// <code>
27+
/// SELECT Value FROM MyEntity WHERE Value IS NULL OR Value != 10
28+
///
29+
/// SELECT e1.Value
30+
/// FROM MyEntity e1
31+
/// INNER JOIN MyEntity e2 ON e1.Value = e2.Value OR (e1.Value IS NULL AND e2.Value IS NULL)
32+
///
33+
/// SELECT Value FROM MyEntity WHERE Value IS NULL OR NOT Value IN (1, 2, 3)
34+
/// </code>
35+
/// </example>
36+
LikeClr,
37+
/// <summary>SQL semantics: nulls values compare as UNKNOWN (three-valued logic).</summary>
38+
/// <note>This translates C# straight to equivalent SQL, which has different semantics when comapring NULLs.</note>
39+
LikeSql,
40+
/// <summary>SQL semantics, except null parameters are treated like null constants (they compile to <c>IS NULL</c>).</summary>
41+
/// <note>This value exists mostly for backward compatibility with versions before 6.0. <see cref="LikeSql"/> is recommended instead.</note>
42+
LikeSqlExceptParameters,
43+
}
44+
}

‎Source/LinqToDB/Common/Configuration.cs‎

Lines changed: 21 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -1,15 +1,15 @@
11
using System;
2+
using System.ComponentModel;
23
using System.Data;
34
using System.Linq.Expressions;
45
using System.Reflection;
6+
using System.Text;
57
using System.Threading.Tasks;
68

79
using JetBrains.Annotations;
810

911
namespace LinqToDB.Common
1012
{
11-
using System.Text;
12-
1313
using Data;
1414
using Data.RetryPolicy;
1515
using Linq;
@@ -283,9 +283,14 @@ public static bool OptimizeJoins
283283
}
284284

285285
/// <summary>
286-
/// If set to true nullable fields would be checked for IS NULL in Equal/NotEqual comparisons.
286+
/// If set to <see cref="CompareNulls.LikeClr" /> nullable fields would be checked for <c>IS NULL</c> in Equal/NotEqual comparisons.
287+
/// If set to <see cref="CompareNulls.LikeSql" /> comparisons are compiled straight to equivalent SQL operators,
288+
/// which consider nulls values as not equal.
289+
/// <see cref="CompareNulls.LikeSqlExceptParameters" /> is a backward compatible option that works mostly as <see cref="CompareNulls.LikeSql" />,
290+
/// but sniffs parameters value and changes = into <c>IS NULL</c> when parameters are null.
291+
/// Comparisons to literal null are always compiled into <c>IS NULL</c>.
287292
/// This affects: Equal, NotEqual, Not Contains
288-
/// Default value: <c>true</c>.
293+
/// Default value: <see cref="CompareNulls.LikeClr" />.
289294
/// </summary>
290295
/// <example>
291296
/// <code>
@@ -304,7 +309,7 @@ public static bool OptimizeJoins
304309
/// db.MyEntity.Where(e => ! filter.Contains(e.Value))
305310
/// </code>
306311
///
307-
/// Would be converted to next queries:
312+
/// Would be converted to next queries under <see cref="CompareNulls.LikeClr" />:
308313
/// <code>
309314
/// SELECT Value FROM MyEntity WHERE Value IS NULL OR Value != 10
310315
///
@@ -315,16 +320,23 @@ public static bool OptimizeJoins
315320
/// SELECT Value FROM MyEntity WHERE Value IS NULL OR NOT Value IN (1, 2, 3)
316321
/// </code>
317322
/// </example>
318-
public static bool CompareNullsAsValues
323+
public static CompareNulls CompareNulls
319324
{
320-
get => Options.CompareNullsAsValues;
325+
get => Options.CompareNulls;
321326
set
322327
{
323-
if (Options.CompareNullsAsValues != value)
324-
Options = Options with { CompareNullsAsValues = value };
328+
if (Options.CompareNulls != value)
329+
Options = Options with { CompareNulls = value };
325330
}
326331
}
327332

333+
[Obsolete("Use CompareNulls instead: true maps to LikeClr and false to LikeSqlExceptParameters"), EditorBrowsable(EditorBrowsableState.Never)]
334+
public static bool CompareNullsAsValues
335+
{
336+
get => CompareNulls == CompareNulls.LikeClr;
337+
set => CompareNulls = value ? CompareNulls.LikeClr : CompareNulls.LikeSqlExceptParameters;
338+
}
339+
328340
/// <summary>
329341
/// Controls behavior of LINQ query, which ends with GroupBy call.
330342
/// - if <c>true</c> - <seealso cref="LinqToDBException"/> will be thrown for such queries;

‎Source/LinqToDB/Data/DataConnection.QueryRunner.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -224,7 +224,7 @@ static PreparedQuery GetCommand(DataConnection dataConnection, IQueryContext que
224224
{
225225
if (!statement.IsParameterDependent)
226226
{
227-
if (sqlOptimizer.IsParameterDependent(NullabilityContext.NonQuery, statement))
227+
if (sqlOptimizer.IsParameterDependent(NullabilityContext.NonQuery, statement, options))
228228
statement.IsParameterDependent = true;
229229
}
230230
}

‎Source/LinqToDB/DataProvider/Access/AccessSqlOptimizer.cs‎

Lines changed: 5 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -173,9 +173,9 @@ SqlStatement CorrectExistsAndIn(SqlStatement statement, DataOptions dataOptions)
173173

174174
var countExpr = SqlFunction.CreateCount(typeof(int), existsQuery.From.Tables[0]);
175175
if (!isNot)
176-
newSearch.AddGreater(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNullsAsValues);
176+
newSearch.AddGreater(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNulls);
177177
else
178-
newSearch.AddEqual(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNullsAsValues);
178+
newSearch.AddEqual(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNulls);
179179

180180
existsQuery.Select.AddColumn(newSearch);
181181

@@ -186,7 +186,7 @@ SqlStatement CorrectExistsAndIn(SqlStatement statement, DataOptions dataOptions)
186186
{
187187
var subquery = inSubQuery.SubQuery;
188188
subquery.Where.EnsureConjunction()
189-
.AddEqual(subquery.Select.Columns[0].Expression, inSubQuery.Expr1, dataOptions.LinqOptions.CompareNullsAsValues);
189+
.AddEqual(subquery.Select.Columns[0].Expression, inSubQuery.Expr1, dataOptions.LinqOptions.CompareNulls);
190190

191191
subquery.Select.Columns.Clear();
192192

@@ -196,9 +196,9 @@ SqlStatement CorrectExistsAndIn(SqlStatement statement, DataOptions dataOptions)
196196
isNot = isNot != inSubQuery.IsNot;
197197

198198
if (!isNot)
199-
newSearch.AddGreater(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNullsAsValues);
199+
newSearch.AddGreater(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNulls);
200200
else
201-
newSearch.AddEqual(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNullsAsValues);
201+
newSearch.AddEqual(countExpr, new SqlValue(0), dataOptions.LinqOptions.CompareNulls);
202202

203203
subquery.Select.AddColumn(newSearch);
204204

‎Source/LinqToDB/DataProvider/Firebird/FirebirdSqlExpressionConvertVisitor.cs‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -147,7 +147,7 @@ protected override ISqlExpression ConvertConversion(SqlCastExpression cast)
147147
if (cast.Type.DbType == nameof(Sql.Types.Bit) && cast.Expression is not ISqlPredicate)
148148
{
149149
var sc = new SqlSearchCondition()
150-
.AddNotEqual(cast.Expression, new SqlValue(0), DataOptions.LinqOptions.CompareNullsAsValues);
150+
.AddNotEqual(cast.Expression, new SqlValue(0), DataOptions.LinqOptions.CompareNulls);
151151
return sc;
152152

153153
}

‎Source/LinqToDB/DataProvider/Firebird/FirebirdSqlOptimizer.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -22,9 +22,9 @@ public override SqlStatement Finalize(MappingSchema mappingSchema, SqlStatement
2222
return statement;
2323
}
2424

25-
public override bool IsParameterDependedElement(NullabilityContext nullability, IQueryElement element)
25+
public override bool IsParameterDependedElement(NullabilityContext nullability, IQueryElement element, DataOptions dataOptions)
2626
{
27-
var result = base.IsParameterDependedElement(nullability, element);
27+
var result = base.IsParameterDependedElement(nullability, element, dataOptions);
2828
if (result)
2929
return true;
3030

‎Source/LinqToDB/DataProvider/Informix/InformixSqlOptimizer.cs‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -17,9 +17,9 @@ public override SqlExpressionConvertVisitor CreateConvertVisitor(bool allowModif
1717
return new InformixSqlExpressionConvertVisitor(allowModify);
1818
}
1919

20-
public override bool IsParameterDependedElement(NullabilityContext nullability, IQueryElement element)
20+
public override bool IsParameterDependedElement(NullabilityContext nullability, IQueryElement element, DataOptions dataOptions)
2121
{
22-
if (base.IsParameterDependedElement(nullability, element))
22+
if (base.IsParameterDependedElement(nullability, element, dataOptions))
2323
return true;
2424

2525
switch (element.ElementType)

‎Source/LinqToDB/DataProvider/Oracle/Oracle11SqlOptimizer.cs‎

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -2,6 +2,7 @@
22

33
namespace LinqToDB.DataProvider.Oracle
44
{
5+
using Common;
56
using Mapping;
67
using SqlProvider;
78
using SqlQuery;
@@ -30,27 +31,26 @@ public override SqlStatement TransformStatement(SqlStatement statement, DataOpti
3031
return statement;
3132
}
3233

33-
public override bool IsParameterDependedElement(NullabilityContext nullability, IQueryElement element)
34+
public override bool IsParameterDependedElement(NullabilityContext nullability, IQueryElement element, DataOptions dataOptions)
3435
{
35-
if (base.IsParameterDependedElement(nullability, element))
36+
if (base.IsParameterDependedElement(nullability, element, dataOptions))
3637
return true;
3738

3839
switch (element.ElementType)
3940
{
4041
case QueryElementType.ExprExprPredicate:
4142
{
42-
var expr = (SqlPredicate.ExprExpr)element;
43-
44-
// Oracle saves empty string as null to database, so we need predicate modification before sending query
45-
//
46-
if ((expr.Operator == SqlPredicate.Operator.Equal ||
47-
expr.Operator == SqlPredicate.Operator.NotEqual ||
48-
expr.Operator == SqlPredicate.Operator.GreaterOrEqual ||
49-
expr.Operator == SqlPredicate.Operator.LessOrEqual) && expr.WithNull == true)
43+
var (a, op, b, withNull) = (SqlPredicate.ExprExpr)element;
44+
45+
// This condition matches OracleSqlExpressionConvertVisitor.ConvertExprExprPredicate,
46+
// where we transform empty strings "" into null-handling expressions.
47+
if (withNull != null ||
48+
(dataOptions.LinqOptions.CompareNulls != CompareNulls.LikeSql &&
49+
op is SqlPredicate.Operator.Equal or SqlPredicate.Operator.NotEqual))
5050
{
51-
if (expr.Expr1.SystemType == typeof(string) && expr.Expr1.CanBeEvaluated(true))
51+
if (a.SystemType == typeof(string) && a.CanBeEvaluated(true))
5252
return true;
53-
if (expr.Expr2.SystemType == typeof(string) && expr.Expr2.CanBeEvaluated(true))
53+
if (b.SystemType == typeof(string) && b.CanBeEvaluated(true))
5454
return true;
5555
}
5656
break;
@@ -84,7 +84,7 @@ protected SqlStatement ReplaceTakeSkipWithRowNum(SqlStatement statement, bool on
8484

8585
if (query.Select.TakeValue != null && query.Select.OrderBy.IsEmpty && query.GroupBy.IsEmpty && !query.Select.IsDistinct)
8686
{
87-
query.Select.Where.EnsureConjunction().AddLessOrEqual(RowNumExpr, query.Select.TakeValue, false);
87+
query.Select.Where.EnsureConjunction().AddLessOrEqual(RowNumExpr, query.Select.TakeValue, CompareNulls.LikeSql);
8888

8989
query.Select.Take(null, null);
9090
return 0;
@@ -105,14 +105,14 @@ protected SqlStatement ReplaceTakeSkipWithRowNum(SqlStatement statement, bool on
105105
if (query.Select.TakeValue != null)
106106
{
107107
processingQuery.Where.EnsureConjunction().AddLessOrEqual(RowNumExpr, new SqlBinaryExpression(query.Select.SkipValue.SystemType!,
108-
query.Select.SkipValue, "+", query.Select.TakeValue), false);
108+
query.Select.SkipValue, "+", query.Select.TakeValue), CompareNulls.LikeSql);
109109
}
110110

111-
queries[queries.Count - 3].Where.SearchCondition.AddGreater(rnColumn, query.Select.SkipValue, false);
111+
queries[queries.Count - 3].Where.SearchCondition.AddGreater(rnColumn, query.Select.SkipValue, CompareNulls.LikeSql);
112112
}
113113
else
114114
{
115-
processingQuery.Where.EnsureConjunction().AddLessOrEqual(RowNumExpr, query.Select.TakeValue!, false);
115+
processingQuery.Where.EnsureConjunction().AddLessOrEqual(RowNumExpr, query.Select.TakeValue!, CompareNulls.LikeSql);
116116
}
117117

118118
query.Select.SkipValue = null;

0 commit comments

Comments
 (0)