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

[7 Feb 2007 14:45] Gabriele Franzoso
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)
[12 Feb 2007 9:51] Tonci Grgin
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?
[12 Feb 2007 14:19] Gabriele Franzoso
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.
[12 Feb 2007 15:03] Tonci Grgin
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.
[12 Feb 2007 16:52] Gabriele Franzoso
Thanks for the support and your quick response