MySQL Bugs: #46742: use " group by" statement in ado.net enetiy framework

Bug #46742	use " group by" statement in ado.net enetiy framework
Submitted:	15 Aug 2009 13:30	Modified:	9 Jun 2011 20:49
Reporter:	Liu Leiz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.4, 6.3.6	OS:	Windows (windows 7)
Assigned to:	Julio Casal	CPU Architecture:	Any
Tags:	GROUP BY

Description:
Table `Tag` structure:
Id bigint pk
Content varchar(10)

Linq statement:
from item in m_db.tag group item by item.Content into g select new { g.Key, Count = g.Count() }

Trace SQL:
SELECT 1 AS `C1`, `GroupBy1`.`K1` AS `Content`, `GroupBy1`.`A1` AS `C2` FROM (SELECT Count(1) AS `A1` FROM `tag` AS `Extent1` GROUP BY `Extent1`.`Content`) AS `GroupBy1` 

EXEC Result:
#1054 - Unknown column 'GroupBy1.K1' in 'field list'

How to repeat:
1,create database use the statement:
CREATE DATABASE  `test` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;
CREATE TABLE  `test`.`Tag` (
`Id` BIGINT NOT NULL AUTO_INCREMENT ,
`Content` VARCHAR( 10 ) NOT NULL,
PRIMARY KEY(Id)
) ENGINE = INNODB;
2,Create EDMX with VS2008 IDE wizard
3,use Ado.net Entity Linq query:
var tags = (from item in m_db.tag group item by item.Content into g select new { g.Key, Count = g.Count() }).ToList();
4,Exception Occor.

Suggested fix:
i can not find the unit test about "group by" feature in source code...

edmx

Attachment: edmx.jpg (image/pjpeg, text), 27.16 KiB.

table tag

Attachment: table tag.jpg (image/pjpeg, text), 24.47 KiB.

table joke

Attachment: table joke.jpg (image/pjpeg, text), 52.38 KiB.

linq

Attachment: linq.png (image/x-png, text), 4.44 KiB.

Hi Liu and thanks for your report.

Please do attach complete test case and explain what you get and what you expected to get from 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".

Guys, this bug is still reproducible in 6.0.5. How to reproduce? Just try to group  and select two values as described in initial post. There is a SO post which contains the actual reason why exception is raised: http://stackoverflow.com/questions/3727623/linq-generating-bad-query-error-unknown-column-...

I got the same error with Connector 6.3.4! It is very annoying as there is no work around. This bug started somewhere after upgrading to the latest MySQL connector, because before that upgrade everything worked fine without any change!

Bug#57186 was marked as duplicate of this report.

Anything new at this bug? We have a production release in some days, but without that function it's impossible to release! As there is no practicable workaround for that problem. Actually I would say this is Severity S1!

Please, I would really appreciate some fixing in any case.

Bug#58218 was marked as duplicate of this report.

i think this is Severity S1 too, can you guys change it?

and we can not use a subscript in group by clause

example:
// t2 is a subtable of t1
var counts = from t1 in t1_entities
group gt1 by t1.c1
select new { C1 = gt1.FirstOrDefault().c1, Count = gt1.Where(t1 => t1.t2s.Any(t2=>t2.c2
== 0)).Count()};

it will also cause an "Unknown column" exception.

Guys, everybody is looking to get this solved but I still did not receive a complete test case. Liu was close to this but pasted DML for just one table and no VS project.

Can anyone please attach small but complete test case (along with DML) which I can load and test?

In a private comment, Whikiey offered to send a test case. So Whikiey, how about clicking Files and just attaching (packed) one?

mysql group by issue sample project

Attachment: mysql_groupby.7z (, text), 142.63 KiB.

SQL for "mysql group by issue sample project"

Attachment: mysql_groupby.7z (, text), 405 bytes.

Thanks Whikiey, let me see what can be done.

Whikiey, your project does not run on my box...

System.Data.EntityException: The underlying provider failed on Open. ---> System
.Collections.Generic.KeyNotFoundException: The given key was not present in the
dictionary.
   at System.ThrowHelper.ThrowKeyNotFoundException()

General log shows nothing happened:
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld, Version: 5.5.6-rc-log (MySQL Community Server (GPL)). started with:
TCP Port: 5530, Named Pipe: MySQL
Time                 Id Command    Argument
101116 10:32:58	    3 Connect	root@localhost on test
		    3 Query	SHOW VARIABLES
		    3 Query	SHOW COLLATION
		    3 Query	SET NAMES utf8mb4
		    3 Query	SET character_set_results=NULL
101116 10:33:56	    3 Quit	

Tables are created in database test, model looks sound, necessary changes in project (connection string etc) made.

i did not fill the provider section, and you might not install v6.3.4. now i added, please try this file again, thank you.

Attachment: mysql_groupby_prj_with_mysqlprovider.7z (, text), 142.75 KiB.

Nope, exactly the same failure... Could be due to Entity dll being 6.3.4 instead of 6.3.5 which I have installed on my box...

Will assign Reggie for recheck.

you can try recreate a project and copy .cs/.edmx files to your project directory.

Anything new here? Is there anything I can help you guys with?? I've got already a second case where I need the COUNT... And there really isn't no workaround!

None has provided functioning test case so far and I'll just guess now. Look into http://dev.mysql.com/doc/refman/5.5/en/connector-net-connection-options.html and set FunctionsReturnString to true. See if it helps.

I will try that. Let me see, if I can give you guys a test case. But actually, it's nothing special. Just use any table you've got and do a query like that:

            var grouping = (from x in xxx
                            group x by x.groupingkey into g
                            select new { Key = g.Key, Count = g.Count() }
                     );

You see, nothing special. But it breaks everything!

Any update on this - I noticed it is marked S3 - that is wrong.  This is basic functionality.  You currently cannot count on a group.  Please fix.

Any news on this?

Any news about this bug? we are using some components and these components are producing the linq expression. so there is no workaround for us. actually it shoul not be "Non-critical" bug. It is critical for everyone.

Hello, I developed a NUnit test to this bug.

It can be inserted in OrderingAndGrouping.cs in MySql.Data.Entity.Tests (Also is need add using System.Linq)

 [Test]
        public void GroupBySimple()
        {
            MySqlDataAdapter da = new MySqlDataAdapter(
                "SELECT c.Name, COUNT(c.id) as Amount FROM Companies c GROUP BY c.Name", conn);
            DataTable dt = new DataTable();
            da.Fill(dt);

            using (testEntities context = new testEntities())
            {

                var query = (from c in context.Companies
                             group c by c.Id into cgroup
                             select new { name = cgroup.Key, amount = cgroup.Count() });

                int i = 0;
                foreach (var result in query)
                {
                    Assert.AreEqual(dt.Rows[i++][0], result.name);
                    Assert.AreEqual(dt.Rows[i++][1], result.amount);
                }
            }
        }

My bosses don't want that I spend time on this. I will workaround it with storedprocedures. When at home, I will check the source code of the connector. If I can understand what is happening, I will post here.

Sorry the bad english.

Added GroupBySimple NUnit Test case.

Attachment: OrderingAndGrouping.cs (text/plain), 4.54 KiB.

Hi, 
I did not test it exacly but I think this bug is related only .net framework 4.0. It is working in 3.5 properly.

The MySql.Data.Entity.Tests Target Framework is 3.5.

When I run the OrderingAndGrouping unit tests, OrderBySimple and OrderByWithPredicate pass, but my GroupBySimple throw:

  ----> MySql.Data.MySqlClient.MySqlException : Unknown column 'GroupBy1.K1' in 'field list'

I'm not sure, but if Target Framework is 3.5, in this framework have the bug too.

ok, than the framework version affects the component which is creating the query.

I used my lunch hour to make some tests around this bug, and I encontered this feature:

 var query2 = (from c in context.Companies
                             group c by c.Name into cgroup
                             select new { name = cgroup.Key, amount = cgroup.Count() });

throws the well known Exception Unknown column 'GroupBy1.K1' in field list.

But, this query performs correctly:

                var query1 = (from c in context.Companies
                              group c by c.Name into cgroup
                              orderby cgroup.Key
                              select new { name = cgroup.Key, amount = cgroup.Count() });

I had a lot of luck. The order by clause makes the query work!

I updated the unit test file to incorporate this. I will send it soon.

Updated to include ORDER BY workaround feature

Attachment: OrderingAndGrouping.cs (text/plain), 5.18 KiB.

Note, the "order by" workaround only work in .net framework 3.5. When I change the target framework of MySql.Entity.Tests to .net 4.0, the GroupBySimple will throw a same exception with or without "order by" clause.

still not working under connector 6.3.6 and visual studio 2010, framework 4.0, linq to sql

I have the same problem with Connector 6.3.6, Visual Studio 2010, Framework 4.0 and Linq to SQL. I get the same exception as stated above in the bug report when using group by.

I have the same problem with Connector 6.3.6, Visual Studio 2010, Framework 4.0 and Entity Framework. Any news?

Clearly, it's impossible to use Mysql + VS2010 + Linq
Dev team, what are you waiting for ?

Bug #60589 was marked as a duplicate of this one.

Simple project to reproduce the bug, made according to original description of Liu Leiz. .

Attachment: Bug46742.zip (application/octet-stream, text), 40.31 KiB.

Why is this still considered as non-critical? An exception on group by is a serious showstopper for the MySql connector in many scenarios. I've posted a simple test project (VS 2010/.NET 4) which was trivial to create in order to reproduce the problem according to the very first description of Liu Leiz! There is even no need to fill the tables with data as the program crashes on incorrect SQL generated to query the tables.

Yes, I have this problem on Net 4.0 Full and 6.3.6.  I will have to add a view as a workaround.  It should be upgraded in my opinion, particularly since LINQ 4.0 is getting increasing usage.

A small modification 6.3.6.0 In my case, works
http://narod.ru/disk/11496486001/Release-4.0.zip.html

Small modification. AS IS

Attachment: Release-4.0.zip (application/octet-stream, text), 176.67 KiB.

Sorry. Forgot to remove garbage.

Attachment: Release-4.0.zip (application/octet-stream, text), 176.57 KiB.

We are updating a project to entity framework and this bug is so critical that we are thinking about migrating from MySQL to SQL Server.

It's been over 7 months now, that I have to use workarounds. Right now I am using a view to not to use the group by. I've got already over 4 cases in just one project where I was required to use workarounds, it's a basic funtionality which is not working. Why is this bug Non-Critical? If I could help anybody, I would...

@Evgeniy Borisov 

Could you please tell us or show, what exactly you fixed in sourcecode?

my patch

Attachment: Source.cs (text/plain), 2.37 KiB.

Was this decision good for you?

I didn't have time yet to check back your changes. If it really works, can please somebody of the core team confirm those changes and possibly include them into a bugfix release? I'm pretty sure everybody here would love it!

Is there any update with this bug?

I found a possible workaround on .Net 4.0

I changed my code from

var result = (from si in model.table
group si by si.NumCores into grp orderby grp.Key
select new CoreCount { Cores = grp.Key, Count = grp.Count() }).ToList();

To

var result = (from si in model.table
group si by si.NumCores into grp orderby grp.Key
select new CoreCount { Cores = grp.FirstOrDefault().NumCores, Count = grp.Count() }).ToList();

and that seemed to fix it. But I would like to chime into what others say. This is rather basic functionality and should be counted as something more than S3.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

Fixed versions: 6.1.6, 6.2.5, 6.3.7 and 6.4.1+

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

How to get  6.3.7? Sorry for the stupid question

Version 6.3.7 is not yet released.  Please refer to http://dev.mysql.com/doc/refman/5.6/en/changes-6.3.x.html

I see this is fixed in 6.3.7, but I don't know where I can download a beta of this to test it?

Also it says it is fixed in 6.4.1+, but that does not indicate if the 6.4.1 beta that is out right now includes the fix or not. I am suspecting not, since the fix appears to have been done 3 days after the 6.4.1 build?

Where can I get the 6.3.7 beta source code, so I could build this myself until the official release is out?

Version 6.3.7 is now available for download.

Thanks! 

I noticed when I tested 6.4.1 (which did not include this fix) that it produced much better SQL code than 6.3.6. When is that code line going to go out of beta?

Kendall, what line of code do you mean? Have you tried 6.3.7? It should include all bug fixes applied in 6.4.1.

Julio, I meant that the grouping fix is not present in 6.4.1, so I am looking forward to testing a 6.4.2 beta that would include that fix in it. I have tested 6.3.7 and the grouping bug is fixed (it is what I am using now), but as I said the SQL generated by the 6.4.x code tree is much better than the 6.3.x code tree.

So I was wondering when 6.4.x might go out of beta and into production :)

Kendall, very soon :)

Is there a mailing list or a group where people who are working on the trunk code for Connector/Net hang out? I would like to start building from source code and help out with this, but I had some issues building and could not find any mailing list where I can post questions?

Maybe you can email me some details kendallb AT amainhobbies DOT com.

Kendall, I'm not sure about the mailing list, I'm asking. About building from the source code, what issues are you having?

I am having a lot of trouble getting the unit tests to run fully to completion, and I don't feel good about using anything where I can't get all the unit tests to run properly. There is no clear documentation on how to set up Nunit and run the unit tests (or how to set up the test database schema).

I got about 50% of the unit tests for the core connector working, but none of the Entity Framework tests would run for me at all.

Kendall, let's continue this discussion in an appropriate topic in Connector/Net forums:

http://forums.mysql.com/list.php?38

Please add a new topic with your issues and I can follow up.

Posted on the forums:

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