Skip to content

Commit

Permalink
Refactored Coalesce to use appropriate SqlCoalesceExpression (#4548)
Browse files Browse the repository at this point in the history 
* add issue test

* Improved condition handling.

* Added translation of DefaultValueExpression.

* Another case for subquery optimization.

* Fixed boolean columns usage.

* Added better compatibility handler at the end of the query. Added two parsing steps to improve simple queries parsing speed.

* Fixed found regressions.

* Additional SqlCondtition Optimization/

* Removed PseudoFunctions.COALSESCE. For now SqlCoalesceExpression is used. Coalesce optimizations.

* Fix query validation logic.

* Cleanup.

* Refactored NullabilityContext replacements handling.

* More fixes.

* SqlCe specific parameters case.

* OrderByBuilder improvements.

* Optimization fixes.

* Fixed SqlRow nullability.

---------

Co-authored-by: MaceWindu <[email protected]>
  • Loading branch information
@sdanyliv @MaceWindu
sdanyliv and MaceWindu authored Jul 9, 2024
1 parent 571865a commit 0bdc5ed
Show file tree
Hide file tree
Showing 21 changed files with 355 additions and 279 deletions.
46 changes: 25 additions & 21 deletions Source/LinqToDB/DataProvider/Access/AccessSqlExpressionConvertVisitor.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -117,6 +117,31 @@ public override ISqlPredicate ConvertSearchStringPredicate(SqlPredicate.SearchSt
return result;
}

public override ISqlExpression ConvertCoalesce(SqlCoalesceExpression element)
{
if (SqlProviderFlags == null || element.SystemType == null)
return element;

if (element.Expressions.Length == 2)
{
return new SqlConditionExpression(new SqlPredicate.IsNull(element.Expressions[0], false), element.Expressions[1], element.Expressions[0]);
}

if (element.Expressions.Length > 2)
{
return new SqlConditionExpression(new SqlPredicate.IsNull(element.Expressions[0], false), new SqlCoalesceExpression(GetSubArray(element.Expressions)), element.Expressions[0]);
}

static ISqlExpression[] GetSubArray(ISqlExpression[] array)
{
var parms = new ISqlExpression[array.Length - 1];
Array.Copy(array, 1, parms, 0, parms.Length);
return parms;
};

return element;
}

public override ISqlExpression ConvertSqlFunction(SqlFunction func)
{
switch (func)
Expand All @@ -128,20 +153,6 @@ public override ISqlExpression ConvertSqlFunction(SqlFunction func)
case { Name: "Length" }:
return func.WithName("Len");

case {
Name: PseudoFunctions.COALESCE,
Parameters: [var p0, var p1],
SystemType: var type,
}:
return new SqlFunction(type, "IIF", new SqlSearchCondition { Predicates = { new SqlPredicate.IsNull(p0, false) } }, p1, p0);

case {
Name: PseudoFunctions.COALESCE,
Parameters: [var p0, ..] parms,
SystemType: var type,
}:
return new SqlFunction(type, PseudoFunctions.COALESCE, p0, new SqlFunction(type, PseudoFunctions.COALESCE, GetSubArray(parms)));

case {
Name: "CharIndex",
Parameters: [var p0, var p1],
Expand All @@ -158,13 +169,6 @@ public override ISqlExpression ConvertSqlFunction(SqlFunction func)

default:
return base.ConvertSqlFunction(func);
};

static ISqlExpression[] GetSubArray(ISqlExpression[] array)
{
var parms = new ISqlExpression[array.Length - 1];
Array.Copy(array, 1, parms, 0, parms.Length);
return parms;
}
}

Expand Down
17 changes: 4 additions & 13 deletions Source/LinqToDB/DataProvider/ClickHouse/ClickHouseSqlExpressionConvertVisitor.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -256,15 +256,6 @@ static SqlFunction ConvertFunc(SqlFunction func)

public override ISqlExpression ConvertSqlFunction(SqlFunction func)
{
if (SqlProviderFlags != null)
{
switch (func)
{
case SqlFunction(_, PseudoFunctions.COALESCE) :
return new SqlFunction(func.SystemType, "Coalesce", func.Parameters);
}
}

switch (func.Name)
{
case "MAX":
Expand Down Expand Up @@ -366,7 +357,7 @@ protected ISqlExpression MakeConversion(ISqlExpression expression, DbDataType to
new SqlValue((byte)(toType.Scale ?? ClickHouseMappingSchema.DEFAULT_DECIMAL_SCALE)));

if (defaultValue != null)
newFunc = ConvertSqlFunction(PseudoFunctions.MakeCoalesce(toType.SystemType, newFunc, defaultValue));
newFunc = (ISqlExpression)Visit(new SqlCoalesceExpression(newFunc, defaultValue));

return newFunc;
}
Expand All @@ -381,18 +372,18 @@ protected ISqlExpression MakeConversion(ISqlExpression expression, DbDataType to
new SqlValue((byte)(toType.Precision ?? ClickHouseMappingSchema.DEFAULT_DATETIME64_PRECISION)));

if (defaultValue != null)
newFunc = ConvertSqlFunction(PseudoFunctions.MakeCoalesce(toType.SystemType, newFunc, defaultValue));
newFunc = (ISqlExpression)Visit(new SqlCoalesceExpression(newFunc, defaultValue));

return newFunc;
}

// default call template
default:
{
ISqlExpression newFunc = new SqlFunction(toType.SystemType, name + suffix, false, true, Precedence.Primary, ParametersNullabilityType.IfAnyParameterNullable, null, value);
ISqlExpression newFunc = new SqlFunction(toType.SystemType, name + suffix, false, true, Precedence.Primary, ParametersNullabilityType.IfAnyParameterNullable, true, value);

if (defaultValue != null)
newFunc = ConvertSqlFunction(PseudoFunctions.MakeCoalesce(toType.SystemType, newFunc, defaultValue));
newFunc = (ISqlExpression)Visit(new SqlCoalesceExpression(newFunc, defaultValue));

return newFunc;
}
Expand Down
12 changes: 5 additions & 7 deletions Source/LinqToDB/DataProvider/Informix/InformixSqlExpressionConvertVisitor.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -46,14 +46,12 @@ public override IQueryElement ConvertSqlBinaryExpression(SqlBinaryExpression ele
return base.ConvertSqlBinaryExpression(element);
}

public override ISqlExpression ConvertSqlFunction(SqlFunction func)
public override ISqlExpression ConvertCoalesce(SqlCoalesceExpression element)
{
return func.Name switch
{
// passing parameter to NVL will result in "A syntax error has occurred." error from server
PseudoFunctions.COALESCE => ConvertCoalesceToBinaryFunc(func, "Nvl", supportsParameters: false),
_ => base.ConvertSqlFunction(func),
};
if (SqlProviderFlags == null || element.SystemType == null)
return element;

return ConvertCoalesceToBinaryFunc(element, "Nvl", supportsParameters : false);
}

//TODO: Move everything to SQLBuilder
Expand Down
3 changes: 0 additions & 3 deletions Source/LinqToDB/DataProvider/Oracle/OracleSqlExpressionConvertVisitor.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -128,9 +128,6 @@ public override ISqlExpression ConvertSqlFunction(SqlFunction func)
{
switch (func)
{
case { Name: "Coalesce", Parameters.Length: 2 }:
return ConvertCoalesceToBinaryFunc(func, "Nvl");

case {
Name: "CharIndex",
Parameters: [var p0, var p1],
Expand Down
32 changes: 26 additions & 6 deletions Source/LinqToDB/DataProvider/SqlCe/SqlCeSqlOptimizer.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -170,16 +170,36 @@ static void CorrectFunctionParameters(SqlStatement statement, DataOptions option

statement.Visit(static e =>
{
if (e.ElementType == QueryElementType.SqlFunction)
switch (e.ElementType)
{
var sqlFunction = (SqlFunction)e;
foreach (var parameter in sqlFunction.Parameters)
case QueryElementType.SqlFunction:
{
var sqlFunction = (SqlFunction)e;
foreach (var parameter in sqlFunction.Parameters)
{
if (parameter.ElementType == QueryElementType.SqlParameter &&
parameter is SqlParameter sqlParameter)
{
sqlParameter.IsQueryParameter = false;
}
}

break;
}

case QueryElementType.SqlCoalesce:
{
if (parameter.ElementType == QueryElementType.SqlParameter &&
parameter is SqlParameter sqlParameter)
var sqlCoalesce = (SqlCoalesceExpression)e;
foreach (var expression in sqlCoalesce.Expressions)
{
sqlParameter.IsQueryParameter = false;
if (expression.ElementType == QueryElementType.SqlParameter &&
expression is SqlParameter sqlParameter)
{
sqlParameter.IsQueryParameter = false;
}
}

break;
}
}
});
Expand Down
35 changes: 10 additions & 25 deletions Source/LinqToDB/Linq/Builder/ExpressionBuilder.SqlBuilder.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -857,34 +857,18 @@ Expression ConvertToSqlInternal(IBuildContext? context, Expression expression, P
switch (expression.NodeType)
{
case ExpressionType.Add:
case ExpressionType.AddChecked: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "+", r, Precedence.Additive), expression, alias : alias);
case ExpressionType.And: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "&", r, Precedence.Bitwise), expression, alias : alias);
case ExpressionType.Divide: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "/", r, Precedence.Multiplicative), expression, alias : alias);
case ExpressionType.ExclusiveOr: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "^", r, Precedence.Bitwise), expression, alias : alias);
case ExpressionType.Modulo: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "%", r, Precedence.Multiplicative), expression, alias : alias);
case ExpressionType.AddChecked: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "+", r, Precedence.Additive), expression, alias : alias);
case ExpressionType.And: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "&", r, Precedence.Bitwise), expression, alias : alias);
case ExpressionType.Divide: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "/", r, Precedence.Multiplicative), expression, alias : alias);
case ExpressionType.ExclusiveOr: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "^", r, Precedence.Bitwise), expression, alias : alias);
case ExpressionType.Modulo: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "%", r, Precedence.Multiplicative), expression, alias : alias);
case ExpressionType.Multiply:
case ExpressionType.MultiplyChecked: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "*", r, Precedence.Multiplicative), expression, alias : alias);
case ExpressionType.Or: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "|", r, Precedence.Bitwise), expression, alias : alias);
case ExpressionType.Power: return CreatePlaceholder(context, new SqlFunction(t, "Power", l, r), expression, alias : alias);
case ExpressionType.Or: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "|", r, Precedence.Bitwise), expression, alias : alias);
case ExpressionType.Power: return CreatePlaceholder(context, new SqlFunction(t, "Power", l, r), expression, alias : alias);
case ExpressionType.Subtract:
case ExpressionType.SubtractChecked: return CreatePlaceholder(context, new SqlBinaryExpression(t, l, "-", r, Precedence.Subtraction), expression, alias : alias);
case ExpressionType.Coalesce:
{
if (QueryHelper.UnwrapExpression(r, checkNullability: true) is SqlFunction c)
{
if (c.Name is "Coalesce" or PseudoFunctions.COALESCE)
{
var parms = new ISqlExpression[c.Parameters.Length + 1];

parms[0] = l;
c.Parameters.CopyTo(parms, 1);

return CreatePlaceholder(context, PseudoFunctions.MakeCoalesce(t, parms), expression, alias : alias);
}
}

return CreatePlaceholder(context, PseudoFunctions.MakeCoalesce(t, l, r), expression, alias : alias);
}
case ExpressionType.Coalesce: return CreatePlaceholder(context, new SqlCoalesceExpression(l, r), expression, alias : alias);
}

break;
Expand Down Expand Up @@ -2350,7 +2334,8 @@ Expression GenerateConstructorComparison(SqlGenericConstructorExpression leftCon
}
}

// Force nullability
// Force nullability.
// TODO: Review. Actually it can be removed. Currently only strange NullableBoolTests.NullTest test fails.
if (QueryHelper.IsNullValue(lOriginal))
{
rOriginal = SqlNullabilityExpression.ApplyNullability(rOriginal, true);
Expand Down
87 changes: 25 additions & 62 deletions Source/LinqToDB/Linq/Builder/OrderByBuilder.cs
View file
Original file line number Diff line number Diff line change
@@ -1,5 +1,4 @@
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Linq.Expressions;
Expand Down Expand Up @@ -42,80 +41,44 @@ protected override BuildSequenceResult BuildMethodCall(ExpressionBuilder builder

var sequence = sequenceResult.BuildContext;

var wrapped = false;

if (sequence.SelectQuery.Select.HasModifier)
{
sequence = new SubQueryContext(sequence);
wrapped = true;
}

var orderByProjectFlags = ProjectFlags.SQL | ProjectFlags.Keys;
var isContinuousOrder = !sequence.SelectQuery.OrderBy.IsEmpty && methodCall.Method.Name.StartsWith("Then");
var lambda = (LambdaExpression)methodCall.Arguments[1].Unwrap();

var byIndex = false;

List<SqlPlaceholderExpression> placeholders;
while (true)
if (!isContinuousOrder)
{
Expression sqlExpr;

var body = SequenceHelper.PrepareBody(lambda, sequence).Unwrap();

if (body is MethodCallExpression mc && mc.Method.DeclaringType == typeof(Sql) && mc.Method.Name == nameof(Sql.Ordinal))
{
sqlExpr = builder.ConvertToSqlExpr(sequence, mc.Arguments[0], orderByProjectFlags);
byIndex = true;
}
else
{
sqlExpr = builder.ConvertToSqlExpr(sequence, body, orderByProjectFlags);
byIndex = false;
}

if (!SequenceHelper.IsSqlReady(sqlExpr))
{
if (sqlExpr is SqlErrorExpression errorExpr)
return BuildSequenceResult.Error(methodCall, errorExpr.Message);
return BuildSequenceResult.Error(methodCall);
}

placeholders = ExpressionBuilder.CollectDistinctPlaceholders(sqlExpr);
if (!builder.DataContext.Options.LinqOptions.DoNotClearOrderBys && !sequence.SelectQuery.Select.HasModifier)
sequence.SelectQuery.OrderBy.Items.Clear();

// Do not create subquery for ThenByExtensions
//
if (wrapped || isContinuousOrder)
break;
if (sequence is not SubQueryContext)
sequence = new SubQueryContext(sequence);
}

// handle situation when order by uses complex field
//
var isComplex = false;
Expression sqlExpr;

foreach (var placeholder in placeholders)
{
// immutable expressions will be removed later
//
var isImmutable = QueryHelper.IsConstant(placeholder.Sql);
if (isImmutable)
continue;
var body = SequenceHelper.PrepareBody(lambda, sequence).Unwrap();

// possible we have to extend this list
//
isComplex = null != placeholder.Sql.Find(e => e.ElementType == QueryElementType.SqlQuery || e.ElementType == QueryElementType.SqlFunction);
if (isComplex)
break;
}
bool byIndex;

if (!isComplex)
break;
if (body is MethodCallExpression mc && mc.Method.DeclaringType == typeof(Sql) && mc.Method.Name == nameof(Sql.Ordinal))
{
sqlExpr = builder.ConvertToSqlExpr(sequence, mc.Arguments[0], orderByProjectFlags);
byIndex = true;
}
else
{
sqlExpr = builder.ConvertToSqlExpr(sequence, body, orderByProjectFlags);
byIndex = false;
}

sequence = new SubQueryContext(sequence);
wrapped = true;
if (!SequenceHelper.IsSqlReady(sqlExpr))
{
if (sqlExpr is SqlErrorExpression errorExpr)
return BuildSequenceResult.Error(methodCall, errorExpr.Message);
return BuildSequenceResult.Error(methodCall);
}

if (!isContinuousOrder && !builder.DataContext.Options.LinqOptions.DoNotClearOrderBys)
sequence.SelectQuery.OrderBy.Items.Clear();
var placeholders = ExpressionBuilder.CollectDistinctPlaceholders(sqlExpr);

foreach (var placeholder in placeholders)
{
Expand Down
17 changes: 17 additions & 0 deletions Source/LinqToDB/Remote/LinqServiceSerializer.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -1688,6 +1688,14 @@ void Visit(IQueryElement e, EvaluationContext evaluationContext)
break;
}

case QueryElementType.SqlCoalesce:
{
var elem = (SqlCoalesceExpression)e;

Append(elem.Expressions);
break;
}

default:
throw new InvalidOperationException($"Serialize not implemented for element {e.ElementType}");
}
Expand Down Expand Up @@ -2769,6 +2777,15 @@ bool Parse()
break;
}

case QueryElementType.SqlCoalesce:
{
var expressions = ReadArray<ISqlExpression>()!;

obj = new SqlCoalesceExpression(expressions);

break;
}

default:
throw new InvalidOperationException($"Parse not implemented for element {(QueryElementType)type}");
}
Expand Down
6 changes: 6 additions & 0 deletions Source/LinqToDB/SqlProvider/BasicSqlOptimizer.cs
View file
Original file line number Diff line number Diff line change
Expand Up @@ -1866,6 +1866,12 @@ protected IQueryElement OptimizeQueries(IQueryElement startFrom, IQueryElement r

#if DEBUG
// ReSharper disable once NotAccessedVariable

if (startFrom is SqlSelectStatement)
{

}

var sqlText = startFrom.DebugText;

if (startFrom is SqlStatement statementBefore)
Expand Down
Loading

0 comments on commit 0bdc5ed

Please sign in to comment.