Bug #30207 Select sum(column) does a select * on table

• Submitted: 2 Aug 2007 14:52
• Modified: 22 Aug 2007 12:26
• Reporter: Matt Ryan
• Status: Can't repeat
• Category: Connector / ODBC
• Severity: S2 (Serious)
• Version: 3.51.17
• OS: Windows
• CPU Architecture: Any
• Tags: sum select *

[2 Aug 2007 14:52] Matt Ryan

Description:
when doing a sum(column) from table the odbc driver converts it to a select * from table

our table has 5 million records, but a LOT of columns

select sum(committed) committed, sum(obligated) obligated from obl_com WHERE
fy = '7' and don = 'test'

#MYSQL command prompt time
.02 seconds

#odbc driver time
5 seconds (does Select *)

How to repeat:
any select sum(column) statement that does not have a group by column in the select clause

Suggested fix:
unsure, odbc driver does a select * no matter what we do

if you change the sql to select columna, sum(columnb) from table group by columna the bug goes away

however our table does not have a column I can group by, or put in the results

[2 Aug 2007 22:41] MySQL Verification Team

Thank you for the bug report. A small test according with your description:

Using the mysql client:

Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.41-community-nt-log MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create database db1;
Query OK, 1 row affected (0.05 sec)

mysql> use db1;
Database changed
mysql> create table t1 (id int not null auto_increment primary key,
    -> col1 int, col2 int,
    -> colt timestamp);
Query OK, 0 rows affected (0.20 sec)

mysql> insert into t1 (col1,col2) values (2,1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into t1 (col1,col2) values (3,1);
Query OK, 1 row affected (0.05 sec)

mysql> insert into t1 (col1,col2) values (2,2);
Query OK, 1 row affected (0.06 sec)

mysql> select sum(col1) from t1 where col2=1;
+-----------+
| sum(col1) |
+-----------+
|         5 |
+-----------+
1 row in set (0.45 sec)

mysql>

Then using the ODBC test tool ODBCTE32.exe:

Full Connect(Default)

	Env. Attr. SQL_ATTR_ODBC_VERSION set to SQL_OV_ODBC3

	Successfully connected to DSN 'testbug'.
SQLExecDirect:
				In:				hstmt = 0x003B1700, 
										szSqlStr = "select sum(col1) from t1 where col2=1;", cbSqlStr = -3
				Return:	SQL_SUCCESS=0

Get Data All:
"sum(col1)"
5
1 row fetched from 1 column.

[22 Aug 2007 12:26] Matt Ryan

we upgraded to the newest odbc driver, that fixed the bug for us