This is a regression when upgrading from 4.1.1 to 4.2.
We're doing a big, although relatively simple query that select many aggregates and then combine them a little bit.
I'm not sure what is triggering the bug, so here's the whole thing in C#:
private static readonly TransferStatus[] rejectedStatuses = new[] { TransferStatus.Rejected, TransferStatus.Refused };
private static readonly TransferStatus[] validatedStatuses = new[] { TransferStatus.Pending, TransferStatus.Validated, TransferStatus.Sent, TransferStatus.Executed };
var q = from r in MyRemittances
where r.ValueDate >= DateTime.Today.AddDays(-7)
from c in linqDb.Counterparties.LeftJoin(c => r.CounterpartyId == c.Id)
join tc in linqDb.TreasuryCenters on r.TreasuryCenterId equals tc.Id
from t in from t in linqDb.GetTable<TTransfer>()
where t.RemittanceId == r.Id
&& t.Status != TransferStatus.Deleted
select new
{
RejectedAmount = Sql.Ext.Sum(rejectedStatuses.Contains(t.Status)
? t.Amount : (decimal?)null).ToValue(),
RejectedCount = Sql.Ext.Sum(rejectedStatuses.Contains(t.Status)
? 1 : 0).ToValue(),
ToSubmitAmount = Sql.Ext.Sum(t.Status == TransferStatus.Draft
&& (t.Alerts & Alerts.DuplicateMask) == 0
? t.Amount : (decimal?)null).ToValue(),
ToSubmitCount = Sql.Ext.Sum(t.Status == TransferStatus.Draft
&& (t.Alerts & Alerts.DuplicateMask) == 0
? 1 : 0).ToValue(),
ToValidateAmount = Sql.Ext.Sum(t.Status == TransferStatus.Submitted
&& t.Validator1Id == null
&& (t.Alerts & Alerts.DuplicateMask) == 0
? t.Amount : (decimal?)null).ToValue(),
ToValidateCount = Sql.Ext.Sum(t.Status == TransferStatus.Submitted
&& t.Validator1Id == null
&& (t.Alerts & Alerts.DuplicateMask) == 0
? 1 : 0).ToValue(),
PreValidatedAmount = Sql.Ext.Sum(t.Status == TransferStatus.Submitted
&& t.Validator1Id != null
? t.Amount : (decimal?)null).ToValue(),
PreValidatedCount = Sql.Ext.Sum(t.Status == TransferStatus.Submitted
&& t.Validator1Id != null
? 1 : 0).ToValue(),
ValidatedAmount = Sql.Ext.Sum(validatedStatuses.Contains(t.Status)
? t.Amount : (decimal?)null).ToValue(),
ValidatedCount = Sql.Ext.Sum(validatedStatuses.Contains(t.Status)
? 1 : 0).ToValue(),
DupToSubmitAmount = Sql.Ext.Sum(t.Status == TransferStatus.Draft
&& (t.Alerts & Alerts.DuplicateMask) != 0
? t.Amount : (decimal?)null).ToValue(),
DupToSubmitCount = Sql.Ext.Sum(t.Status == TransferStatus.Draft
&& (t.Alerts & Alerts.DuplicateMask) != 0
? 1 : 0).ToValue(),
DupToValidateAmount = Sql.Ext.Sum(t.Status == TransferStatus.Submitted
&& (t.Alerts & Alerts.DuplicateMask) != 0
? t.Amount : (decimal?)null).ToValue(),
DupToValidateCount = Sql.Ext.Sum(t.Status == TransferStatus.Submitted
&& (t.Alerts & Alerts.DuplicateMask) != 0
? 1 : 0).ToValue(),
ExecutedAmount = Sql.Ext.Sum(t.Status >= TransferStatus.Validated
? t.Amount : (decimal?)null).ToValue(),
ExecutedCount = Sql.Ext.Sum(t.Status >= TransferStatus.Validated
? 1 : 0).ToValue(),
Alerts = OracleSql.BitOrAgg(t.Alerts).ToValue(),
}
from i in from i in linqDb.GetTable<TInvalid>()
where i.RemittanceId == r.Id
select new
{
Count = Sql.Ext.Count(1).ToValue(),
Total = (decimal?)Sql.Ext.Sum(i.Amount).ToValue(),
}
select new RemittanceDto
{
Id = r.Id,
DebitedAccount = r.DebitedAccount,
ValueDate = r.ValueDate,
ValueDateOrigin = r.ValueDateOrigin,
CreationDate = r.CreationDate,
CurrencyId = r.CurrencyId,
Counterparty = c.ShortName,
Type = r.TransferType,
TotalCount = r.TotalCount,
TotalAmount = r.TotalAmount,
AbnormalAmount = (t.RejectedAmount ?? 0m) + (t.DupToSubmitAmount ?? 0m) + (t.DupToValidateAmount ?? 0m) + (i.Total ?? 0m),
AbnormalCount = t.RejectedCount + t.DupToSubmitCount + t.DupToValidateCount + i.Count,
ToSubmitAmount = t.ToSubmitAmount,
ToSubmitCount = t.ToSubmitCount,
ToValidateAmount = t.ToValidateAmount,
ToValidateCount = t.ToValidateCount,
PreValidatedAmount = t.PreValidatedAmount,
PreValidatedCount = t.PreValidatedCount,
ValidatedAmount = t.ValidatedAmount,
ValidatedCount = t.ValidatedCount,
DupToSubmitAmount = t.DupToSubmitAmount,
DupToSubmitCount = t.DupToSubmitCount,
DupToValidateAmount = t.DupToValidateAmount,
DupToValidateCount = t.DupToValidateCount,
ExecutedAmount = t.ExecutedAmount,
ExecutedCount = t.ExecutedCount,
ErrorCount = i.Count,
ErrorAmount = i.Total,
Alerts = t.Alerts | r.Alerts,
ExternalReference = r.ExternalReference,
};Here's the generated SQL, also in its entirety:
SELECT
r.Id,
r.DebitedAccount,
r.ValueDate,
r.ValueDateOrigin,
r.CreationDate,
r.CurrencyId,
c_1.ShortName as Counterparty,
r.TransferType as Type,
r.TotalCount,
r.TotalAmount,
Nvl(t1.RejectedAmount, 0),
Nvl(t1.DupToSubmitAmount, 0),
Nvl(t1.DupToValidateAmount, 0),
Nvl(t2.Total, 0),
t1.RejectedCount as AbnormalCount,
t1.DupToSubmitCount as AbnormalCount,
t1.DupToValidateCount as AbnormalCount,
t2.Count_1 as AbnormalCount,
t1.ToSubmitAmount,
t1.ToSubmitCount,
t1.ToValidateAmount,
t1.ToValidateCount,
t1.PreValidatedAmount,
t1.PreValidatedCount,
t1.RejectedAmount as ValidatedAmount,
t1.DupToSubmitAmount,
t1.DupToValidateAmount,
t1.ExecutedAmount,
t1.ExecutedCount,
t2.Total as ErrorAmount,
t1.Alerts,
r.Alerts,
r.ExternalReference
FROM
Remittance r
LEFT JOIN Counterparty c_1 ON r.CounterpartyId = c_1.Id
INNER JOIN TreasuryCenter tc ON r.TreasuryCenterId = tc.Id
CROSS APPLY (
SELECT
SUM(CASE
WHEN t.Status IN (2, 9) THEN t.Amount
ELSE NULL
END) as RejectedAmount,
SUM(CASE
WHEN t.Status = 1 AND BITAND(t.Alerts, 320) <> 0
THEN t.Amount
ELSE NULL
END) as DupToSubmitAmount,
SUM(CASE
WHEN t.Status = 4 AND BITAND(t.Alerts, 320) <> 0
THEN t.Amount
ELSE NULL
END) as DupToValidateAmount,
SUM(CASE
WHEN t.Status IN (2, 9) THEN 1
ELSE 0
END) as RejectedCount,
SUM(CASE
WHEN t.Status = 1 AND BITAND(t.Alerts, 320) <> 0
THEN 1
ELSE 0
END) as DupToSubmitCount,
SUM(CASE
WHEN t.Status = 4 AND BITAND(t.Alerts, 320) <> 0
THEN 1
ELSE 0
END) as DupToValidateCount,
SUM(CASE
WHEN t.Status = 1 AND BITAND(t.Alerts, 320) = 0
THEN t.Amount
ELSE NULL
END) as ToSubmitAmount,
SUM(CASE
WHEN t.Status = 1 AND BITAND(t.Alerts, 320) = 0
THEN 1
ELSE 0
END) as ToSubmitCount,
SUM(CASE
WHEN t.Status = 4 AND t.Validator1Id IS NULL AND BITAND(t.Alerts, 320) = 0
THEN t.Amount
ELSE NULL
END) as ToValidateAmount,
SUM(CASE
WHEN t.Status = 4 AND t.Validator1Id IS NULL AND BITAND(t.Alerts, 320) = 0
THEN 1
ELSE 0
END) as ToValidateCount,
SUM(CASE
WHEN t.Status = 4 AND t.Validator1Id IS NOT NULL
THEN t.Amount
ELSE NULL
END) as PreValidatedAmount,
SUM(CASE
WHEN t.Status = 4 AND t.Validator1Id IS NOT NULL
THEN 1
ELSE 0
END) as PreValidatedCount,
SUM(CASE
WHEN t.Status >= 6 THEN t.Amount
ELSE NULL
END) as ExecutedAmount,
SUM(CASE
WHEN t.Status >= 6 THEN 1
ELSE 0
END) as ExecutedCount,
bit_or_agg(t.Alerts) as Alerts
FROM
TRANSFER t
WHERE
t.RemittanceId = r.Id AND t.Status <> 3
) t1
CROSS APPLY (
SELECT
SUM(i.Amount) as Total,
COUNT(1) as Count_1
FROM
InvalidTransfer i
WHERE
i.RemittanceId = r.Id
) t2
WHERE
r.TransferType IN (8, 4, 1, 128)
ORDER BY
r.CreationDate DESC,
r.Id
Now here's the bug: try to find ValidatedAmount:
- It is nowhere to be found in the inner query.
- In the outer SELECT,
RejectedAmount is renamed into ValidatedAmount, which is what I indeed observe in the results. This is of course, totally incorrect.
Environment details
Linq To DB version: 4.2.0
Database (with version): Oracle 19
ADO.NET Provider (with version): ODP.NET Core 3.21.70
Operating system: Win 10
.NET Version: 6.0
This is a regression when upgrading from 4.1.1 to 4.2.
We're doing a big, although relatively simple query that select many aggregates and then combine them a little bit.
I'm not sure what is triggering the bug, so here's the whole thing in C#:
Here's the generated SQL, also in its entirety:
Now here's the bug: try to find
ValidatedAmount:RejectedAmountis renamed intoValidatedAmount, which is what I indeed observe in the results. This is of course, totally incorrect.Environment details
Linq To DBversion: 4.2.0Database (with version): Oracle 19
ADO.NET Provider (with version): ODP.NET Core 3.21.70
Operating system: Win 10
.NET Version: 6.0