Bug #82991 Entity Framework 7 - LINQ - .Take() causes MySqlException: Undeclared variable:

Submitted: 14 Sep 2016 12:33 Modified: 27 Sep 2022 21:52
Reporter: Vaclav Elias Email Updates:
Status: Unsupported Impact on me:
None 
Category:Connector / NET Severity:S2 (Serious)
Version:7.0.4, 7.0.5 OS:Windows (Any)
Assigned to: Assigned Account CPU Architecture:Any
Tags: .Net Core, Entity Framework 7

[14 Sep 2016 12:33] Vaclav Elias
Description:
Hi, when I do a very basic query 

var model = _context.wp_posts.Take(10).ToList(); 

I get this error: MySqlException: Undeclared variable: __p_0 

It seems that you do use LIMIT __p_0 in the query but you don't define __p_0. 

Is this a bug or not implemented? If it is a bug, do we need to escalate it here http://bugs.mysql.com/ or you can take care of it? 

A detailed log below 

Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommandBuilderFactory[1] 
Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30'] 
SELECT `w`.`ID`, `w`.`comment_count`, `w`.`comment_status`, `w`.`guid`, `w`.`menu_order`, `w`.`ping_status`, `w`.`pinged`, `w`.`post_author`, `w`.`post_content`, `w`.`post_content_filtered`, `w`.`post_date`, `w`.`post_date_gmt`, `w`.`post_excerpt`, `w`.`post_mime_type`, `w`.`post_modified`, `w`.`post_modified_gmt`, `w`.`post_name`, `w`.`post_parent`, `w`.`post_password`, `w`.`post_status`, `w`.`post_title`, `w`.`post_type`, `w`.`to_ping` 
FROM `wp_posts` AS `w` 
LIMIT __p_0 
fail: Microsoft.EntityFrameworkCore.Query.RelationalQueryCompilationContextFactory[1] 
An exception occurred in the database while iterating the results of a query. 
MySql.Data.MySqlClient.MySqlException: Undeclared variable: __p_0 
at MySql.Data.MySqlClient.MySqlStream.ReadPacket() 
at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int64& insertedId) 
at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int64& insertedId) 
at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force) 
at MySql.Data.MySqlClient.MySqlDataReader.NextResult() 
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior) 
at MySql.Data.MySqlClient.MySqlCommand.ExecuteDbDataReader(CommandBehavior behavior) 
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.Execute(IRelationalConnection connection, String executeMethod, IReadOnlyDictionary`2 parameterValues, Boolean openConnection, Boolean closeConnection) 
at Microsoft.EntityFrameworkCore.Storage.Internal.RelationalCommand.ExecuteReader(IRelationalConnection connection, IReadOnlyDictionary`2 parameterValues, Boolean manageConnection) 
at Microsoft.EntityFrameworkCore.Query.Internal.QueryingEnumerable.Enumerator.MoveNext() 
at Microsoft.EntityFrameworkCore.Query.QueryMethodProvider.<_ShapedQuery>d__3`1.MoveNext() 
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.<_TrackEntities>d__15`2.MoveNext() 
at Microsoft.EntityFrameworkCore.Query.Internal.LinqOperatorProvider.ExceptionInterceptor`1.EnumeratorExceptionInterceptor.MoveNext() 

How to repeat:
Make any Linq query and use Take()

Suggested fix:
No suggestions but I guess you are not transferring the values from Skip(x) and Take(y) to LIMIT x,y
[14 Sep 2016 12:35] Vaclav Elias
I was testing with these packages:

"MySql.Data.Core": "7.0.4-IR-191",
"MySql.Data.EntityFrameworkCore": "7.0.5-IR21",
[14 Sep 2016 15:11] Chiranjeevi Battula
Hello Vaclav Elias,

Thank you for the bug report.
Could you please provide repeatable test case (sample project, code, create table statements/database etc. - please make it as private if you prefer) to
confirm this issue at our end?

Thanks,
Chiranjeevi.
[14 Sep 2016 15:41] Vaclav Elias
Dear Chiranjeevi,

Thank you for your quick reply an interest.

The repeatable test case would take 1+ hour to set up and there is no time to do it as you can set it up yourselves in 5 minutes.

If you work yourselves on this project you can try to query any MySQL database and table with this.

var rowCount = 10; // or any number
var skipCount = 2; // or any number

var result = yourContext.yourTable.Take(rowCount).ToList();

var result2 = yourContext.yourTable.Skip(skipCount).Take(rowCount).ToList();

You should get a reported error in both cases. Reported also by another person 

http://forums.mysql.com/read.php?38,650020,650020#msg-650020

Regards, Vaclav.
[21 Nov 2016 1:44] Mark Guinness
This still appears to be a bug with version 7.0.6-IR31, is there any timeline on when this will be fixed, what is the complexity of the problem?
[21 Nov 2016 2:51] Mark Guinness
This is the limit/offset code used for SQLite, hopefully it can help.

https://github.com/aspnet/EntityFramework/blob/master/src/Microsoft.EntityFrameworkCore.Sq...
[23 Nov 2016 6:34] Chiranjeevi Battula
Hello Vaclav Elias,

Thank you for the bug report.
Verified based on internal discussion with dev's.

Thanks,
Chiranjeevi.
[26 Jan 2017 14:55] Gabriela Martinez Sanchez
This is a duplicated bug of http://bugs.mysql.com/bug.php?id=84453
[27 Sep 2022 21:52] Daniel Valdez
Posted by developer:
 
Connector/NET no longer have support for EF 7, instead we have for EFCore which do not present this bug.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.044 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.