MySQL Bugs: #1001: Problems with Left Joins 3.23.56 -> 4.0.13/4.0.14

Bug #1001	Problems with Left Joins 3.23.56 -> 4.0.13/4.0.14
Submitted:	5 Aug 2003 5:15	Modified:	23 Oct 2003 6:55
Reporter:	Paul Menheere	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.13-Max & 4.0.14-Max	OS:	Linux (Linux ( RedHat 7.3))
Assigned to:		CPU Architecture:	Any

Description:
 have 3 Tables.
one with Customer information, one with invoices and one with payments over the
invoices.

To calculate the invoices which haven't been paid in full I have a query with
two left joins.

QUERY : 
select rel.RNUMMER, rel.RNAAM, deb.FACTUUR, deb.DATUM, deb.BRUTO, deb.BRUTO + 
sum(bet.BEDRAG) AS BETAALD, deb.BRUTO - sum(bet.BEDRAG) AS BEDRAG,
TO_DAYS("2003-6-6") - ( TO_DAYS(deb.DATUM) + deb.TERMIJN) AS TELAAT
from relatie rel
left join deb_rek deb on ( rel.RNUMMER = deb.NUMMER )
left join betalingen bet on ( deb.FACTUUR = bet.TRANSACTIE AND bet.DATUM <=
"2003-6-6")
WHERE deb.DATUM <= "2003-6-6"
GROUP BY deb.FACTUUR
HAVING SUM(bet.BEDRAG) NOT LIKE deb.BRUTO

It now returns only the records which allready have a payment, but in the
earlier versions ( don't know if it is in the 4.0 series or only in release 13
)

According to the left join statement should a record also be printed if there
isn't a matching record in the right table.

How to repeat:
install the different servers and see

can provide data if requested.

It works with 4.0.12 but not with 4.0.13 & 4.0.14

Mysqldump of the file

Attachment: mysql_drop.sql (application/octet-stream, text), 83.46 KiB.

I also have this problem (probably, it's very similar).

In short: LEFT JOIN of a table with an INNER JOIN returns not a join, but a cartesian product of records (as if there was no join at all!).

I have a simpler, artificial testcase. I'll attach necessary files here.

Heck, I cannot attach files...

I've created Bug #1262 and I'll attach all stuff there. Maybe my problem is a different one anyway...

Note that I've experienced my problem on MySQL 4.0.11a. as well as MySQL 4.0.14.

When will this be looked at/fixed

if it is something I did wrong I would like to know to.

>> WHERE deb.DATUM <= "2003-6-6"

Referencing a specific value in the WHERE clause for a column from a table which participates in the 'optional' side of an outer join, without also checking IS NULL, prevents the return of rows for that table where no match was found.

If that is the cause of the problem, let me know.

If not, please try upgrading to the latest stable version of MySQL (4.0.16 as I write this).  A bug (which appears to be unrelated) with outer joins was corrected in 4.0.15, so if you are experiencing a bug I would like to ensure that it has not already been corrected.

Thank you

select rel.RNUMMER, rel.RNAAM, deb.FACTUUR, deb.DATUM, deb.BRUTO, sum(bet.BEDRAG) AS BETAALD, deb.BRUTO - sum(bet.BEDRAG) AS BEDRAG, TO_DAYS("2003-10-22") - ( TO_DAYS(deb.DATUM) + deb.TERMIJN) AS TELAAT
from deb_rek deb
left join relatie rel on ( rel.RNUMMER = deb.NUMMER and deb.DATUM <= "2003-10-22")
left join betalingen bet on ( deb.FACTUUR = bet.TRANSACTIE AND bet.DATUM <= "2003-10-22" )
WHERE deb.DATUM <= "2003-10-22"
GROUP BY deb.FACTUUR
HAVING NOT(SUM(bet.BEDRAG) <=> deb.BRUTO)

This did the trick

the problem was in the having statement. Thank you for your time though