Bug #11439 Using SUM over empty rowset with group by + left join - returns NO RECORDS!!!

• Submitted: 19 Jun 2005 20:11
• Modified: 20 Jun 2005 9:36
• Reporter: Marek Srom
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.7
• OS: Windows (Windows XP)
• CPU Architecture: Any

[19 Jun 2005 20:11] Marek Srom

Description:
In 5.0.6 and 5.0.7 versions is something wrong with SUM and AVG functions, especially when used over rowset with no records. When used on windows, ADO, and cursorlocation adUseClient produces errors (here is another bug already reported). 
When used on empty table produces no record. On empty table, query "select SUM(X) FROM t_Table" (t_table is empty) returns EOF and should return 1 record with NULL value. This is not so bad, but example in how to repeat may produce total application misbehavior without any error messages!!!

How to repeat:
Try query:
select s_number, SUM(nd_points) from t_customers left join t_points on t_customer.id=t_points.customer

(table t_customers contains some records, table t_points is empty)

returns EOF!!!

But this query in the same situation:
select s_number, COUNT(t_points.id) from t_customers left join t_points on t_customer.id=t_points.customer

returns (correct) records in t_customer and 0 value in Count column.

[19 Jun 2005 20:41] Marek Srom

Changing severity to serious.

[19 Jun 2005 20:43] Marek Srom

I guess this is optimiser bug, because works the same way when using for example sum(1) in the query

[20 Jun 2005 6:42] Hartmut Holzgraefe

Please add *working* examples to this report (including CREATE TABLE statements, sample data if possible)

The two queries in your report are obviously not working as they use inconsistant table names (t_customer vs. t_customers) and mix aggregate functions and regular columns 
without GROUP BY ...

[20 Jun 2005 7:27] Marek Srom

Sorry, here is working SQL statement:

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

[20 Jun 2005 9:36] Vasily Kishkin

I've got the follow result:

mysql> 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;

+--------+-------------------+---------------------+
| number | COUNT(t_point.id) | sum(t_point.amount) |
+--------+-------------------+---------------------+
| C001   |                 0 | NULL                |
+--------+-------------------+---------------------+
1 row in set (0.05 sec)

[20 Jun 2005 12:04] Marek Srom

tested more:
- mywac command line - works OK
- ODBC (Borland Database Explorer) - returns recordset with 1 row (OK) but SUM column is missing (even when I add one more column behind it, or move it to the frist place)
- ADO - returns EOF, sum field is in fields collection

ODBC driver problem?