We are migrating from 2.6 to 5.9 (later to 5.11). The truncation detection introduced in 5.3 broke some queries.

The detection is automatically assuming the comparison is equality and replaces the predicate with 1=0 however that is not the case if the condition is excluding data. E.g.

from c in db.Currency
where c.Varchar3Column != "EURO"
select c

As a result you would expect all records because EURO isn't possible in varchar(3) column. But instead we get nothing.

We have various queries where the input is provided by the user and it is very hard to find these queries.

Is this a bug nobody reported? Is there an option to disable this behavior?

It's not a bug, we intentionally added this, because previously c.Varchar3Column == 'EURO' would resolve to true when the c.Varchar3Column was 'EUR', as we set the length of the parameter to the length of the field, which would truncate the value passed in as value for the parameter to the length specified (in this case the parameter value would become 'EUR'). To prevent this we do an additional check to see if this is happening and if so, we transform the predicate to a predicate that always fails.

The check is located in FieldCompareValuePredicate, line 297. If you don't want this check you have to modify the runtime sourcecode (which is available on the website -> MyAccount -> Downloads -> Version -> Extras section. ) as we didn't add a flag for this as we fixed unintended behavior.

I was looking at this problem more and found another interesting scenarios.

For example this:

var testA = from c in db.Currency
            where c.Varchar3Column != "EURO" && c.Varchar3Column != "EUR"
            select c

var testB = from c in db.Currency
            where !(c.Varchar3Column == "EURO" || c.Varchar3Column == "EUR")
            select c

You would expect testA.Count() == testB.Count() because the logic is same just written in a reverted boolean logic however because of the gap in the truncation detection testA is zero and testB is correct. The truncation detection should translate != operator into 1=1 instead of 1=0. You could also think about what < <= => > should do to get correct results.

And even more - Contains is actually still broken - the truncation detection is missing.

var arr = new [] {  "EURO" };

from c in db.Currency
where arr.Contains(c.Varchar3Column)
select c

Results in:

exec sp_executesql N'
    SELECT TOP(@p2) COUNT(*) AS [LPAV_] 
    FROM [dbo].[CURRENCY] [LPLA_1] 
    WHERE ( ( ( [LPLA_1].[VARCHAR3_COLUMN] IN (@p3))))',
    N'@p2 bigint,@p3 varchar(3)',
    @p2=1,
    @p3='EUR'

And obviously also this one is going to be a problem if you implement the truncation detection without dealing with the negation because the situation becomes a lot more interesting if arr contains a mix of correct and incorrect values:

var arr = new [] {  "EURO", "EUR", "USD" };

from c in db.Currency
where !arr.Contains(c.Varchar3Column)
select c

I cannot think about a good fix for Contains but I think != should become 1=1.

MMStan wrote:

I was looking at this problem more and found another interesting scenarios.

For example this:

var testA = from c in db.Currency
            where c.Varchar3Column != "EURO" && c.Varchar3Column != "EUR"
            select c

var testB = from c in db.Currency
            where !(c.Varchar3Column == "EURO" || c.Varchar3Column == "EUR")
            select c

You would expect testA.Count() == testB.Count() because the logic is same just written in a reverted boolean logic however because of the gap in the truncation detection testA is zero and testB is correct. The truncation detection should translate != operator into 1=1 instead of 1=0. You could also think about what < <= => > should do to get correct results.

In theory that is true, in practice it would require us parsing the predicate how it will turn out and we didn't do that as the expression can be very complex. We don't include code that would 'interpret' what the RDBMS would do (which will always be limited/out of date etc.). This change was purely for the unforeseen consequence that the code would send a truncated parameter to the database, leading to unwanted side effects. We added the comment so people could adjust their code to avoid the call with the wrong input in the first place.

And even more - Contains is actually still broken - the truncation detection is missing.

var arr = new [] {  "EURO" };

from c in db.Currency
where arr.Contains(c.Varchar3Column)
select c

Results in:

exec sp_executesql N'
    SELECT TOP(@p2) COUNT(*) AS [LPAV_] 
    FROM [dbo].[CURRENCY] [LPLA_1] 
    WHERE ( ( ( [LPLA_1].[VARCHAR3_COLUMN] IN (@p3))))',
    N'@p2 bigint,@p3 varchar(3)',
    @p2=1,
    @p3='EUR'

And obviously also this one is going to be a problem if you implement the truncation detection without dealing with the negation because the situation becomes a lot more interesting if arr contains a mix of correct and incorrect values:

var arr = new [] {  "EURO", "EUR", "USD" };

from c in db.Currency
where !arr.Contains(c.Varchar3Column)
select c

I cannot think about a good fix for Contains but I think != should become 1=1.

The core of the issue is really: if the input is wrong, who should fix it: us or you. I think looking back we should have thrown an exception but at the time we decided that might lead to angry faces so we made it less intrusive. Another alternative would have been to make the parameter take the size of the value (which would lead to the right results here). Then the issue becomes what to do if the input is > 8KB, or in case of utf16 text 4KB... Other than the fact we then have to introduce breaking changes for generating parameters, for a situation that is up to the developer to fix.