MySQL Bugs: #26149: Problem with AVG() function

Bug #26149	Problem with AVG() function
Submitted:	7 Feb 2007 14:45	Modified:	12 Feb 2007 16:52
Reporter:	Gabriele Franzoso	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.11-2	OS:	Any (All)
Assigned to:		CPU Architecture:	Any

Description:
I use the following setup:
- Visual Studio 2003, .NET 1.1
- MySql 5.0 
- connector 3.51.11-2
- a system DSN for connection to MySQL
            
Trying to execute query “SELECT AVG (rating) FROM ratings”, using the ADODB .NET objects, the following error is returned from the recordset:

“<error: an exception of type: {System.Runtime.InteropServices.COMException} occurred>”.

The problem is not related to the type of the field, because ‘ratings’ is of type integer (and value range 1 to 5).
I have tried to execute the same query from the MySql Query Browser and the result is correct in this case.

How to repeat:
Following are the commands to create the table and few lines of VB.NET code to replicate the error.

CREATE TABLE  ratings (
  `id` int(11) NOT NULL auto_increment,
  `response` int(11) default '0',
  `comment` varchar(2000) default NULL,
  `rating` int(11) default '0',
  `xdate` varchar(8) default NULL,
  `xtime` varchar(6) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Dim ConnessioneDbEsterno as String = ”DSN=SystemDsn”
Dim ConnessioneDB As ADODB.Connection = New ADODB.Connection
Dim rsControllo As ADODB.Recordset = New ADODB.Recordset
Dim QueryValidazione as String=” SELECT AVG(rating) FROM ratings”

ConnessioneDB.Open(ConnessioneDbEsterno)
rsControllo = ConnessioneDB.Execute(QueryValidazione)

Hi Gabriele. 

Please do not:
 - Submit reports on old versions. Upgrade first.
 - Submit report more than once. I will point you to my last post in Bug#18157 which should probably solve your problem.

Now, what is the exact error (and error number)? What happens when you upgrade (ADO, MyODBC, NET fw) to recent versions?

Thanks for your feedback! 
I have updated the ODBC driver and aligned to the latest Windows update. 
AVG() is now calculated correctly, however the exception in the Recordset is still there. 
The exact error number is not available to us; the only information we got from the system is: {System.Runtime.InteropServices.COMException}

Despite the error above, we should now be able to use MySQL with our application - provided all the remaining tests will pass.

Hi Gabriele.

Ah hmmm, that exception can mean anything... You would do good to investigate it. Anyway, glad I could be of assistance.

Thanks for your interest in MySQL.

Thanks for the support and your quick response