I had to upgrade to 10.4 yesterday from 10.1. I went through each major version and ran mysql_upgrade after each.
Today I noticed strange problems on our selects:
This select returns 1 result:
This select returns 0 results:
The same selct on the same database structure on version 10.2.25 returns a a whole lot of results.
I noticed similar problems on LEFT JOINS, even if there is data to be joined, it is NOT joined and the result set is empty, unless there is a condition on the main table on the left side.
The left side select should not affect the joined result.
I am not sure which version is related to (10.3 or 10.4) nor where to start to debug.
I tried setting optimizer switch for 10.1 defaults but no change: