Bug #11541 wrong recordset when used with query containing sum or avg

• Submitted: 24 Jun 2005 6:20
• Modified: 11 Jun 2007 13:57
• Reporter: Marek Srom
• Status: Can't repeat
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: 3.51
• OS: Windows (windows xp)
• CPU Architecture: Any

[24 Jun 2005 6:20] Marek Srom

Description:
query:
select number, COUNT(t_point.id), sum(t_point.amount) from t_customer left join
t_point on
t_customer.id=t_point.customer group by t_customer.number

(description of case in how to repeat)

does return wrong recordset (foe example in borland database explorer sum column is missing, ADO returns EOF - both results are bad)

in mysql command line query works good

How to repeat:
create table t_customer(id int,number varchar(10))
GO
create table t_point(id int,customer int, amount int)
GO
insert into t_customer values(1,'C001')
GO
--SELECT that works bad
select number, COUNT(t_point.id), sum(t_point.amount) from t_customer left join
t_point on
t_customer.id=t_point.customer group by t_customer.number
GO
--SELECT that works ok
select number, COUNT(t_point.id) from t_customer left join t_point on
t_customer.id=t_point.customer group by t_customer.number
GO

[24 Jun 2005 6:45] Vasily Kishkin

Tested on Win 2000 Sp4, MySQL server 5.0.8 beta, MyODBC 3.51.11, Borland SQL Explorer 4.0

[8 Aug 2005 9:13] Soren Heitmann

Also verified this bug, but query can be much more simple.  ASP record set returns EOF for "select sum(1) from [any table you like]"

[20 Jan 2006 5:17] David Bailey

This is still a problem on 5.0.18-nt. I am also using the odbc driver 3.51.11 (could not get any odbc driver greater than this to work) on Windows XP. 

Also note that this is not limited to SUM(). I get the same problem on simple SELECT statements using ROUND() indicating that this is not a GROUP BY problem.

It is also interesting that if any of the affending statments is the only statement in a select (SELECT SUM(x) FROM...) a single blank headingless column with the correct number of rows, also blank, is displayed. 

These problems have only accured since upgrading from MySQL4 to MySQL5.

[10 Apr 2006 0:38] Rui Ribeiro

This issue is still not fixed in 5.0.19. It's hard to believe that no one is even handling this!

[19 Jun 2006 17:45] Steve Owen

Can confirm that this is still a bug in 5.0.22, although probably only in connection with myODBC

SELECT AVG(1) FROM [any table]

results in 1.0000 as you'd expect in the console, but in ASP through the connector 3.51.10.00 you get no results returned.

In 3.51.12.00, though, it appears to be fixed, although I did have to cast the result to a double:
avg = CDbl(objRS("average"))

but then I have to do this with SUMs too.

[11 Jun 2007 13:57] Georg Richter

Can't repeat. Tested against 3.51.15