Changing this ticket to be more correct taking into account some new info.
"Conversion of big predicated into subqueries" ( https://mariadb.com/kb/en/library/conversion-of-big-in-predicates-into-subqueries/ ) is making many queries using *specific pattern* especially on default configuration to degrade in performance over 100 fold in specific cases. Most of these can't be fixed by enabling hash joins using join_cache_limit = 8
- These issues are catastrophic even on simplest queries and tiny tables
- Some issues can be partially fixed by reconfiguring the server or completely rewriting queries, leading to "bearable" performance degradation (<10 times slower)
- This "optimization" is very specific, only for IN sets witch >1000 items
- Degradation feels totally "random", simple data conversion between string and int can change 50x degradation into over 100x.
- All tests are still on very small tables, with very small IN (..) sets to keep this ticket as short as possible
- CPU overload caused by this on production machines can be huge
So adding a switch to turn this off, so it can be disabled for when it's causing issues seem necessary
Some of many cases will follow as separated comments as this is too large to add at once.