Bug #54631 L2E query returns wrong result when Where calls stored function returns TIME
Submitted: 19 Jun 2010 11:24 Modified: 16 Aug 2010 13:33
Reporter: Sho Ijiri Email Updates:
Status: No Feedback Impact on me:
None 
Category:Connector / NET Severity:S3 (Non-critical)
Version:6.3.2 OS:Windows (Windows 7 x64 / .NET Framework 4.0)
Assigned to: Assigned Account CPU Architecture:Any
Tags: linq, visual studio 2010

[19 Jun 2010 11:24] Sho Ijiri
Description:
I tried to call TIME function of MySQL in Linq to Entities query via stored function (returns TIME (TimeSpan)) I created.
It worked in Select clause correctly.
However, when I used it in Where clause expression, it didn't return correct result.

How to repeat:
1. Install MySQL 5.5 and MySQL Connector/NET 6.3.2
2. Create following table and insert some data

use testschema;
CREATE TABLE test(id INT PRIMARY KEY, `datetime` datetime);
INSERT INTO test VALUES (0, '2010-01-01 17:00:00');
INSERT INTO test VALUES (1, '2010-03-01 18:00:00');
INSERT INTO test VALUES (2, '2010-05-06 19:00:00');

3. Create following stored function

DELIMITER $$
CREATE FUNCTION `MyTime`(a datetime) RETURNS time
RETURN TIME(a)$$

4. Make edmx file from DB, and then modify function definition in edmx like this:

<Function Name="MyTime" ReturnType="time" Aggregate="false" BuiltIn="false" NiladicFunction="false" IsComposable="true" ParameterTypeSemantics="AllowImplicitConversion" Schema="testschema" >
    <Parameter Name="a" Type="datetime"></Parameter>
</Function>

5. Map the function to .NET class function

public static class MyEntityFunctions
{
    [EdmFunction("DataModel.Store", "MyTime")]
    public static TimeSpan? MyTime(DateTime? a)
    {
        throw new NotSupportedException();
    }
}

6. Call Linq To Entities query

using (var dbContext = new DataEntities())
{
    TimeSpan ts = new TimeSpan(18, 30, 0);
    var list =  dbContext.test.Where(x => MyEntityFunctions.MyTime(x.datetime) > ts).ToList();
}

Actual Results:

id |  datetime
---+---------------------
0  | 2010-01-01 17:00:00
1  | 2010-03-01 18:00:00
2  | 2010-05-06 19:00:00

Expected Results:

id |  datetime
---+---------------------
2  | 2010-05-06 19:00:00
[24 Jun 2010 3:12] Bogdan Degtyariov
Hello Sho,

Thank you very much for the detailed description of the problem.
Can you provide the resulting SQL query that produces incorrect result?
If the server general query logging is enabled you can just copy the query from the general query log.

If the logging is disabled you can easily turn it on: MySQL 5.5 allows enabling the general query log dynamically without restarting the server:

mysql> SET GLOBAL general_log = 'ON';

The default log file name is host_name.log in the server data directory.
Thanks.
[24 Jun 2010 6:16] Bogdan Degtyariov
Can you please upload a test project for this bug report?

On the step #5 how exactly do I map the function to NET class method?
Is the NET method auto-generated or I should create it? If create, in which file (Model1.Designer.cs)?

Thanks.
[24 Jun 2010 6:39] Bogdan Degtyariov
Must be something from the following list:

http://msdn.microsoft.com/en-us/library/cc716690.aspx
[24 Jun 2010 23:12] Sho Ijiri
>Can you please upload a test project for this bug report?
OK, I just uploaded test project.

>On the step #5 how exactly do I map the function to NET class method?
>Is the NET method auto-generated or I should create it? If create, in which file
>(Model1.Designer.cs)?

I created it in other file manually. 
for example, "Program.cs" in test project I uploaded.

(I also tried auto generation. But VS2010 couldn't import the function. 
 I'm not sure about this exactly, but VS2010 said the function is composable.)

the log is following:

100625  7:50:35	   21 Connect	user01@localhost on testschema
		   21 Query	SHOW VARIABLES
		   21 Query	SHOW WARNINGS
		   21 Query	SHOW COLLATION
		   21 Query	SET NAMES utf8
		   21 Query	SET character_set_results=NULL
		   21 Init DB	testschema
		   21 Query	SELECT
`Extent1`.`datetime`, 
`Extent1`.`id`
FROM `test` AS `Extent1`
 WHERE (`MyTime`(`Extent1`.`datetime`)) > '0 18:30:00.0'

Thank you.
[28 Jun 2010 6:07] Bogdan Degtyariov
Hi Sho,

Thank you for the test project. I was able to repeat the problem.
Indeed, seems that connector/NET performed incorrect time conversion, which resulted in a wrong SQL query. This WHERE clause is always true because the string converted to 00:00:00 instead of 18:30:00:

... WHERE (`MyTime`(`Extent1`.`datetime`)) > '0 18:30:00.0'

Obviously the conversion fails because the string starts from '0 '.
Thanks again.
[16 Jul 2010 13:31] Vladislav Vaintroub
This appears to be a server bug, I just filed Bug#55321.
@Sho, is it possible for you to workaround it in your code, until server fixes the above mentioned bug
[16 Aug 2010 23:00] Bugs System
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".