MySQL Bugs: #11267: AVG() and SUM() functions causes error

Bug #11267	AVG() and SUM() functions causes error
Submitted:	12 Jun 2005 6:36	Modified:	5 Jan 2008 0:39
Reporter:	Lars Lautrup-Larsen	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (Windows Server 2003)
Assigned to:		CPU Architecture:	Any

Description:

An error occurs when the AVG() og SUM() functions is used:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'
[Microsoft][ODBC Driver Manager] Program type out of range 

I don't know if this is caused by the MySQL server or the MyODBC driver (v. 3.51.11.2 used).

How to repeat:

CREATE TABLE testtable (id VARCHAR (50) NOT NULL, stem1 TINYINT (3) UNSIGNED DEFAULT '0');
INSERT INTO testtable (id, stem1) VALUES ('Lars', 1);
INSERT INTO testtable (id, stem1) VALUES ('Lars', 2);

In ASP3:

Set cachestatus = Server.CreateObject("ADODB.Recordset")
cachestatus.ActiveConnection = MyConnectionString
cachestatus.Source = "SELECT AVG(stem1) as stem1 FROM testtable WHERE id='Lars'"
cachestatus.CursorType = 0
cachestatus.CursorLocation = 2
cachestatus.LockType = 1
cachestatus.Open()

Response.Write cachestatus.Fields.Item("stem1")

Results in:

Microsoft OLE DB Provider for ODBC Drivers error '80020009'
[Microsoft][ODBC Driver Manager] Program type out of range 

Regards,

Lars Lautrup-Larsen.

I was not able to reproduce this bug on VB. I guess the bug is asp bug. Could you please reproduce this on VB and attach case test ?

It's not possible for me to provide a VB testcase. The problem is in ASP3 as mentioned, but I don't know if the source of the problem is in ASP or in the MyODBC driver.

This problem does not exist in previous versions of MySQL (3.x and 4.x)

The ASP3 testcase provided in the initial bugreport should clearly show what goes wrong..

Rgrds, 

Lars.

Hi !

Could you please provide full text of ASP3 test case and attach ?

Here's the ASP3 testcase. Just insert the password for root and save this as f.e. "test.asp":

'------- TESTCASE START
<%

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Port=3306; Database=test; Uid=root; Pwd=rootpassword"

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.ActiveConnection = objConn

objConn.Execute "DROP TABLE IF EXISTS testtable;"
objConn.Execute "CREATE TABLE testtable (intfield INT UNSIGNED);"
objConn.Execute "INSERT INTO testtable (intfield) VALUES (1);"
objConn.Execute "INSERT INTO testtable (intfield) VALUES (2);"

objRS.Source = "SELECT AVG(intfield) as intfield FROM testtable"
objRS.Open()

Response.Write objRS.Fields.Item("intfield")

%>
'------- TESTCASE END

Regards, 

Lars Lautrup-Larsen.

Hi!

Thanks for your test case.

I was able to reproduce this bug with the provided ASP test-case, but couldn't repeat it using VBA (Visual Basic Access).

BTW, tested w/5.0.7

This is not an optimizer bug.  its a myodbc bug.  Testcases in VBA as well as running the actual queries failed to reproduce the results explained in this bug report.

Please do the following;

- minimize the activity on the server (perhaps use a dev server)
- turn on ODBC trace on the server using the ODBC Administrator
- recreate the problem without uneeded activity
- turn off ODBC trace
- attach the resulting file to this bug

Look in the trace file for any signs of a problem surrounding the use of the functions in question.

Im having the same problem since i upgraded from mysql 4.1 to 5.0

Any solution?

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".

Please see my last post in Bug#18157.

Fixed the version number at this bug report. The report is for MyODBC version 3.51 and not for version 5.0.

Works fine with 3.51 and 5.1.

Hi this bug still exists with 
Mysql 5.0.51 with 
MySQL ODBC 3.51 Driver
ASP Classic

I found that using 
SELECT 
SUM(IF(general3 ='Attractions',1,0)), 
SUM(IF(general4 = 'Transport',1,0)), 
SUM(IF(general5 = 'Eating',1,0)), 
SUM(IF(general6 = 'Accom',1,0)), 
SUM(IF(general7 = 'Shopping',1,0)), 
SUM(IF(general8 = 'Events',1,0)), 
SUM(IF(general9 <> '',1,0)), 
SUM(IF(Reg27 <> '',1,0)), 
SUM(IF(Reg28 <> '',1,0)), 
SUM(IF(Reg29 <> '',1,0)), 
SUM(IF(Reg26 <> '',1,0)), 
SUM(IF(REG30 = 'Transfer Brochure',1,0)) 
FROM WebRequests 
WHERE CLIENT ='lda' 

results in a 
Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Driver Manager] Program type out of range 

The workaround is a little long winded but it does work
Create a stored procedure that populates a temporary table with the above results
ie
DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_LDAreportSelect`$$

CREATE PROCEDURE `sp_LDAreportSelect`(IN dateFrom VARCHAR(10), IN dateTo VARCHAR(10))
BEGIN
TRUNCATE TABLE tempReportSelect;
INSERT INTO tempReportSelect(CountAttr,CountTrans,CountEat,CountAccom,CountShop,CountEvents,CountOther,CountForwardedEmail,BulkBrochure,SingleBrochure,LetterSent,CountBrochure) 
SELECT 
SUM(IF(general3 ='Attractions',1,0)), 
SUM(IF(general4 = 'Transport',1,0)), 
SUM(IF(general5 = 'Eating',1,0)), 
SUM(IF(general6 = 'Accom',1,0)), 
SUM(IF(general7 = 'Shopping',1,0)), 
SUM(IF(general8 = 'Events',1,0)), 
SUM(IF(general9 <> '',1,0)), 
SUM(IF(Reg27 <> '',1,0)), 
SUM(IF(Reg28 <> '',1,0)), 
SUM(IF(Reg29 <> '',1,0)), 
SUM(IF(Reg26 <> '',1,0)), 
SUM(IF(REG30 = 'Transfer Brochure',1,0)) 
FROM WebRequests 
WHERE CLIENT ='lda' 
AND dateshort>=dateFrom
AND dateshort<=dateTo;
END$$

DELIMITER ;

And call it from within ASP passing in any variables needed (I passed in 2 dates)

Then simply select the columns from the temporary table.

Its not perfect as it is slow, but it does work!

Darrell