| Bug #606 | Problems with Left Joins 3.23.56 -> 4.0.13 | ||
|---|---|---|---|
| Submitted: | 6 Jun 2003 6:19 | Modified: | 6 Jul 2003 7:36 |
| Reporter: | Paul Menheere | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server | Severity: | S1 (Critical) |
| Version: | 4.0.13-Max | OS: | Linux (Linux) |
| Assigned to: | CPU Architecture: | Any | |
[6 Jun 2003 7:02]
Indrek Siitan
Yes, it would be great if you could provide the data. You can upload it to ftp://support.mysql.com/ pub/mysql/secret and let us know of the filename.
[6 Jun 2003 7:36]
Paul Menheere
filename: paulmenheere.sql on ftp://support.mysql.com/pub/mysql/secret/paulmenheere.sql
[12 Jun 2003 0:34]
Paul Menheere
Do I have to do something ? did you find my file ??
[3 Nov 2004 7:45]
Sergei Kulakov
I got a similar bug. Before I report it referring to this one I can say this will work if you change
left join betalingen bet on
( deb.FACTUUR = bet.TRANSACTIE AND bet.DATUM <="2003-6-6")
into
left join betalingen bet on
( deb.FACTUUR = If(bet.DATUM <="2003-6-6", bet.TRANSACTIE, Null)
[3 Nov 2004 7:48]
Sergei Kulakov
Sorry, I didn't notice the HAVING SUM(bet.BEDRAG) NOT LIKE deb.BRUTO which may be the reason here
[14 Feb 2005 22:54]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
Description: I 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.