>>>>> "Patrick" == Patrick Michael Kane <modus@stripped> writes:
Patrick> Hello!
Patrick> I am running MySQL 3.22.27 (built on the target host from the www.tcx.se
Patrick> source RPM) and have run into an interesting bug in left joins and the avg()
Patrick> function.
Patrick> The test tables and their data:
Patrick> create table a (
Patrick> id int not null
Patrick> )
Patrick> ;
Patrick> create table b (
Patrick> id int not null
Patrick> rating int null
Patrick> )
Patrick> ;
Patrick> insert into a values(1);
Patrick> insert into a values(2);
Patrick> insert into a values(3);
Patrick> insert into b values(1, 3);
Patrick> insert into b values(2, NULL);
Patrick> insert into b values(2, NULL);
Patrick> insert into b values(3, 2);
Patrick> insert into b values(3, NULL);
Patrick> The following query:
Patrick> select a.id, avg(rating) from a left join b on ( a.id = b.id ) group by a.id
Patrick> produces the following output:
Patrick> +----+-------------+
Patrick> | id | avg(rating) |
Patrick> +----+-------------+
Patrick> | 1 | 3.0000 |
Patrick> | 2 | NULL |
Patrick> | 3 | NULL |
Patrick> +----+-------------+
Patrick> 3 rows in set (0.01 sec)
Patrick> The third row should be 3,2, but it is 3, NULL. In fact, if you add more
Patrick> rows to either table, everything after the first row (1, 3) is NULL.
Patrick> Is this a MySQL bug or am I missing some sort of left join nuance?
Patrick> Best,
Patrick> --
Patrick> Patrick Michael Kane
Patrick> <modus@stripped>
Hi!
As Sinisa noticed, this works in MySQL 3.23
Here is a patch for MySQL 3.22
*** /my/monty/master/mysql-3.22.26a/sql/item.cc Sat Jul 3 20:29:53 1999
--- ./item.cc Tue Nov 9 23:58:25 1999
***************
*** 233,238 ****
--- 233,239 ----
null_value=1;
return 0.0;
}
+ null_value=0;
return nr/(double) count;
}
***************
*** 267,272 ****
--- 268,274 ----
null_value=1;
return 0.0;
}
+ null_value=0;
double tmp=(double) count;
double tmp2=(sum_sqr - sum*sum/tmp)/tmp;
return tmp2 <= 0.0 ? 0.0 : sqrt(tmp2);
Regards,
Monty
