Issue Details (XML | Word | Printable)

Key: DNET-723
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Jiri Cincura
Reporter: Denis Maslov
Votes: 3
Watchers: 3
Operations

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

Wrong generate SQL statement for the stored procedure

Created: 24/Nov/16 10:57 AM   Updated: 20/Jan/17 04:04 PM
Component/s: Entity Framework
Affects Version/s: 5.6.0.0
Fix Version/s: None

Environment: Firebird 2.5.2, Firebird Entity Framework Provider - version 5.6.0, Firebird ADO.NET Data Povider - version 5.6.0, Entity Framework - version 6.1.3


 Description  « Hide
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.

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Denis Maslov added a comment - 24/Nov/16 11:08 AM - edited
In this case, for all parameters total size of course exceeds 64 K...

Jeldrik Hornschild added a comment - 27/Nov/16 10:53 AM - edited
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 :-(

Vladimir added a comment - 01/Dec/16 10:52 AM
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

Vladimir added a comment - 01/Dec/16 11:11 AM
I suggest the following:
in FirebirdSql.Data.EntityFramework6.SqlGen.cs
public override ISqlFragment Visit(DbParameterReferenceExpression e)
{
  _shouldCastParameter = false;
  ...
 _shouldCastParameter = true;
  return result;
}

Jeldrik Hornschild added a comment - 20/Jan/17 03:21 PM - edited
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 :-(