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

Wrong generate SQL statement for the stored procedure [DNET723] #232

Closed
firebird-automations opened this issue Nov 24, 2016 · 6 comments
Closed

Comments

@firebird-automations
Copy link

Submitted by: Denis Maslov (denmpei)

Votes: 3

Good day!

When I try execute simple procedure over EF, i got the next error: "FirebirdSql.Data.FirebirdClient.FbException: Dynamic SQL Error SQL error code = -204 Implementation limit exceeded ..."
In the end, I found that the problem in the wrong EF generation sql statement.

Text procedure (Firebrd):
procedure B_IMP_I_REESTR (
IDUSER integer,
IMPMODE integer,
IDGEN integer,
IDPARENT integer,
IDTCH integer,
NOMER varchar(15),
SBORNIK integer,
NAME varchar(250),
VIX_DAN blob sub_type 0 segment size 80,
ISSYSTEM integer,
POKAZ varchar(8),
GOD integer,
RABMAT integer,
IDGROUP integer,
IDPODGROUP integer,
IDTYPE integer,
ORDERVIEW integer,
CREATEDATE date,
AUTHOR varchar(250),
NORM_GUID varchar(100),
INSTALL_GUID varchar(100),
INDEX_YEAR integer,
INDEX_MONTH integer,
INDEX_ORGNAME varchar(250),
INDTYPE varchar(50))
returns (
ID integer)
...

Text procedure (EF generate):
SELECT
1 AS "C1",
"A"."ID" AS "ID"
FROM "B_IMP_I_REESTR"(CAST(@IDUSER AS INT), CAST(@IMPMODE AS INT), CAST(@idgen AS INT), CAST(@IDPARENT AS INT), CAST(@IDTCH AS INT), CAST(@nomer AS VARCHAR(8191)), CAST(@sbornik AS INT), CAST(@name AS VARCHAR(8191)), CAST(@VIX_DAN AS BLOB SUB_TYPE BINARY), CAST(@issystem AS INT), CAST(@pokaz AS VARCHAR(8191)), CAST(@god AS INT), CAST(@rabmat AS INT), CAST(@IDGroup AS INT), CAST(@IDPODGROUP AS INT), CAST(@idtype AS INT), CAST(@OrderView AS INT), CAST(@Createdate AS TIMESTAMP), CAST(@author AS VARCHAR(8191)), CAST(@NORM_GUID AS VARCHAR(8191)), CAST(@INSTALL_GUID AS VARCHAR(8191)), CAST(@INDEX_YEAR AS INT), CAST(@INDEX_MONTH AS INT), CAST(@INDEX_ORGNAME AS VARCHAR(8191)), CAST(@INDTYPE AS VARCHAR(8191))) AS "A"

For example, why INDTYPE varchar(50) -> CAST(@INDTYPE AS VARCHAR(8191)) ???

Regards, Maslov Denis.

@firebird-automations
Copy link
Author

Commented by: Denis Maslov (denmpei)

In this case, for all parameters total size of course exceeds 64 K...

@firebird-automations
Copy link
Author

Commented by: Jeldrik Hornschild (jeldrik)

I'm having the same problem with an "Include" query - something like foreach (var serverGroup in ctx.ServerGroup.Include("Server")) {}:

SELECT
"B"."C1" AS "C1",
"B"."C2" AS "C2",
"B"."ID" AS "ID",
"B"."DISPLAY_TEXT" AS "DISPLAY_TEXT",
"B"."AGGREGATION_INTERVAL" AS "AGGREGATION_INTERVAL",
"B"."SYNC_INTERVAL" AS "SYNC_INTERVAL",
"B"."C3" AS "C3",
"B"."C5" AS "C4",
"B"."C4" AS "C5",
"B"."ID1" AS "ID1",
"B"."SERVER_GROUP_ID" AS "SERVER_GROUP_ID",
"B"."DISPLAY_TEXT1" AS "DISPLAY_TEXT1",
"B"."ADDRESS" AS "ADDRESS",
"B"."PORT" AS "PORT",
"B"."LAST_AGGREGATION" AS "LAST_AGGREGATION"
FROM ( SELECT
"D"."ID" AS "ID",
"D"."DISPLAY_TEXT" AS "DISPLAY_TEXT",
"D"."AGGREGATION_INTERVAL" AS "AGGREGATION_INTERVAL",
"D"."SYNC_INTERVAL" AS "SYNC_INTERVAL",
1 AS "C1",
CAST(_UTF8'dcd91403-9e2f-429e-ae61-c38e6194371e' AS VARCHAR(8191)) AS "C2",
CAST(_UTF8'Server' AS VARCHAR(8191)) AS "C3",
"E"."ID" AS "ID1",
"E"."SERVER_GROUP_ID" AS "SERVER_GROUP_ID",
"E"."DISPLAY_TEXT" AS "DISPLAY_TEXT1",
"E"."ADDRESS" AS "ADDRESS",
"E"."PORT" AS "PORT",
"E"."LAST_AGGREGATION" AS "LAST_AGGREGATION",
CASE WHEN ("E"."ID" IS NULL) THEN NULL ELSE CAST(_UTF8'dcd91403-9e2f-429e-ae61-c38e6194371e' AS VARCHAR(8191)) END AS "C4",
CASE WHEN ("E"."ID" IS NULL) THEN NULL ELSE 1 END AS "C5"
FROM "SERVER_GROUP" AS "D"
LEFT OUTER JOIN "SERVER" AS "E" ON "D"."ID" = "E"."SERVER_GROUP_ID"
) AS "B"
ORDER BY "B"."ID" ASC, "B"."C5" ASC

DDL:

CREATE TABLE "SERVER"
(
"ID" BIGINT NOT NULL PRIMARY KEY,
"SERVER_GROUP_ID" BIGINT NOT NULL,
"DISPLAY_TEXT" VARCHAR(255),
"ADDRESS" VARCHAR(255) NOT NULL,
"PORT" SMALLINT,
"LAST_AGGREGATION" TIMESTAMP
)
;

CREATE TABLE "SERVER_GROUP"
(
"ID" BIGINT NOT NULL PRIMARY KEY,
"DISPLAY_TEXT" VARCHAR(255),
"AGGREGATION_INTERVAL" SMALLINT,
"SYNC_INTERVAL" SMALLINT
)
;

ALTER TABLE "SERVER" ADD CONSTRAINT "FK_S_SG" FOREIGN KEY ("SERVER_GROUP_ID") REFERENCES "SERVER_GROUP" ("ID") ON DELETE CASCADE;

I think this problem is not new. I used to work around this issue by changing UnicodeVarcharMaxSize (FbProviderManifest.cs:52) to a lower value than in the original source. This is just a hack (I dont' know how to fix this right). As a side effect I can't use the nuget packages - I always have to patch the original source code :-(

@firebird-automations
Copy link
Author

Commented by: Vladimir (vrastrigin)

Thank you for your answer! This quick hack can help to execute this particular procedure, but it wasn't enough. After some digging into the source code and trying to cast actual sizes of input paramters, i found a simpler solution. I think, that there is no need to cast implicitly input parameters of procedure, because firebird is able to determine them by itself

@firebird-automations
Copy link
Author

Commented by: Vladimir (vrastrigin)

I suggest the following:
in FirebirdSql.Data.EntityFramework6.SqlGen.cs
public override ISqlFragment Visit(DbParameterReferenceExpression e)
{
_shouldCastParameter = false;
...
_shouldCastParameter = true;
return result;
}

@firebird-automations
Copy link
Author

Commented by: Jeldrik Hornschild (jeldrik)

as far as I can see this wouldn't help in my scenario:

1. public override ISqlFragment Visit(DbConstantExpression e) (SqlGenerator L:614 -- Query parameter C2)
.......

                case PrimitiveTypeKind\.String:
                    var isUnicode = MetadataHelpers\.GetFacetValueOrDefault<bool\>\(e\.ResultType, MetadataHelpers\.UnicodeFacetName, true\);
                    // constant is always considered Unicode
                    isUnicode = true;
                    var length = MetadataHelpers\.GetFacetValueOrDefault<int?\>\(e\.ResultType, MetadataHelpers\.MaxLengthFacetName, null\)
                        ?? \(isUnicode ? FbProviderManifest\.UnicodeVarcharMaxSize : FbProviderManifest\.AsciiVarcharMaxSize\);
                    result\.Append\(FormatString\(\(string\)e\.Value, isUnicode, length\)\);
                    break;

2. internal static string FormatString(string value, bool isUnicode, int? explicitLength = null) (SqlGenerator L:3176 - Parameter explicitLenth = 8191 since MaxLength Facet is null)
{
var result = new StringBuilder();
result.Append("CAST(");
if (isUnicode)
{
result.Append("_UTF8");
}
result.Append("'");
result.Append(value.Replace("'", "''"));
result.Append("' AS VARCHAR(");
result.Append(explicitLength ?? value.Length);
result.Append("))");
return result.ToString();
}

3. Generated Query:

SELECT FIRST (CAST(@p__linq__0 AS INT))
"B"."C1" AS "C1",
"B"."C2" AS "C2",
"B"."C3" AS "C3",
"B"."USERNAME" AS "USERNAME",
"B"."C4" AS "C4",
"B"."ID" AS "ID"
FROM ( SELECT
"C"."ID" AS "ID",
"C"."USERNAME" AS "USERNAME",
"C"."CREATED" AS "CREATED",
1 AS "C1",
CAST(_UTF8'f5b1af22-6aac-46a6-b553-b857b237f03d' AS VARCHAR(8191)) AS "C2",
CAST(_UTF8'Username' AS VARCHAR(8191)) AS "C3",
CAST(_UTF8'ID' AS VARCHAR(8191)) AS "C4"
FROM "USER" AS "C"
) AS "B"
ORDER BY "B"."CREATED" DESC, "B"."ID" ASC
Parameters:
Name:p__linq__0 Type:Integer Used Value:100

--> Exception:

Dynamic SQL Error
SQL error code = -204
Implementation limit exceeded
block size exceeds implementation restriction

i can't see that the parameter "_shouldCastParameter" is involved here anywhere :-(

BTW: this was tested with odata/restier Framework using a query like this:

GET http://localhost:9000/OData/User?$select=ID,Username&$top=100&$orderby=Created%20desc

The Problem habpens as soon as you use $expand or $orderby in a odata query. Not able using these core features leaves odata/firebird pretty useless right now :-(

@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