Running the attached query (either with EXPLAIN or by simply doing the SELECT) on the following empty table will exhaust system memory in seconds(uses more than 10G of memory in less than 2 minutes), killing the query even less than a second after having executed it can take up to 30 seconds to finish (the query will be in "Killed" command and "statistics" state on the Processlist) while still keep eating up more memory.
Tested on both Wheezy and Jessie packaged versions of 10.0.24 with the same result, it didnt seemed to happen on 5.5.48 as i hit the bug only since i upgraded.
The bug doesnt happens when :
- Switching the engine to Aria or MyISAM
- When the optimizer switch "extended_keys" = "off" (which was the default on 5.5 but not since 10.0)
- When both columns are "tinyint"
- With only two values on each IN()/NOT IN()
Using "smallint" for both columns result in the query to be faster to kill but still not ending while eating memory.