CODEWITHSUNDEEP
mysql
Igor Pigin
Igor Pigin

Reputation: 101

MySQL 5.7 vs. 8 difference in LEFT JOIN processing

We are checking if we can upgrade our project database from MySQL 5.7 to v.8. The system is 7 years old and has tons of code... Today we got a slightly strange bug which did not appear on 5.7 (I wonder why). The buggy request is the following:

SELECT TableA.Amount, SUM(TableB.Amount) AS Amount2
FROM 
TableA LEFT JOIN TableB ON TableA.ReservID = TableB.ReservID
WHERE 
TableB.InvoiceID IS NULL
AND TableB.InvoiceStatusID = 2
AND TableB.PersonID = 389
AND TableB.PersonTypeID = 1
AND TableA.ReservID = 4657;

There is one record in TableA and no records in TableB for the given conditions. I know that WHERE conditions are applied after joining the tables. So it is not a suprise for me that the query return NULL, NULL on MySQL8. But our developer (who's still sure that this query is Ok) just showed me that it returns 67667.65, NULL on MySQL 5.7! So I got 2 questions at ones. 1. Why it works on 5.7 when all data must be filtered out by the WHERE conditions on non-existent (all null in joint table) Table2 fields? 2. Is there a way to make MySQL8 work in the same 'tolerant' way as I am sure there are many such 'genius' queries all over our old code?

Upvotes: 0

Views: 791

Answers (2)

Solarflare
Solarflare

Reputation: 11106

The problem in your query is not the (left) join. While it makes it less clear to the reader that your left join is treated as a join, having the comparisons in the where clause is completely valid sql. Every database will treat your left join correctly as a join, and I don't think that MySQL (5.7 or 8.0) would give you a different result if you replace left join with a join, as the internal representation would not change.

Your query has a problem with the aggregation. select colA, sum(colB) without using group by colA will leave the value of colA unclear, see MySQL Handling of GROUP BY:

SELECT name, MAX(age) FROM t;

Without GROUP BY, there is a single group and it is nondeterministic which name value to choose for the group.

MySQL is about the only database system that will even allow you to run this query, a very special behaviour that generates a lot of questions on stackoverflow. Most other databases will complain about that column listed in the select - exactly for the reason you face right now: they don't really know what to return.

So the value you get for tablea.amount is basically random. While it usually depends on how MySQL executes the query internally (so it could depend on some optimization setting), it looks unlikely that you can convince MySQL 8 to return the number value there - and especially to make sure it is consistent in all similar queries you may have.

And I want to emphasize: the value null in MySQL 8 is also not deterministic - it could be something different.

To make a proper query, use an aggregate function for tablea.amount too, and depending on your requirements, fix your left join, e.g.

SELECT MAX(TableA.Amount) AS Amount,
       SUM(TableB.Amount) AS Amount2
FROM TableA LEFT JOIN TableB
ON TableA.ReservID = TableB.ReservID 
   AND TableB.InvoiceID IS NULL
   AND TableB.InvoiceStatusID = 2
   AND TableB.PersonID = 389
   AND TableB.PersonTypeID = 1
WHERE TableA.ReservID = 4657

This should give you the behaviour from MySQL 5.7, e.g. <value for amount>, null. If you use join, you should get null, null. Both cases will be deterministic.

Although using just SELECT TableA.Amount, SUM(...) ... LEFT JOIN ... (with a proper left join) will return the amount instead of null for MySQL 8 too, it is still not valid sql! MySQL will only allow it because ReservID = 4657 limits it to a single row in TableA using the primary key. So if you have to check all queries anyway, fix it properly.

Upvotes: 1

Jake
Jake

Reputation: 171

i dont know the reason, why its working on 5.7. but to get your expected result you can do:

SELECT 
  TableA.Amount, 
  SUM(TableB.Amount) AS Amount2
FROM TableA 
LEFT JOIN TableB 
   ON TableA.ReservID = TableB.ReservID 
      AND TableB.InvoiceID IS NULL
      AND TableB.InvoiceStatusID = 2
      AND TableB.PersonID = 389
      AND TableB.PersonTypeID = 1
WHERE TableA.ReservID = 4657;

Upvotes: 0

Related Questions