MySQL Bugs: #45277: Calling User Defined Function using eSql causes NullReferenceException

Bug #45277	Calling User Defined Function using eSql causes NullReferenceException
Submitted:	2 Jun 2009 21:57	Modified:	8 Jan 2010 14:11
Reporter:	Muhammad Mosa	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.3	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	entity framework, Entity SQL, eSql, udf

Description:
Calling UDF using Entity SQL in Entity Framework causes NullReferenceException.

Participant Classes:
*BinaryFragment class this is where exception take place on WriteSql(StringBuilder) method
*FunctionProcessor class

The cause of this exception:
// do left arg
if (WrapLeft)
   sql.Append("(");
   Left.WriteSql(sql); //At this line, Left is null
if (WrapLeft)
   sql.Append(")");

Reason:
Generate method of FunctionProcessor class searchs only for built in functions and returns null if function not found

How to repeat:
due to my limitted time I couldn't wirte a small sample for this but if you wish to get my full project code no problem. it is an open source anyway and contain test cases and I can highlight which case exactly is failing.

For simple repeate do the following
*create a simple T-SQL user defined function mine is called "EFSearchStory"

*Make sure you made correct mapping in SSDL here is mine
<Function Name="EFSearchStory" Aggregate="false"
    BuiltIn="false" NiladicFunction="false" IsComposable="true" 
    ParameterTypeSemantics="AllowImplicitConversion" StoreFunctionName="EFSearchStory"
    Schema="dbo" ReturnType="bit">
    <Parameter Name="storyId" Type="uniqueidentifier" Mode="In" />
    <Parameter Name="query" Type="nvarchar" MaxLength="4000" Mode="In" />
  </Function>
Note that StoreFunctionName is different from Name in this example.

* create an ESql statement and execute it mine is:
"select s.Id as Id from YourContainNamer.Story as s where YourStoreFullNamespace.EFSearchStory(s.Id, @query) = true

Execute your ESQL Statement and it should throw a NullReferenceException in the location I specified in the description

Suggested fix:
This is not a fix, this is a work around I made to make my application run.
The workaround I made is in FunctionProcessor.

public SqlFragment Generate(DbFunctionExpression e, SqlGenerator caller)
        {
            callingGenerator = caller;
            if (bitwiseFunctions.ContainsKey(e.Function.Name))
                return BitwiseFunction(e);

            if (dateFunctions.ContainsKey(e.Function.Name))
                return GenericFunction(dateFunctions, e);
            
            if (stringFunctions.ContainsKey(e.Function.Name))
                return GenericFunction(stringFunctions, e);
            
            if (mathFunctions.ContainsKey(e.Function.Name))
                return GenericFunction(mathFunctions, e);
            
            return UdfFunction(e);
        }

private SqlFragment UdfFunction(DbFunctionExpression e)
        {
            var frags = new SqlFragment[e.Arguments.Count];
            
            for (var i = 0; i < e.Arguments.Count; i++)
                frags[i] = e.Arguments[i].Accept(callingGenerator);

            var functionFormat = new StringBuilder(100);
            
            functionFormat.Append(e.Function.Name);
            functionFormat.Append("(");
            for (var i = 0; i < frags.Length; i++)
            {
                functionFormat.Append("{" + i + "}");
                if (i != frags.Length - 1)
                {
                    functionFormat.Append(", ");
                }
            }
            functionFormat.Append(")");
            
            string sql = String.Format(functionFormat.ToString(), frags);
            return new LiteralFragment(sql);
        }

Hi Muhammad and thanks for your report.

I would really like to see full self-sufficient test case attached to it.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96040

780 Reggie Burnett	2010-01-05
      - fixed entity framework function processing so that it handles stored functions properly  (bug #45277)

fixed in 6.0.6, 6.1.4, and 6.2.3

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96043

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96044

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96046

An entry was added to the 6.0.6, 6.1.4, and 6.2.3 changelogs:

Calling a User Defined Function using Entity SQL in the Entity Framework caused a NullReferenceException.