Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
5.5.41, 10.0.15
-
Linux
Description
When "semijoin" is enabled via "optimizer_switch", data can be duplicated with certain values of "optimizer_search_depth".
According to this documentation page:
A DuplicateWeedout strategy is supposed to be implemented using temporary tables for semijoin queries. For some values of "optimizer_search_depth", this duplicate weedout step may not occur.
Attached is the following:
optimizer_search_depth_semijoin_data_setup.sql - A script that sets up a database and a few tables.
optimizer_search_depth_semijoin_query_test.sql - A script that queries the tables to demonstrate the problem.
optimizer_search_depth_semijoin_output.txt - Example execution of the scripts and their output.
Attachments
Activity
Ran a set of tests, got several hundred of mismatches between the patched version and baseline 10.0 (low optimizer_search_depth values). All differences look legit, related to the bugfix, so the good news is that tests hit the right spot; but due to the sheer amount of mismatches it's unrealistic to check each one separately.
Next step – I'll run a similar test, but between the patched version with low optimizer_search_depth and baseline 10.0 with normal optimizer_search_depth. The expectation is that there should be no mismatches.
On a separate note, I wonder if the commit comment is correct:
JOIN::cur_dups_producing_tables was not maintained correctly in
the cases of greedy optimization (search_depth > n_tables).
Is it not the other way round, search-depth < n_tables?
The second run went okay. I will also try valgrind tests, but please go ahead and push (we need to decide first whether it's for 5.5 or 10.0).
Pushed a patch into https://github.com/MariaDB/server/tree/bb-10.0-mdev7474 . I'll need testing from elenst.