Skip to content
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

Closed
firebird-automations opened this issue Mar 13, 2020 · 14 comments

Comments

@firebird-automations
Copy link

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"

@firebird-automations
Copy link
Author

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.
2> That's not possible, because then the value is considered CHAR by Firebird and shit happens.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

status: Open [ 1 ] => In Progress [ 3 ]

@firebird-automations
Copy link
Author

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. ;-)
Would it make a difference if FbParameters (captured variables in EF queries) are used without any cast?
SELECT @_linq_a1 AS "A1" FROM "ARTICLE"

@firebird-automations
Copy link
Author

Commented by: @cincuranet

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.

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Commented by: @cincuranet

What do you mean by "computed properties"?

@firebird-automations
Copy link
Author

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
.Customers
.Select(c => new
{
FullName = c.FirstName + " " + c.LastName,
Address = c.Street + ", " + c.Zip + " " + c.City,
Age = (DateTime.Now - c.BirthDate).TotalYears,
})

@firebird-automations
Copy link
Author

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.

@firebird-automations
Copy link
Author

Modified by: @cincuranet

Link: This issue is duplicated by DNET980 [ DNET980 ]

@firebird-automations
Copy link
Author

Commented by: Andrey Belov (scratchcat)

I faced the same issue on EF Core.
Where can I find this option (disable/enable the casts)? Or is it not yet in the release version?

@firebird-automations
Copy link
Author

Commented by: @cincuranet

For EF Core you have it in FbDbContextOptionsBuilder.

@firebird-automations
Copy link
Author

Commented by: Valdir Stiebe Junior (ogecrom)

For us the following configuration solved this issue on EF Core.
We didn't get any side effects, so far.

```
optionsBuilder
.UseFirebird(connectionString, providerOptions => providerOptions
.WithExplicitStringLiteralTypes(false)
.WithExplicitParameterTypes(true)
);
```

@firebird-automations
Copy link
Author

Commented by: Andrey Belov (scratchcat)

Thank you very much. I really appreciate your help.

@cincuranet
Copy link
Member

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants