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.