List:General Discussion« Previous MessageNext Message »
From:Michael Widenius Date:November 9 1999 10:00pm
Subject:possible bug in left joins (3.2.27)
View as plain text  
>>>>> "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
Thread
possible bug in left joins (3.2.27)Patrick Michael Kane3 Nov
  • Re: possible bug in left joins (3.2.27)sinisa4 Nov
    • Re: possible bug in left joins (3.2.27)Bob Kline4 Nov
      • Re: possible bug in left joins (3.2.27)Patrick Michael Kane4 Nov
      • Re: possible bug in left joins (3.2.27)sinisa4 Nov
    • Re: possible bug in left joins (3.2.27)Patrick Michael Kane4 Nov
  • possible bug in left joins (3.2.27)Michael Widenius10 Nov
    • Re: possible bug in left joins (3.2.27)Patrick Michael Kane10 Nov
      • Re: possible bug in left joins (3.2.27)Michael Widenius10 Nov
Re: possible bug in left joins (3.2.27)sinisa4 Nov