New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Cast of string values to VARCHAR(8191) results in "Implementation limit exceeded" [DNET934] #857
Comments
Commented by: @cincuranet 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. |
Modified by: @cincuranetstatus: Open [ 1 ] => In Progress [ 3 ] |
Commented by: Daniel Richter (drichter) 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. ;-) |
Commented by: @cincuranet 1> Yes, that's more or less the case (at least what's said in comments). |
Commented by: Daniel Richter (drichter) 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. |
Commented by: @cincuranet What do you mean by "computed properties"? |
Commented by: Daniel Richter (drichter) > What do you mean by "computed properties"? Computing new propiertes in e.g. Select methods. For example var result = dataContext |
Commented by: @cincuranet 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. |
Modified by: @cincuranet |
Commented by: Andrey Belov (scratchcat) I faced the same issue on EF Core. |
Commented by: @cincuranet For EF Core you have it in FbDbContextOptionsBuilder. |
Commented by: Valdir Stiebe Junior (ogecrom) For us the following configuration solved this issue on EF Core. ``` |
Commented by: Andrey Belov (scratchcat) Thank you very much. I really appreciate your help. |
EF6 provider here is in maintenance/legacy mode here (see https://groups.google.com/g/firebird-net-provider/c/_0Hwoh5XG_I). If somebody feels like tackling this with good PR, it will be reopened. |
Submitted by: Daniel Richter (drichter)
Is duplicated by DNET980
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"
The text was updated successfully, but these errors were encountered: