Issue Details (XML | Word | Printable)

Key: DNET-934
Type: Bug Bug
Status: In Progress In Progress
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Daniel Richter
Votes: 0
Watchers: 1
Operations

If you were logged in you would be able to see more operations.
.NET Data provider

Cast of string values to VARCHAR(8191) results in "Implementation limit exceeded"

Created: 13/Mar/20 06:36 PM   Updated: 29/Jun/20 06:53 AM
Component/s: Entity Framework
Affects Version/s: 7.5.0.0
Fix Version/s: None


 Description  « Hide
For string values in queries, the generated SQL results in CASTs to VARCHAR(8191)
   dataContext.Articles.Select(_ => new { A1 = "1A", A2 = "2A" })
generates SQL such as
   SELECT CAST(_UTF8'1A' AS VARCHAR(8191)) AS "A1", CAST(_UTF8'2A' AS VARCHAR(8191)) AS "A2" FROM "ARTICLE"
   
In case the string value is used in multiple* columns, the SQL command execution results in an error "SQL error code = -204, Implementation limit exceeded, block size exceeds implementation restriction".

*) With charset UTF8 the query fails with 2+ columns using string values. The amount of string values used in the column does not seem to affect the outcome (50+ string values in one column still work).
   With charset WIN1252 the query fails with 8+ columns using one string value. Using multiple string values in a column shows different results; in some cases, 8+ string values used in the entire query fails, in some cases not.

It does not matter whether the string value is used inline or as a captured variable (the cast is also inserted for those).

Because of the following code in SqlGenerator.cs, Visit(DbConstantExpression)
   // constant is always considered Unicode
   isUnicode = true;
   var length = MetadataHelpers.GetFacetValueOrDefault<int?>(e.ResultType,MetadataHelpers.MaxLengthFacetName,null)
?? (isUnicode ? FbProviderManifest.UnicodeVarcharMaxSize : FbProviderManifest.AsciiVarcharMaxSize);
The length will result in FbProviderManifest.UnicodeVarcharMaxSize (8191).


Is the CAST to VARCHAR(n) really necessary? The following two solutions will work without problems (so far) for my use-case:

1) Don't pass the explicitLength parameter to FormatString (SqlGenerator.cs), so the length of the string value will be used:
   SELECT CAST(_UTF8'ABC' AS VARCHAR(3)) AS "A1", CAST(_UTF8'DEFGH' AS VARCHAR(5)) AS "A2" FROM "ARTICLE"
   
2) Omit the CAST entirely.
   SELECT _UTF8'ABC' AS "A1", _UTF8'DEFGH' AS "A2" FROM "ARTICLE"

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Jiri Cincura added a comment - 16/Mar/20 11:44 AM
1> I don't have the link here, but I recall some issue when casting to the length of the string with some comparisons. But I fail to recall some details. I'll try to search in tracker.
2> That's not possible, because then the value is considered CHAR by Firebird and shit happens.

Daniel Richter added a comment - 16/Mar/20 12:47 PM
1> There are some explanations, but I don't know to which kind of queries they relate. (https://stackoverflow.com/questions/50136336/, https://stackoverflow.com/questions/40170882/)

2> In the 15+ years usage of the .NET Firebird provider (and another connector to FB) we never used any cast to VARCHAR(x) when string parameters were involved in queries and did not run in any problems ever. But that's purely anecdotal evidence. ;-)
Would it make a difference if FbParameters (captured variables in EF queries) are used without any cast?
   SELECT @_linq_a1 AS "A1" FROM "ARTICLE"

Jiri Cincura added a comment - 16/Mar/20 01:05 PM - edited
1> Yes, that's more or less the case (at least what's said in comments).
2> It would. Sometimes EF generates queries like @p1 = @p2 and that's not going to work in Firebird, because the type needs to be known, hence the explicit cast. In EF Core provider I made an option to disable/enable the casts. Maybe that would be an option here too.

Daniel Richter added a comment - 16/Mar/20 03:45 PM
An option to disable the casts would definitely be great! :-)

At the moment, this issue is is complete showstopper for specific use-case (creating user-defined queries with computed properties), because it is practically impossible to create queries with more than one computed column - at least with the official, unmodified provider.

Jiri Cincura added a comment - 16/Mar/20 04:01 PM
What do you mean by "computed properties"?

Daniel Richter added a comment - 16/Mar/20 04:32 PM
> What do you mean by "computed properties"?

Computing new propiertes in e.g. Select methods. For example

var result = dataContext
   .Customers
   .Select(c => new
   {
      FullName = c.FirstName + " " + c.LastName,
      Address = c.Street + ", " + c.Zip + " " + c.City,
      Age = (DateTime.Now - c.BirthDate).TotalYears,
   })

Jiri Cincura added a comment - 16/Mar/20 04:45 PM
This does not concern me a lot. Mostly because this should/could be done on client side (usually).

But I'll probably make it configurable, as in EF Core.