Turning semijoin optimization on/off can affect number of rows returned by a query.
Enabling semojoin optimisation can cause a query like that:
to return more rows than:
Please, find a full test case setup attached.
Just to sum up the test case:
It executes the query below: (manufacturers table size: 2)
Then the query returns:
- 2 rows ( SET optimizer_switch='semijoin=off'; )
- 3 rows ( SET optimizer_switch='semijoin=on'; )