Hi,

we just noticed something very strange when using Linq to LLBLGen Pro v5.0.6.

Consider the following query:

var linq = new LinqMetaData(adapter);
var q = linq.TTable.Where(TTable ->  TTable.TTable_Column == "SOMESTRING").ToList();

This produces the following T-SQL:

exec sp_executesql N'SELECT [LPA_L1].[TTable_Column],... FROM [Database].[dbo].[TTable]  [LPA_L1]   WHERE ( ( [LPA_L1].[TTable_Column] = @p1))',N'@p1 varchar(3)',@p1='SOM'

As you can see, the string value "SOMESTRING" was replaced by "SOM" in the final query. That's obviously very bad.

After some testing, we noted that the query parameter is always reduced to the size of the column it is compared against. The data type of TTable_Column is varchar(3).

What's going on?! I would assume that this is a bug. Could there be some of our code causing this?

Thanks!

I don't understand why you'd consider this as a bug. Why do you compare a column with a value that's greater in size than what it can hold?

I'd suggest a business or presentation layer validation on the value to compare with.

From a purely conceptual point of view: you query a database table to see if a row matches a criterion. You're not **inserting **values into that database, you're comparing values. The size of two varchars should not matter in that comparison, and I know of no database engine where it does.

More generally: if I compare two values of different data types, this comparison can only happen if there is an implicit conversion from one to the other where no information is lost. So if you compare a varchar(3) to a varchar(10) you convert the varchar(3) operand to varchar(10), and not the other way around.

More practically: I'd expect the Linq to LLBLGen provider to behave the way SQL does. If you do SELECT * FROM TTable WHERE TTable_Column = 'SOMETEXT' then you don't have to worry about the varchar size of TTable_Column.

To me this is as obvious a bug as anything.

Nobody expects this to behave the way it does! I certainly didn't expect the Linq provider to mess with the values I put into the query.

So what result would you expect from this query?

1- As is ("SOM" == "SOMETHING"), so no rows returned

2- "SOM " == "SOMETHING", also no rows would be returned

3- "SOM" == "SOM", matching rows would be returned

4- Exception returned, since "SOMETHING" exceeds the field's length

Sorry, I don't really understand your four options. I would expect the Linq to LLBLGen Query to be translated to the following:

exec sp_executesql N'SELECT [LPA_L1].[TTable_Column],... FROM [Database].[dbo].[TTable] [LPA_L1] WHERE ( ( [LPA_L1].[TTable_Column] = @p1))',N'@p1 varchar(9)',@p1='SOMETHING'

The value passed in the parameter is of the type of the field it's compared against, that's always been the case. Comparing the field to a value that's bigger than the field (so a varchar(3) field compared to "SOMETHING") will never result in a selected value, as there's no row where the value "SOMETHING" is present. This means that the type of the parameter is set to varchar(3). We then set the value of the parameter with the value you specified, "SOMETHING", but SqlClient (and others) truncate the value to the type set, as expected. I mean: the type of the parameter is varchar(3).

acl wrote:

Sorry, I don't really understand your four options. I would expect the Linq to LLBLGen Query to be translated to the following:

exec sp_executesql N'SELECT [LPA_L1].[TTable_Column],... FROM [Database].[dbo].[TTable] [LPA_L1] WHERE ( ( [LPA_L1].[TTable_Column] = @p1))',N'@p1 varchar(3)',@p1='SOMETHING'

That can't happen, 'SOMETHING' doesn't fit as a value in a parameter with type 'varchar(3)'.

We also don't specify a different type than the field compared against, as that will cause conversions in the DB.

I don't know why you compare a field of length 3 with a string that's longer, though if you do that then indeed it might cause unexpected results. There's however little else we can do: it's impossible to send "SOMETHING" as-is to the DB in a varchar(3) typed parameter. This thus requires a parameter of a different length (which is not correct and thus we can't do that) and the only other option is to throw an exception when this happens. Which is not what you'd like either.

Thanks for the explanation. This finally makes sense.

(As a side-note regarding the modified query from my previous post: I indeed forgot to change the parameter type; I corrected this now.)

I still don't get your bewilderment at my comparing a string to a string of different size. As I explained: if you were doing this in pure SQL, you wouldn't even think about it. Also: if I'd do this with a prepared statement in ADO.NET, I would use Parameters.AddWithValue and don't worry about the string length.

But ok, I understand now that you stick to the exact parameter type. Honestly, I would prefer an exception to a truncated string in this case.

acl wrote:

Thanks for the explanation. This finally makes sense.

(As a side-note regarding the modified query from my previous post: I indeed forgot to change the parameter type; I corrected this now.)

I still don't get your bewilderment at my comparing a string to a string of different size. As I explained: if you were doing this in pure SQL, you wouldn't even think about it.

With parameters in sql you mean? You'd get the same thing. Or an error perhaps, haven't checked.

Comparing a varchar(3) field with a string longer than 3 will never result in a row, it will always result in 0 rows. I understand that by the truncation of the value, it's resulting in some rows perhaps as the fragment matches, but it's more of an 'unspecified' behavior: your comparison otherwise would never match any row. I'm not bewildered, I just wondered why one would compare a string much longer than any value in the table column

Also: if I'd do this with a prepared statement in ADO.NET, I would use Parameters.AddWithValue and don't worry about the string length.

But ok, I understand now that you stick to the exact parameter type. Honestly, I would prefer an exception to a truncated string in this case.

AddWithValue will likely set the parameter to the length of the value. That's a bit of a problem tho -> first you'll get a comparison mismatch, which the DB likely will not sweat about (but you will if lengths cross a certain length), but more importantly, every query will be different and it can't likely re-use cached execution plans (as the parameter sizes differ). so the alternative is to send the parameter with length 4000. I think EF did that in its first year and it wasn't a success.

The option for an exception is fair, though I think you'd be posting the same thread here if you'd have gotten an exception "WHy does this throw an exception?", which is a fair question in that context, as you assumed the value would be passed as-is.

It's a leaky abstraction, as you'd be required to know the length of the field in the DB. I'll add a workitem to make it configurable to get an exception in this case (when a parameter value is truncated).

With pure SQL I meant without parameters, as in TTable_Column = 'SOMESTRING'. In a prepared statement I'd use .AddWithValue. In my particular case, these are tables with about 20 rows tops, so I really don't care about execution plan reuse. I do care for readable code however.

The problem in my case is exactly what you call unspecified behavior. Because the 3-letter substring **did **match a row in the target table which lead to some very unexpected phenomena.

This whole thing was in the context of a query that checked several database tables for a user-supplied string. This is what the code looks like:

var linq = new LinqMetaData(adapter);
bool exists = false;
exists = exists || linq.TTable.Where(TTable -> TTable.TTable_Column == input).Any();
exists = exists || linq.TOtherTable.Where(TOtherTable-> TOtherTable.TOtherTable_Column == input).Any();
exists = exists || linq.TYetAnother.Where(TYetAnother-> TYetAnother.TYetAnother_Column == input).Any();

If you want to do this correctly with the current linq to llblgen provider, the code will get verbose & ugly very quickly. Plus I am certain that most of the time, you would simply forget the length check ..

var linq = new LinqMetaData(adapter);
bool exists = false;
exists = exists || (input.Length <= TTableFields.TTable_Column.MaxLength && linq.TTable.Where(TTable -> TTable.TTable_Column == input).Any());
exists = exists || (input.Length <= TOtherTableFields.TOtherTable_Column.MaxLength && linq.TOtherTable.Where(TOtherTable-> TOtherTable.TOtherTable_Column == input).Any());
exists = exists || (input.Length <= TYetAnotherFields.TYetAnother_Column.MaxLength && linq.TTable.Where(TYetAnother-> TYetAnother.TYetAnother_Column == input).Any());

But I didn't come here to argue. I now understand why it behaves the way it does. We can leave it at that and agree on this being a leaky abstraction.

[Edit]

Instead of the exception, I think that you could optimize this when you construct the query. If a varchar column of a certain length is compared to a string that is longer, you could just emit 1=0 or some equivalent always-false constant expression. That would certainly be more correct than the current solution of truncating the string to which you compare. Also, that would plug the abstraction leak, since you wouldn't have to worry about varchar size.

To be clear, we don't truncate the string, SqlParameter does that (see the SqlParameter.Size documentation). If we would truncate the value, then I'd have changed it already. The thing is we don't, we just set the length (as explained above ).

It's indeed a situation where the current behavior isn't desired, and I agree with you that what it would do intuitively would be best. I'm not sure emitting a 1=0 is possible, as the parameter value is set as part of a predicate, but we'll check it out what the options are.

Implemented in v5.3 (Post EAP).

We decided that the only way people will use this anyway is to emit an 1=0 predicate. All other variants with an optional setting won't work, people won't know the setting is even there.

[Test]
public void ParameterValueTruncationTest()
{
    using(var adapter = new DataAccessAdapter())
    {
        var categories = new EntityCollection<CategoryEntity>();
        adapter.FetchEntityCollection(categories, new RelationPredicateBucket(CategoryFields.CategoryName=="AA123456789012TOOLONG"));
        Assert.AreEqual(0, categories.Count);
    }
}

gives:

Generated Sql query: 
    Query: SELECT [Northwind].[dbo].[Categories].[CategoryName], [Northwind].[dbo].[Categories].[Description], [Northwind].[dbo].[Categories].[CategoryID] AS [Id], [Northwind].[dbo].[Categories].[Picture] FROM [Northwind].[dbo].[Categories] WHERE ( 1=0 /* Parameter value truncation, predicate invalidated */)

We emit a comment so it's obvious. We only check for truncation for variable string types, not binary types. fixed length strings don't get value truncation anyway so we don't need to check it there (See DbParameter.Size).

Great news, thanks!