A nested query does not return correct results in at least one case.
- A nested query query (Select col... where col in (subquery)) returns empty.
- Subquery correctly returns the rows (x,y,z).
- OuterQuery takes (z,y,z) in an IN statement and correctly selects the relevant results (Select col... where col in (x,y,z))
This is not happening in 10.1 but only on 102 onwards.
I could not reproduce in a simpler scenario. Perhaps this is due to using fulltext search in the subquery, or any other reason.
Tables are innodb. Attached the cfg settings I added to my.ini.