When "semijoin" is enabled via "optimizer_switch", data can be duplicated with certain values of "optimizer_search_depth".

According to this documentation page:

https://mariadb.com/kb/en/mariadb/documentation/managing-mariadb/optimization-and-tuning/query-optimizations/optimization-strategies/duplicateweedout-strategy/

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.