-----Original Message----- From: Alex Villacís Lasso [mailto:avillaci@.palosanto.com] Sent: Friday, November 30, 2001 11:50 AM To: bu...@lists.mysql.com Cc: avil...@ceibo.fiec.espol.edu.ec Subject: LEFT JOIN with GROUP BY report only first row of query with empty right table.

Description:

When a query is made in the form: select table_a.name, sum(table_b.value) as sum_values from table_a left join table_b on table_a.a_key = table_b.a_key group by table_b.a_key and table_b happens to be empty, the result set incorrectly contains only the first row of table_a. It should contain at least as many rows as there are on table_a, with the group function (in this case, 'sum()'), evaluated to the appropriate value for a NULL field.

How-To-Repeat: The following script demonstrates the bug: ----------------------------- CUT HERE ----------------------------- /* Switch to test database and create test tables */ use test; create table if not exists table_a (a_key int not null primary key, name varchar(32) not null); create table if not exists table_b (id int not null auto_increment primary key, a_key int not null references table_a (a_key), value int not null);

/* Populate both tables */ insert into table_a values (1, 'Alice'); insert into table_a values (2, 'Bob'); insert into table_a values (3, 'Carol');

insert into table_b (a_key, value) values (1, 675); insert into table_b (a_key, value) values (2, 765); insert into table_b (a_key, value) values (3, 186); insert into table_b (a_key, value) values (1, 584); insert into table_b (a_key, value) values (2, 294); insert into table_b (a_key, value) values (3, 142); insert into table_b (a_key, value) values (1, 654); insert into table_b (a_key, value) values (2, 164); insert into table_b (a_key, value) values (3, 385);

/* Show contents from both tables */ select * from table_a; select * from table_b;

select table_a.name, table_b.value from table_a left join table_b on table_a.a_key = table_b.a_key; /* Seems to work OK */ select table_a.name, sum(table_b.value) as sum_values from table_a left join table_b on table_a.a_key = table_b.a_key group by table_b.a_key; /* The previous query correctly returns: +-------+------------+ | name | sum_values | +-------+------------+ | Alice | 1913 | | Bob | 1223 | | Carol | 713 | +-------+------------+ */ delete from table_b; /* Notice, table_b is now empty */ select table_a.name, table_b.value from table_a left join table_b on table_a.a_key = table_b.a_key; /* Seems to work OK */ select table_a.name, sum(table_b.value) as sum_values from table_a left join table_b on table_a.a_key = table_b.a_key group by table_b.a_key; /* The previous query should return: +-------+------------+ | name | sum_values | +-------+------------+ | Alice | 0 | | Bob | 0 | | Carol | 0 | +-------+------------+

However, it actually returns: +-------+------------+ | name | sum_values | +-------+------------+ | Alice | 0 | +-------+------------+

What happened to Bob and Carol? */

delete from table_b; delete from table_a; ----------------------------- CUT HERE -----------------------------

Originator: Alex Villacís Lasso Organization: Escuela Superior Politécnica del Litoral (ESPOL) Megatelcon-Ecuador (Palosanto Solutions) MySQL support: none Synopsis: LEFT JOIN with GROUP BY report only first row of query with empty right table. Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.44 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.22 Distrib 3.23.44, for pc-linux-gnu on i686 Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license

Server version 3.23.44 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 44 min 34 sec

Threads: 1 Questions: 1 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.000 System: Linux srv3.palosanto.com 2.4.2-2 #1 Sun Apr 8 19:37:14 EDT 2001 i586 unknown Architecture: i586

Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/2.96/specs gcc version 2.96 20000731 (Red Hat Linux 7.1 2.96-85) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 13 sep 27 07:13 /lib/libc.so.6 -> libc-2.2.2.so -rwxr-xr-x 1 root root 5578134 abr 6 2001 /lib/libc-2.2.2.so -rw-r--r-- 1 root root 26350254 abr 6 2001 /usr/lib/libc.a -rw-r--r-- 1 root root 178 abr 6 2001 /usr/lib/libc.so Configure command: ./configure --disable-shared --with-mysqld-ldflags=-all-static --with-client-ldflags=-all-static --without-berkeley-db --without-innodb --enable-assembler --with-mysqld-user=mysql --with-unix-socket-path=/var/lib/mysql/mysql.sock --prefix=/ --with-extra-charsets=complex --exec-prefix=/usr --libexecdir=/usr/sbin --sysconfdir=/etc --datadir=/usr/share --localstatedir=/var/lib/mysql --infodir=/usr/info --includedir=/usr/include --mandir=/usr/man '--with-comment=Official MySQL RPM'

To request this thread, e-mail bugs...@lists.mysql.com To unsubscribe, e-mail <bugs...@lists.mysql.com>