Hello mysql team,

Server info:

Release: mysql-3.23.52 (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.23 Distrib 3.23.52, 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.52 Protocol version 10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 1 day 14 hours 59 min 28 sec Threads: 1 Questions: 430690 Slow queries: 11 Opens: 58 Flush tables: 1
Open tables: 38 Queries per second avg: 3.068 System: Linux ic-tm-s-slave-0001.intercaf.ru 2.4.7-10enterprise #1 SMP Thu Sep 6
16:48:20 EDT 2001 i686 unknown Architecture: i686 Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc
/usr/bin/cc

Let's say I have 2 tables:

create table eee(cid int not null primary key, l varchar(10)); create table ddd(cid int not null, c int not null, amount int);

And some data: insert into eee values(1,'1'); insert into eee values(2,'2'); insert into eee values(3,'3'); insert into eee values(4,'4'); insert into eee values(5,'5'); insert into ddd values(2,1,10); insert into ddd values(2,2,100); insert into ddd values(2,3,1020); insert into ddd values(4,1,1020); insert into ddd values(4,2,1020); insert into ddd values(4,3,1020);

The problem is: when I issue the query: select e.cid,sum(d.amount),sum(dd.amount) from eee e left join ddd d on d.cid=e.cid and d.c>=2 and d.c<=3 left join ddd dd on dd.cid=e.cid and dd.c=3 group by e.cid;

I've got: +-----+---------------+----------------+ | cid | sum(d.amount) | sum(dd.amount) | +-----+---------------+----------------+ | 1 | 0 | 0 | | 2 | 1120 | 2040 | | 3 | 0 | 0 | | 4 | 2040 | 2040 | | 5 | 0 | 0 | +-----+---------------+----------------+

while I shuld get quite other result: +-----+---------------+----------------+ | cid | sum(d.amount) | sum(dd.amount) | +-----+---------------+----------------+ | 1 | 0 | 0 | | 2 | 1120 | 1020 | | 3 | 0 | 0 | | 4 | 2040 | 1020 | | 5 | 0 | 0 | +-----+---------------+----------------+

See the difference in the 3rd columns???