Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.3.12, 5.5(EOL), 10.0(EOL), 10.1(EOL)
-
None
-
mysql Ver 15.1 Distrib 10.0.18-MariaDB, for osx10.10 (x86_64) using readline 5.1
MariaDB installed with homebrew
-
5.5.45, 10.1.7-1
Description
Turning semijoin optimization on/off can affect number of rows returned by a query.
Enabling semojoin optimisation can cause a query like that:
SELECT * FROM manufacturers WHERE (...); |
to return more rows than:
SELECT * FROM manufacturers; |
Please, find a full test case setup attached.
Just to sum up the test case:
It executes the query below: (manufacturers table size: 2)
SELECT * FROM manufacturers WHERE manufacturers.id IN ( |
SELECT ships.manufacturers_id FROM owners JOIN ships ON owners.id = ships.owners_id WHERE owners.id = 1 |
);
|
Then the query returns:
- 2 rows ( SET optimizer_switch='semijoin=off'; )
- 3 rows ( SET optimizer_switch='semijoin=on'; )